ComboBox Question -- Excel and VB

[palmese]

I am creating an Excel expense report template for my company. It contains ComboBoxes for users to select different projects to charge their expenses to. On the bottom of the template is an area that will be used for the accounts payable clerks to key in information based on the ComboBoxes.

My question is whether there is a way to write a LOOKUP formula to pull what was selected in the ComboBox to a specific cell. In a perfect world the function would work like the following: =IF("ComboBox1"="","",LOOKUP(------)).

Any ideas on the appropriate function and or VB code to get me there. THANKS!!!

 

[SkipVought]

HI,

The value selected is ComboBox1.Value

You can put that value in a particular cell, lets say A1...

[A1].Value = ComboBox1.Value

Then you lookup is based on the value in A1

=VLOOKUP(A1,LookupTable,LookupColumn,FALSE)

You might modify the above formula to accomodate a non-existant lookup value using the ISERROR function.

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[palmese]

THANKS SKIP!!!

Unfortunately, I think I might be messing up the function. I am typing the following in the cell:

=Value("ComboBox1.Value&quot

it is returning the #Value! Excel Error. Any suggestions. Thanks for your help.

 

[SkipVought]

Are you expecting a numeric value? This function converts a string of digits to a numeric value. Skip,
SkipAndMary1017@mindspring.com

 

[palmese]

No, say the user selected the following from the ComboBox "Project1".

A cell would populate the text value from the ComboBox(i.e. Project1). Then from there I would use the VLOOKUP to specify the appropriate relationship (in this case if Project1 was selected - I would put in the applicable accounting code for that project in a VLOOKUP Table).

I hope I am making sense. Please advise. Thanks!

 

[SkipVought]

The VALUE function converts numeric text digits to a numeric value.

Why didn't you use...

=VLOOKUP(A1,LookupTable,LookupColumn,FALSE)

where A1 would contain "Project1" and the VLOOKUP function would return the accounting code. Skip,
SkipAndMary1017@mindspring.com

 

[palmese]

Because I need to populate cell A1 with the ComboBox selection (there are 12 to choose from). Sorry for the confusion.

 

[SkipVought]

Well, that is EXACTLY what I am suggesting! A1 get the value of the ComboBox and the VLOOKUP function returns you accounting code to WHATEVER cell you decide to put it in. Skip,
SkipAndMary1017@mindspring.com

 

[palmese]

my bad...it was a VB Code problem I had related to the Reset button I had set up (basically it wasn't saving my LinkedCell). you have been a great help and i thank you very much. take care.