Any Excel Experts out there?

C

Chillimonster

Messages
4,771
Name
Chris
Edit My Images
Yes
I'm re-writing some spreadsheets at work with some VB in the backgorund.

One of the features that has been requested is for the form to have the facility of a drop-down list that pulls prices and item details from a seperate spreadsheet.

I can do the drop down list if the info is within the same workbook, but i want it on a seperate workbook so that we can maintain the price list seperately.

Any help / pointers would be very much appreciated :)

Chris
 
You need to use vlookup don't you? We done this sort of thing in IT class a few years ago. I cant remember exactly how its done but maybe google vlookup?

Regards, James
 
For the cells that are the content of the drop down use a formula that references the relevant cells on the other sheet/book.
 
I *think* (and havent tried this out) is that you can only link to another sheet when you name the range for the drop down list

I.e. in the other sheet, highlight all the options, then type a name in the space to the left of the fx bar, you should then be able to refer to it :)

Let us know if it works :D

Dave.
 
On your 'form' spreadsheet, create your drop down list via "Data Validation" to get the item description - but when you point to the "Area" you want, first change to the other spreadsheet (both need to be open to start this off) by pointing to the "Windows" menu, then navigate to the area where you have the items listed, press F4 to make it an absolute reference.

Back on the 'form' spreadsheet - in the cell next to the item description, insert the your Vlookup formula:

= VLOOKUP (cell name with results from Drop down list, "Area" see above, 2 this will give you the results of the second column which will be your price)

By keeping the item description and price separate, you can then do calculations on the price, if you wanted to combine the two, ie description and price, , I would do this later by means of the =TEXT function with the use of the ampersand which is a concatenation operator that joins the text with the contents of another cell.

I always find it easier to do these things in a simple manner perhaps through several steps rather than creating a monster formula or VB.

Hope that helps.
 
You must log in or register to reply here.
Back
Top