Auto populate data from another table

**pphg** · 08-22-2012, 07:01 AM

Hi guys

Hope u can help me with the attached spreadsheet. I have a summary sheet and need to autopopulate the data to the table from another cell. Please refer to the attached file im working on.

Many thanks!

**DBY** · 08-22-2012, 07:35 AM

Hello
Take a look at the attached reply. It uses the VLOOKUP and INDIRECT functions to return the values. For convenience of reference, I've renamed your sheets: 'Hotel 1'; 'Hotel 2'. This is a 2007 file format as your original attachment but if you have any problems, as you state you're using 2003, then I can attach a 2003 version.

Hope this helps.
DBY

**pphg** · 08-22-2012, 08:30 AM

Hi!

Thanks for the help, just wondering, What if I need to modify the name of the Hotel, what changes should I make to the fomula? Sorry, fairly new to this

E.g. HOTEL 1 - Shangrila Hotel

Thanks

Originally Posted by DBY

Hello
Take a look at the attached reply. It uses the VLOOKUP and INDIRECT functions to return the values. For convenience of reference, I've renamed your sheets: 'Hotel 1'; 'Hotel 2'. This is a 2007 file format as your original attachment but if you have any problems, as you state you're using 2003, then I can attach a 2003 version.

Hope this helps.
DBY

**Ace_XL** · 08-22-2012, 09:20 AM

You could populate your list in Column T based on Cell A1(say) of each sheet name. The Vlookup & INDIRECT combo suggested by DBY in Post #2 will then work seamlessly.

In Cell A1 of each sheet use the below to get sheet name
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,32)

**DBY** · 08-22-2012, 09:37 AM

Hello

If you can do as Ace_Xl suggests, that would be a very good way to go. Wished I'd known that myself! We all learn here.

As long as your Data Validation drop down list matches your sheet names then the formula needs no amendment. Be aware however, that within the 'Table Array' that the VLOOKUP function references, the range of cells:

=VLOOKUP($B7,INDIRECT("'"&$B$3&"'!B6:N7"),COLUMN(B1),0)

in this example , B6:N7, is not dynamic, so your data must remain within these ranges on each sheet. If those change then you would have to amend the formulas.

Hope that's clear.
DBY