Excel - Multiple data sources into one table/pivot table

Hi Simon, thank you for your query in STL's forum.

Vlookups would not work if the source table ranges that are being consolidated are normal Excel lists. If you change the source tables into Data tables then each table has a name i.e. Table1, Table2.

What you then do in the Vlookup is instead of selecting the entire table with your mouse for the second argument of the Vlookup, you enter the name of the table instead.

So: VlOOKUP(LookupValue, A1:D100,4,False)which is static because the range is defined as a specific area on the sheet would look like this:
VLOOKUP(LookupValue,Table1,4,False) which is dynamic because the Table1 automatically includes new rows of data

Data tables are dynamic and therefor if you add new records they will automatically be included in the table range. So you do NOT have to constantly redefine the Vlookup's source range.

The consolidated table should then automatically adjust and you only need to 'Refresh' your Excel workbook to get the latest data in your PivotTable. There is no need to 'Change Data Source' in the PivotTable every time.

I hope this helps.

PS. The Excel workbook attached to this message has nothing to do with your query. I accidentally attached the file to the wrong query. My apologies

Kind regards,

Ron Oldeboom
Learning and Development Consultant
STL Training