Lookup problem

  • Thread starter Thread starter jamesjohn
  • Start date Start date
J

jamesjohn

I have an excel file with Overall Hotel bookings in it on the firs
worksheet.
Here the rows are in order of arrival date and have the agency name
reference number, hotel name, group name, number of PAX amongst othe
things.

I want to also have a sheet for each hotel, by looking up the hotel'
name in the first sheet and copying the information across.

I have tried with vlookup, but all I get is a repeated list of on
booking per hotel instead of all the different bookings.

Can anyone help ?

Thanks in advanc
 
Did you remember to change the column index number? Meaning if your
formula in your specific hotel sheet is:

=VLOOKUP(A1,Sheet1!A1:F21,1)

Sheet1! is the master
A1 being the hotel name.

The data return will be from column 1 in Sheet 1 when it finds the
Hotel name in the array Sheet1!A1:F21. If you want to return the
values in column 2,3,4,5 and 6 then you need to change the column index
number in the cell where you are using the VLOOKUP to reflect that. So
in other words, the formula won't change your column index number when
you drag it across. You can drag it across but you'll have to go in
and manually change the col_index_num. Otherwise it will always return
the data from column 4.



Cheers,

Steve
 
I think I'd try to keep all my data on one sheet. Then use
Data|filter|Autofilter to see different hotel names.

But if you want to split the data from one worksheet into many worksheets based
on a column, then both Debra Dalgleish and Ron de Bruin may have solutions for
you:

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

And Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm
 
Back
Top