Pulling multiple data to auto fill in another worksheet

[Machala Sentance]

Hi, apologies if this has been asked before but I don't seem to find the answer and if it is possible at all.

I have a spreadsheet with multiple tabs for a workload allocation (one per staff member).  I want to create a summary worksheet and want the data to pull through from the multiple sheets.  I have tried looking at INDIRECT, MATCH and VLOOKUP formulas but it seems to be that the data has to be in columns and mine in in various places across the worksheet.  I have tried the following formula but this obviously doesn't work but not sure if it's something simple that I have missed or if it's not possible and I have to use VBA.  - =VLOOKUP(A3,INDIRECT("'"&INDEX(Codes,MATCH(1,--(COUNTIF(INDIRECT("'"&Codes&"'!$A$2:$B$5"),B2)>0),0))&"'!$A$2:$B$5"),2,FALSE)

[#2]

Hey Machala-

Hope you're doing well.  I saw that you had some external workbooks referenced in your workbook. If you are trying to reference external workbooks using the INDIRECT() function, then both workbooks will need to be open.  Otherwise the function can't process.  i.e. INDIRECT() won't work if your workbook is closed.

If you have all of this information in one workbook this becomes much easier.  If this is the case can you provide a better working example file that contains a before and after scenario, so the community members can test their solutions.

[#3]

Hi Matt

Thanks for your reply, sorry didn't really make myself very clear - I think that I made it more complicated than it needed to be.  What I need to know if it's possible with one formula to autofill all of the various cells in the summary sheet (copy attached) with the corresponding information in the WLA sheet without having to put individual formulas in each cell in the summary sheet - i.e. to almost read across the headers in the summary sheet and find the corresponding cell in the WLA sheet.

All the referencing will be within the same spreadsheet so hopefully this will make it much easier as you say.

Thanks for your help

[#4]

Thanks for your reply, sorry didn't really make myself very clear - I think that I made it more complicated than it needed to be. What I need to know if it's possible with one formula to autofill all of the various cells in the summary sheet (copy attached) with the corresponding information in the WLA sheet without having to put individual formulas in each cell in the summary sheet - i.e. to almost read across the headers in the summary sheet and find the corresponding cell in the WLA sheet. All the referencing will be within the same spreadsheet so hopefully this will make it much easier as you say. Thanks for your help

[#5]

Try using a formula like this....

[#6 · Accepted solution]

Thank you so much - I have been thinking about this for hours

[#7]

Glad you were able to get it working.  Please feel free to post back to the community in the future if you have any other questions.