Register
Log In
Unanswered
Home
August 25, 2017
Offline
Good Morning
I need some help with a spreadsheet that is to pull data from specific sheets. The way I have it set up is not very efficient. I have six workbooks titled, Sheet11, Sheet12, Sheet13, Sheet14, Sheet15 and Sheet16. My question is how would I adjust my formula so that I can have one formula to pull from specific sheet. I enter data in column J and column K and the results is listed in column L. I would like to add Column I for the name of the sheet. So once I enter the data into the appropriate columns the correct data is populated.
Here's my formula. CollectDataFromGroup($J43,"Sheet11",$K43,COLUMN(A1))
Any ideas on how to revise the formula is greatly appreciated.
Thanks Mel
Dashboards
June 25, 2016
Offline
Hi Mel
Not sure if this is what you are looking for.
It would help if you can post a sample file with the expected result.
Sunny
August 25, 2017
Offline
Sunny
Thanks for the reply. Attached is the file. I hope it helps. I would like to see if there is a way to combine or expand the CollectDataFromGroup formula to include the Sheet Name as one of the selection criteria.
Dashboards
June 25, 2016
Offline
There is no file attached.
August 25, 2017
Offline
Sorry, this is my first posting. I will try again.
Dashboards
June 25, 2016
Offline
Hi Mel
Thanks for attaching the file as it better describe your requirements. It is more complicated than what you have described in your post 
I have created another UDF named ExtractData(WorksheetName , Date , GroupNo , RowNo ,IndexNo) that require 5 arguments:
1) WorksheetName :- Name of the worksheet where your data is
2) Date :- The date you are looking for in column A of the data sheet
3) GroupNo :- Your grouping number
4) RowNo :- The row where the GroupNo are located (currently at row 16)
5) IndexNo :- The column of the Group that you wanted to extract from (columns 1 to 5 as per your example)
Hope this helps.
Sunny
August 25, 2017
Offline
Sunny, Thank you so very much. This is perfect!
August 25, 2017
Offline
Sunny
I would like to create another extract data table by entering the worksheet name and date. Then the data will pull from worksheet "DataSheetMorn",
Beginning with Row 449 columns AE, AF, AG, AH, AI, and AJ. I could not figure out how to modify the previous macro so that it can extract the data.
Can you help?
Thanks
Dashboards
June 25, 2016
Offline
Please attach some sample data and the expected results.
August 25, 2017
Offline
Sunny
Here is the file.
Thanks
Dashboards
June 25, 2016
Offline
There is no attachment.
Most Users Ever Online: 57
Currently Online:
16 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
sugi gunawan
janja mesec
John DiMartino
lizette acosta
eric johnson
Kim Bond
Nichelle Stevens
Manuel Rodriguez
peter wang
Amit Kanekar
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
