Reference data on within

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:52 PM GMT

Question

QUESTION: I need to build a proposal template that will continually reference an item list on either a separatefile or simply an additional worksheet within the same workbook. Preferably a separate file, however I will take whatever I can get.


My first thought was to make a worksheet (called "items") containing all items in a list (or database) with columns for things like manufacture, model, description, cost, labor.


Example of "items" worksheet:

manufacture  : model  : description  : cost  : labor

Sony XBR42 42" TV 1400 1

Panasonic KYR58 58" Plasma 2800 1


On the "proposal" sheet I need to somehow quickly reference the "items" and pull the data into the proposal sheet... maybe by use of a drop down box selecting only the Manufacture and Model, then haveautomatically populate the additional fields. I will need to pull at least three additional fields which will each have there own column in the "items" list. So from what I can tell VLOOKUP won't work. Also, VLOOKUP doesn't have a very "user friendly interface".


So in total, there are five fields, two (manufacture and model) which would be used to identify any particular item. And three which would be pulled and populate new cells.


Is there another method to do this other than VLOOKUP that would allow for drop down lists?

Steve

ANSWER: Steve,


You can have the data in another workbook, but both the workbooks would need to be open.

You can do the dropdowns using data validation. But a dropdown will need a list of items to include in the dropdown.

The vlookup table can be set up so the left most column is a concatenation of manufacturer and model (create a dummy column), so it should work fine that way.

The challenge I would see is in the model dropdown, restricting it to models that go with the manufacturer selected. This could probably be done, but it might be easier to do it using VBA code than trying to do it with formulas.

You can look at Debra Dalgleish's site on dependent dropdowns if you want to go with setting up the data to just use formulas:

http://www.contextures.com/tiptech.html look under the D entries for topics on Data Validation Dropdowns. She has a specific listing for dependent dropdowns. I think if you go that way you would probably be set off having the information in the same workbook.

The question you have stated is very broad and somewhat involved - much to much to try to describe a step by step solution here. If you need more detailed help on it you can send me you workbook with the data and what you are trying to do to


Tell me any constraints and I will try to help you out with some suggested solutions.



---------- FOLLOW-UP ----------

QUESTION: Awesome. Got it...

I used the dependent drop downs method you mentioned along with the Vlookup.

Now that I have this covered. Do you know of a way to have a master "items" list that could be updated frequently and accessed from multiple people?

Could the "items" be a separate workbook? Or could it be updated using some kind of "Get External Data" command?

Would VBA code allow this to be done easier?

Steve

Answer

Steve,

I don't know what the capabilities are of your company. Most companies that deal with that type of situation use a database server such as SQL Server, Orcle or something like mysql. This allows simultaneous updating and acess of data. supports drawing data from such a database server. A smaller operation can be done with microsoft Access. Again,supports getting data from an MS Access file.

Excel itself allows you to share workbooks, but this has a lot of limitations (I have never used it). If there isn't an issue with multiple people needing to update the workbook at the same time, you could just use a single normal workbook on a common drive and have people make the updates.

So assuming you have such a database (not workbook), you could have a query inand pull in the items to that workbook on a hidden sheet or even a sheet that isn't hidden and then have your dropdowns pull from that data.

If you used a shared workbook or even just a common workbook, you could use a linking formula in a separate sheet that pulls in the data from that common source. Then use you formulas to draw the information you need.

VBA code can facilitate this process, but the right answer would depend on the specific situation.

I know this response is pretty general in nature, but there are many options available depending on what is at your disposal.

Advertisement

©2021 eLuminary LLC. All rights reserved.