Look Up Text Value on Multiple Worksheets and Return Adjacent Cell or Cells

[EduardStoo]

Hi -

I am trying to piece hundreds of workbooks (single worksheets in each) into one workbook, so the 'files' become 'worksheets' in effect.

I have got this working using VBA, and now have 3,000 worksheets in one file. I now need to populate various fields to make a large information table for products and can do this using an index of all the sheet names and =INDIRECT("'"&B6&"'!"&J6) where B6 is the tab (worksheet) name ref and J6 the cell location of the text I require. This is fine where the information is static sheet-to-sheet, but some of the details vary in their cell placement.

Is there a way to use indirect with either INDEX or VLOOKUP, to say "look in sheet 6, search for the term 'product code' or 'colour' and return the next cell along to the right?'

I can't get this working and just get #N/A error when using =VLOOKUP($M$3,INDIRECT("'"&B8&"'!$A$2:$A$6"),2) (where M3= 'colour', B8 is sheet ref and A2:A6 location on the sheet where word 'colour' may be?

I am not sure on the limits of the function or what you can/can't combine with?

I'd appreiciate any help on this

Thanks,

Stuart

[Olly]

3,000 worksheets sounds utterly unmanageable.

It sounds like it would be better to merge the data from all your source files into one data model, and filter / report against that as required. This would be quite straightforward to automate using Power Query.

[EduardStoo]

Solved it - the references such as 'colour' had a colon, so 'colour:', working now!!

Thanks,

Stuart