Lookup problem

[jamesjohn]

I have an excel file with Overall Hotel bookings in it on the first 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 other things.

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

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

Can anyone help ?

Thanks in advance

[SteveG]

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

[Dave Peterson]

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

jamesjohn wrote:
>
> I have an excel file with Overall Hotel bookings in it on the first
> 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 other
> things.
>
> I want to also have a sheet for each hotel, by looking up the hotel's
> name in the first sheet and copying the information across.
>
> I have tried with vlookup, but all I get is a repeated list of one
> booking per hotel instead of all the different bookings.
>
> Can anyone help ?
>
> Thanks in advance
>
> --
> jamesjohn
> ------------------------------------------------------------------------
> jamesjohn's Profile: http://www.excelforum.com/member.php...fo&userid=9692
> View this thread: http://www.excelforum.com/showthread...hreadid=482263

--

Dave Peterson