IF and VLOOKUP or Macro

[Katrina Randle]

I am wanting to pull a report together that takes data in specified cells from four other tabs based on the content of an entry in a particular cell within the Report tab.

 I'm not sure which is the best way to go about this or the formulae/macro to use.

I have attached an example of what I am trying to achieve. The grey section on the report tab is information that I would like to source from the named tab according to the identifier in column A on the report tab. 

[#2]

Hey Katrina-

It would help if you used some sort of ID in the sheets with people that way if you have multiple cities on the list you can be sure to pull the correct entry.  You may want to put them all on one sheet.  It will make the formula much simpler.  However if you would like to keep it as is please see attached for reference:

[#3]

Thanks Matt.

Unfortunately I need to keep the sheet separate as there may be fields that the individual's want to personalise for their own use that do not need to come across in the Report tab.

Does the ID have to be numerical or could I use textual code for example the location code (column D).

Many thanks Kat

[#4]

As variant that could be done with Power Query

- create separate table with the names (same as sheet names);

- for each sheet the name range for first 1000 rows as in example, names are same as for sheets

- PQ function which loads data from the named sheet

- query which combined that and load back to the sheet

Please see attached.

[#5]

Hey Kat-

The ID's can be text if you like.  The formula should handle them the same.  You will just want to avoid duplicates.  If there are duplicates in the column the formula will return the first result from top to bottom.