utilizing multiple worksheets for lists and one for report

kurai · May 16th, 2002, 02:40 PM

how would I, say, make a range on one worksheet and vlookup on another?

would I just use the name for the range or is that worksheet dependent?

Oh! even better,
I have a list of items, parts and prices. I need a total of 4 seperate price lists for distro, cost, discount, and retail. could I create a listbox with said price types and have the prices automatically adjust without making many different ranges and vlookup calls?

[ This Message was edited by: kurai on 2002-05-16 13:44 ]

inarbeth · May 16th, 2002, 03:41 PM

Quote:

On 2002-05-16 13:40, kurai wrote:
how would I, say, make a range on one worksheet and vlookup on another?

would I just use the name for the range or is that worksheet dependent?

Oh! even better,
I have a list of items, parts and prices. I need a total of 4 seperate price lists for distro, cost, discount, and retail. could I create a listbox with said price types and have the prices automatically adjust without making many different ranges and vlookup calls?

[ This Message was edited by: kurai on 2002-05-16 13:44 ]

You can have the lookup range on another sheet. The name is not worksheet dependent. You can have problems if you copy a sheet with named ranges in it.

Not sure I understand the second part of your question. If you have different columns for different prices you might use a fomula to change the col_index_num argument in the VLOOKUP formula. If you post some more details we can help.

Brian · May 16th, 2002, 04:10 PM

1) If a name only occurs on one sheet in your workbook, then you can use that name on any sheet in your workbook and it will refer to the correct sheet. If, however, you define a "name1" on sheet1 and a "name1" on sheet2, then excel when you are on sheet1, excel will refer to the "name1" range on sheet1, same for sheet2. If you try to refer to "name1" from sheet3, then I expect that you would get a #name? error because it won't know which sheet you want to refer to, so you would need to say Sheet1!name1.

2) Probably don't need different lists, just different columns in one list (table)
ex columns:
col 1 = Item
col 2 = dist$
col 3 = cost$
col 4 = discount$

3) If you are looking for:
Listbox#1 will return
1 for dist$ selected
2 for cost$ selected
3 for disc$ selected

Listbox#2 will display a different list depending on the result of Listbox #1 then

There are many examples of working with list boxes on MrExcel, http://www.j-walk.com and other sites. search them and this message board.

4) Or, if you want to
Listbox#1 will return a value to A1
1 for dist$ selected
2 for cost$ selected
3 for disc$ selected

Listbox#2 will list all items and return value to B1
1 for item 1 selected, etc.

Then equation in C1 to display the correct price would be simply:

= INDEX (TABLE_NAME, $B$1, $A$1 + 1)
= INDEX (range, row #, column #)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 02:40 PM	#1
kurai New Member Join Date: May 2002 Posts: 19	how would I, say, make a range on one worksheet and vlookup on another? would I just use the name for the range or is that worksheet dependent? Oh! even better, I have a list of items, parts and prices. I need a total of 4 seperate price lists for distro, cost, discount, and retail. could I create a listbox with said price types and have the prices automatically adjust without making many different ranges and vlookup calls? [ This Message was edited by: kurai on 2002-05-16 13:44 ]

May 16th, 2002, 04:10 PM	#3
Brian Board Regular Join Date: Apr 2002 Posts: 113	1) If a name only occurs on one sheet in your workbook, then you can use that name on any sheet in your workbook and it will refer to the correct sheet. If, however, you define a "name1" on sheet1 and a "name1" on sheet2, then excel when you are on sheet1, excel will refer to the "name1" range on sheet1, same for sheet2. If you try to refer to "name1" from sheet3, then I expect that you would get a #name? error because it won't know which sheet you want to refer to, so you would need to say Sheet1!name1. 2) Probably don't need different lists, just different columns in one list (table) ex columns: col 1 = Item col 2 = dist$ col 3 = cost$ col 4 = discount$ 3) If you are looking for: Listbox#1 will return 1 for dist$ selected 2 for cost$ selected 3 for disc$ selected Listbox#2 will display a different list depending on the result of Listbox #1 then There are many examples of working with list boxes on MrExcel, http://www.j-walk.com and other sites. search them and this message board. 4) Or, if you want to Listbox#1 will return a value to A1 1 for dist$ selected 2 for cost$ selected 3 for disc$ selected Listbox#2 will list all items and return value to B1 1 for item 1 selected, etc. Then equation in C1 to display the correct price would be simply: = INDEX (TABLE_NAME, $B$1, $A$1 + 1) = INDEX (range, row #, column #)