*FIXED* Excel question on looking up data in other worksheets

[Specop 007]

Ok, little confused on how to reference data in other worksheets in the same workbook.

Lets say I have 2 tabs, tab A and tab B.

Lets say I enter static data in tab A and reference that through tab B.

Lets say tab B has the following fields
A: Item name
B: Item unit cost
C: Item Retail cost

In tab A lets say I want to be able to use a drop down box (Much like the auto filter would do) do reference my items from Tab B and pull the corresponding data.

Tab A
A: Drop down box with names from Tab B column A
B: Vlookup from Tab B, Column B
C: Same as B

I could then apply changes to B and C with equations to account for changing build times or material costs. I understand how to do the VLOOKUP function and modify the referenced value, but what I cant figure out is how to get Tab A column A to be a drop down box selecting a value from Tab B column A. Within the same worksheet you can use an auto filter, and the drop down box in Tab A should look like the auto filter if used in Tab B, I just have no damned clue how to do it.

Hope that makes sense.

Thanks

 

[mayest]

Ok, if I understand what you are trying to do, it can't be done. 😀 At least not the way that you are trying to do it. Let's see if I have this right: You have two sheets (A & B). In sheet A you have a combo box control and you want to populate it with the column headers from sheet B.

Sheet B looks like this:

Item Name Item Unit Cost Item Retail Cost

with some data below in each column. Let's say that the headers are in A1:C1.

The problem is that the combo box data has to come from a single column, not a row. So, what you need to do is, somewhere in either sheet, select three cells (say Z1:Z3) and then type =transpose(A1:C1) and then press Shift+Ctrl+Enter (this is an array function). That will populate cells Z1:Z3 with the column headers. Now, format your combo box so that the input range is Z1:Z3. Now the combo box will have a list of the column headers, and the "cell link" will tell you which selection the user made. You can now do whatever further processing (VLookup, etc) that you need.

 

[mayest]

I think that I misunderstood your question in my previous response. On rereading your question, I think that you just want to know how to create a drop-down list with "Item Name", "Unit Cost" and "Retail Cost" as choices. These choices come from column A on sheet B, right?

If that is correct, then you want to use the Combo Box control from the Forms Toolbar. To enable that toolbar, go to View --> Toolbars and select Forms. Click the Combo Box control and then draw it on the worksheet wherever you want it to appear. Now, right click the combo box and choose Format Control. The "Input Range" will be the range of cells in column A from sheet B that contain your field names. Something like SheetName!A1:A3. The "Cell Link" is a cell somewhere on your worksheet where the control will keep track of which item has been chosen. You can reference that cell to determine what the user wants. This will probably be the driver for your VLookup functions.

If neither of these replies help, then I'm totally missing what you are looking to do.

 

[Specop 007]

Well, do this. On the second sheet at the top type
Unit

Then below that type...
Widget A
Widget B
Widget C

Now, turn on Autofilter for the Item column. It gives a neat little drop down arrow to select from Widget A,B and C. Thats what I'm looking for, except I want that neat little drop down arrow to be on the first sheet but still select from Widget A, B and C which are on the second sheet.

Yeah, I knew explaining this would be a pain in the arse.

 

[mayest]

Sorry, I'm out of ideas. As far as I know, you are not going to be able to autofilter data from another sheet, and trying to duplicate that functionality with combo boxes would be impossible. You can easily create a combo box that will let you choose between Widgets that are listed on another sheet (see previous response), but that is far from an autofilter.

 

[Specop 007]

Originally posted by: mayest
Sorry, I'm out of ideas. As far as I know, you are not going to be able to autofilter data from another sheet, and trying to duplicate that functionality with combo boxes would be impossible. You can easily create a combo box that will let you choose between Widgets that are listed on another sheet (see previous response), but that is far from an autofilter.

Asked around at work and got it! 😀

On sheet 2 do the Widgets. Now select the widgets (Not the column heading "Item"). On the far left upper box that says the cell (To the left of the fx symbol and table data field) type in the array name (Say, Items).

Now on sheet 1 type Item. On the celll beneath it go Data - Validation.
Allow: List
Source: =Items

Badda bing. You now have the item list on your other page.

:beer: