Log In
Register
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
VIP
Trusted Members
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.
VIP
Trusted Members
June 25, 2016
Offline
There is no file attached.
August 25, 2017
Offline
Sorry, this is my first posting. I will try again.
VIP
Trusted Members
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
VIP
Trusted Members
June 25, 2016
Offline
Please attach some sample data and the expected results.
August 25, 2017
Offline
Sunny
Here is the file.
Thanks
VIP
Trusted Members
June 25, 2016
Offline
There is no attachment.
1 Guest(s)
