Drop down look up table

Microsoft Excel 2007
August 29, 2009 at 19:48:47
Specs: Windows XP
Hi,

I have an excel application to produce invoice and a packing sheet that I have built over the years. It also exports data to an access database from which sales figures can be obtained.

The excell sheet uses a a lookup function to pick up values like title, price, index numbers etc from a Booklist.xls file. The link key is the product_id. So by inputting the prod_id value pulls up into the invoice line all the other required values.

What I want to find out now is if I can use the Booklist.xls as a lookup table while it is closed so that in the incoice.xls file, clicking in the product_id cell brings up a table showing product_ids along with product titles from the booklist, and I can scrol down and pick the one I need. Instead of having to remember id product_id or having to open the booklist file to see it.

Can this be done? How do I do it? If anyone has any ideas or has done something similar to it, I will be grateful for their wisdom and help.

Thanks.

Talât


See More: Drop down look up table

Report •


#1
August 29, 2009 at 21:25:34
Would this work?

Taken in pieces...

re: clicking in the product_id cell brings up a table showing product_ids along with product titles from the booklist

I assume the product_id and the product titles are in 2 different columns in Booklist.xls.

In order for both of them to appear in a data-validation drop down, they would need to be concatenated into one cell.

e.g. =A1&" "&B1

re: use the Booklist.xls as a lookup table while it is closed

You can't build a data validation drop down from a closed workbook, but you could replicate the data from the Booklist.xls somewhere else in the Invoice workbook and build the list from there:

e.g. =[Booklist.xls]Sheet1!A1& " " &[Booklist.xls]Sheet1!B1

re: So by inputting the prod_id value pulls up into the invoice line all the other required values.

Since the drop down will show the product id, space, product title, it can't be used as is for the VLOOKUP, but you could extract the product_id and use that.

e.g. With your data validation list in B1, in C1 you could use this to pull data from Column 2 of the lookup_table.

=VLOOKUP(LEFT(B1,FIND(" ",B1)-1),[Booklist.xls]Sheet1!$A$1:$B$3,2)

Putting this all together, in A1:B2 of Booklist.xls you might have:

       A           B
1   Widg-4    Size-4-Widget
2   Widg-5    Size-5-Widget

In AA1:AA2 of your invoice sheet you might have:

=[Booklist.xls]Sheet1!A1& " " &[Booklist.xls]Sheet1!B1
=[Booklist.xls]Sheet1!A2& " " &[Booklist.xls]Sheet1!B2

Which would show:

         AA
1 Widg-4 Size-4-Widget
2 Widg-5 Size-5-Widget

In B1 of your invoice sheet you could put a data validation drop down which pulls it's data from AA1:AA2

Finally, in C1 you would use this VLOOKUP construction to look up just the product_id portion of what you chose from the drop down list:

=VLOOKUP(LEFT(B1,FIND(" ",B1)-1),[Booklist.xls]Sheet1!$A$1:$B$3,2)

Hope that helps.


Report •

#2
August 30, 2009 at 09:00:05
Thank you Derbydad03.

Seems bit complicated at first look. I was hoping it might be much simpler. I was hoping to avoid replicating data from the external file into the invoice.xls file as the booklist is a constantly changing file with new rows being added not necessarily at the end but often somewhere in the middle; that causes problems.

I will try and follow your instructions and see how much progress I make. Will let you know. Hope I can come back to you if I have supplementary questions. Thanks again. :-)


Report •

#3
August 30, 2009 at 16:14:01
It's really not complicated at all.

There's:

- One concatenation formula to put 2 pieces of data together
- One data validation list
- One VLOOKUP formula, with an embedded LEFT() function to take apart the data that the concatenation formula put together.

Sure beats trying to remember - or look up - each product_id every time you need one.

I did a little research and everything I've read says that you can't build a Data Validation list from a closed workbook. The 2 options consistently offered are either open the other workbook hidden, or replicate the data in the workbook that has the dropdown via an external reference like I offered.

There are ways to deal with the addition of rows in Booklist, the easiest being the use of Named ranges.


Report •

Related Solutions

#4
September 11, 2009 at 20:42:13
I am still having no luck with this DerbyDad03.

1. Fine with first step of replicating data into an area of the worksheet concatenating prod_id and Prod_title fields.

2. Also OK with setting up a validation in the cells where I need to select from the drop down list the prod_id.

Problems starts after that:

a) Drop down list is too narrow to see both the prod_id and the full title which can be 30-50+ characters

b) because of the concatenation, I cannot just pick the prod_id. The whole purpose of the drop down list is to enable me to select the correct product and by clicking on it to place the prod_id in the cell. The rest of the excell sheet formulae does the rest. ie populate title, price, index numbers, language etc

b) All that the formula
=VLOOKUP(LEFT(B1,FIND(" ",B1)-1),[Booklist.xls]Sheet1!$A$1:$B$3,2)

does is strip the title back from the concatenated field. and put it in another cell. I dont need that.I already have a lookup formula that pulls that information from the booklist, using teh prod_id value I am trying to select in from the validation cell.

What I need is to be able to see both the prod_id and Prod_title in the drop down list, be able to select the one I need and put in the validation cell just the prod_id. The rest will then be OK.

I still have to find ways of making drop down validation table wider. I have seen some suggestions on how this might be done, but can only start looking into it after I have sorted out the needs above.

May be I am asking for too much from excel....

Thanks for persevering with me on this. I am grateful for all so far. Even if it has not helped to solve teh query, by trying the suggestions, I am learning more and more.

Thanks.

T.


Report •

#5
September 11, 2009 at 22:05:03
First, let me say that I just edited my earlier response because I noticed that my example data didn't match in 2 sections of my post due to a typo. Only the example data was edited, not any of my formulae or text, and it does not make any difference in how the concept that I suggested works.

Keep in mind that this is a concept and is based on my sample data. Obviously the formulae you use will need to be based on your data.

Now, to address your issues:

a) Drop down list is too narrow to see both the prod_id and the full title which can be 30-50+ characters

From:

http://office.microsoft.com/en-us/e...

"The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list."

b) All that the formula

=VLOOKUP(LEFT(B1,FIND(" ",B1)-1),[Booklist.xls]Sheet1!$A$1:$B$3,2)

does is strip the title back from the concatenated field and put it in another cell..

No, that is not what is does. It looks up the prod_id in the table_array in Booklist.xls and returns the title from that sheet.

The reason it appears to just "strip the title back" from the concatenated field is because the title is what is in the 2nd column of the table_array in Booklist.xls.

Look at the sample data I provided.

The original data is in Column A (prod_id) and Column B (prod_title) in Sheet1 of Booklist.xls

This data in concatenated on the invoice sheet and placed in the drop down list, which in my example is in B1 of the Invoice sheet.

The LEFT(B1,FIND(" ",B1)-1 portion of the formula returns the left most characters up to, but not including the space. This is the prod_id in my sample data. This then becomes the lookup_value for the VLOOKUP.

The VLOOKUP then looks up that string in the table_array and returns whatever is in the 2 column.

If you want to prove this to yourself, add a 3rd column to the table_array and change the formula so that the table_array argument is $A$1:$C$3 and the col_index_num is 3. It will then return whatever is in the 3rd column in Booklist.xls proving that it really is looking up the prod_id.

re: What I need is to be able to see both the prod_id and Prod_title in the drop down list, be able to select the one I need and put in the validation cell just the prod_id.

If the "validation cell" is the same cell as the one that contains the drop down (you know, the one created via Data...Validation) then I don't think you can have it both ways - at least not easily. The only way I know of to make the cell wide enough to show both the prod_id and prod_title but only show the prod_id once it is selected from the list is with VBA. Widen the column when you select the drop down cell and make it narrow after the item has been chosen. VBA code could be written for that.

However, keep in mind that just because it only shows the prod_id when narrow, it still contains the prod_title and that has to be stripped off (with the LEFT function) so that the VLOOKUP only looks up the prod_id.

Another option is to have a separate cell that will hold the prod_id once the item is chosen from the drop down. Keep the drop down list wide, use the LEFT function in another cell to extract the prod_id from the longer string and then use that cell as the lookup_value for the VLOOKUP function.

re: I still have to find ways of making drop down validation table wider.

Asked and answered.


Report •

#6
September 12, 2009 at 15:38:42
Hi,

I agree with DerbyDad03 that it is a good idea to have the Booklist.xls data in your Invoice.xls workbook.

If you name a new sheet in Invoice.xls 'List', then Link
the source data in Booklist.xls exactly 'as is', to the new List sheet, i.e., Cell A1 in [Invoice.xls]List!A1 will be linked to cell A1 in Booklist.xls

You mention that you add data in BookList.xls, sometimes in the middle of the data.

If you link using formulas such as =[BookList.xls]Sheet1!$F$13
you will run into problems if you insert new rows into the BookList.xls data, so follow DerbyDad03's advice and use a named range.

Name the data range in Booklist.xls. Any name will do, but make the range one row bigger than you are using now, and when you add new data to the end of Booklist.xls, highlight this empty row and insert a new row for the new item. This will enlarge the named range.

If you add a row in the middle of the Booklist.xls data, the named range will also enlarge
.
Now link the Booklist.xls data to the List sheet in Invoice.xls

In Invoice.xls in Cell A1 on the List sheet, type = (don't click enter)
Swap to Booklist.xls
Hit f3 and a Paste Name box opens,
select the name you gave to your data and click OK
Swap back to Invoice.xls and hit enter, and cell A1 will have a link to A1 in Booklist.xls

I named my data range BListData
The formula in Invoice.xls List Cell A1 is now
=Booklist.xls!BListData
(note - there are no cell addresses visible when you link to a named range).
Just drag the formula in Cell A1 across the columns then down, until it is the same size as the original.

All the data will show up.

As the data is linked, when you open Invoice.xls even when Booklist.xls is closed, you will get a message asking you if you want to update the linked data. Just Click 'Update' and new or changed data in your linked cells will appear.
No need to open Booklist.xls.

Regards


Report •

#7
September 14, 2009 at 07:10:57
Thanks Humar.

I am trying your suggestion of linking with a named range:

* Created a new sheet and called it "Sheet1
"
* Named the range in Booklist as you suggested , whole range + one extra row and called it Prods

* in cell A! of Sheet1 put in = and went to teh open Booklist, F3, chose the named range, OK and back into cell A1 in Sheet1 and enter. ===> all I get is #VALUE!

I think I can see now what DerbyDad03 was trying to do with the formula

=VLOOKUP(LEFT(B1,FIND(" ",B1)-1),[Booklist.xls]Sheet1!$A$1:$B$3,2)

I think if I get over the #VALUE! problem, I will have made a big progress.

Thnx again .

T.


Report •

#8
September 14, 2009 at 07:25:17
re: in cell A! of Sheet1 put in = and went to teh open Booklist, F3, chose the named range, OK and back into cell A1 in Sheet1 and enter

You can't set a single cell equal to an entire Range. Think about that - How would you put all of the values in B1:C45 into a single cell?

What you can do is use the Range Name in a formula. e.g.

=SUM(Prods)

or

=VLOOKUP(C1,Prods,2,0))

or create a Data Validation list:

List...Source...=INDIRECT("Prods")

etc.


Report •

#9
September 14, 2009 at 08:17:11
Hmmmm

I was following Humar's suggestion
----------------o---------------------
Now link the Booklist.xls data to the List sheet in Invoice.xls

In Invoice.xls in Cell A1 on the List sheet, type = (don't click enter)
Swap to Booklist.xls
Hit f3 and a Paste Name box opens,
select the name you gave to your data and click OK
Swap back to Invoice.xls and hit enter, and cell A1 will have a link to A1 in Booklist.xls

I named my data range BListData
The formula in Invoice.xls List Cell A1 is now
=Booklist.xls!BListData
(note - there are no cell addresses visible when you link to a named range).
Just drag the formula in Cell A1 across the columns then down, until it is the same size as the original.

------------------o--------------------

have I missed a step?

In cell A1 the formula I get is the same
=BookLIST.XLS!Prods

but the vale show is an error. I am throroughly confused now...


Report •

#10
October 12, 2009 at 07:01:31
OK Derbydad03 and Humar.

After a bit of silence I am back on this again.
During the silent period, I decided to try a difefrent route, whcih worked quite well up to a point and now I am stuck again. Hence seeking your wisdom and experience :-)

I am now toying with the idea of of using a combo box to select from a list a particular product and put the value in a cell. It works fine, although I have some other aspects of it which I will try and sort later.

My immediate problem now is that, I need to repeat the selection process from the Combobox, for each line of the invoice ( some 10 lines) on each occasion droping the value in a different cell. ie D20, D21, D22, D30. Is there a way of doing that? I am using the linkedcell function of the Combobox property. and it works well for the first cell that the combo box is linked to. Is there a way of using a VBA incremental function to increase d20 to d30 step by step?

Thanks for any help.

Talât :-)


Report •

#11
October 12, 2009 at 08:21:51
I have a suggestion, but I would prefer if you posted your question in a separate thread, maybe with a subject line like "Increment ComboBox Linked Cell".

We were discussing VLOOKUP, etc. and now we're discussing ComboBox code, so these 2 discussions should be in separate threads.

Thanks!


Report •


Ask Question