Excel worksheet search function to return sheet specific data.... HELP PLEASE :) Can't remember whether this is doable in Excel or if I have previously done this in Access.

Question

Excel worksheet search function to return sheet specific data.... HELP PLEASE :) Can't remember whether this is doable in Excel or if I have previously done this in Access.

Anonymous

I have an Excel spreadsheet with a large number of worksheets, all of which are formatted in the same way. I'd like to know if there is a way I can create a search function on the first sheet that when entering a job number (entered in same column/same row on all worksheets) it will return data in specific cells on that front sheet?

Ideally, on the first worksheet I'd like to be able to enter the job# ( in F2 on every subsequent worksheet) and have the data from cells G2, H2, I2, J2 and K2 shown on that first worksheet.

It's been a long time since I did this sort of thing and can't remember if the last time I did this was in Excel or Access.

Would appreciate any help, or an alternative way of doing things. (tried uploading a screenshot but it keeps failing to upload!)

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

2 answers

Answer 1

If you are also entering the job# in cell F2 (if it is in another cell change the F2 of the MATCH: MATCH(F2

=CHOOSEROWS(VSTACK('First Sheet:Last Sheet'!G2:K2),MATCH(F2,TOROW('First Sheet:Last Sheet'!F2),FALSE))

Or, slightly shorter:

=LET(D,VSTACK('First Sheet:Last Sheet'!F2:K2),DROP(FILTER(D,INDEX(D,,1)=F2),,1))

Where First Sheet is the first of your searched sheets, and Last Sheet is the last of your searched sheets.....

Answer 2

Ashish Mathur 99,575 Volunteer Moderator

Hi,

It would be ideal to append data from multiple worksheets into a single table and then apply filters/formulas/pivots on the appended table. Watch this video - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube

Share via

Excel worksheet search function to return sheet specific data.... HELP PLEASE :) Can't remember whether this is doable in Excel or if I have previously done this in Access.

2 answers