Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup problem
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 |
#2
|
|||
|
|||
Lookup problem
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=482263 |
#3
|
|||
|
|||
Lookup problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions |