VLOOKUP Over multiple sheets

[sooghtac]

Hello,

I have to consolidate 100 projects where the reports are extracted from a database into excel. There is no summary of the projects and I'm requesting help to pull the data through to the summary page.
I've enclosed an example with coloured fields that I require the data to be completed.

Instead of manually linking all the fields to the summary page, I need the formula to look up the project name across the multiple sheets and retrieve the data from that specified project.
Some of the fields are different line items in each project report, therefore I believe indexing will be required for some fields.

The extracted reports from the database are terrible, so I'm not sure if it's possible for all fields.

For example:
I require to look up the Current Phase for Project A. This is G9 of the Project A Report.

I've attached file.

Thanks in advance.

[tim201110]

function INDIRECT will do it

[JohnTopley]

If I were you I would get rid of all the merged columns which are the cause of so many problems when trying to use formulae.

This is shown in your formula where you referenced G9:S9 as these are merged.

in G3

=INDIRECT("'" &A3 &"'!G9")

in K3

=INDIRECT("'" &A3 & "'!AC50")

Would the above not be better if it referenced the phase in rows 46 onward: in the above A50 ???

[sooghtac]

Thank you - indirect worked.