How to read large data set at hourly interval?

Reference data within

• 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 If manufacturer and model are enough to unambiguously identify the product I would include a column in your database that was a concatenation of the two columns. Then pick that from a dropdown list and use it to look up the relevant information. Since the combination column would be used in the VLOOKUP, it should be on the left. So, with the manufacturer in column B and the model in column C, you would have formula in column A that was =a2 & b2 on each row. You could hide the column if you want. Then assign the data that results from the formula in column A to a dropdown. The VLOOKUP formula can be a little confusing, but, it is pretty powerful and it can be made simpler if you use range names. For example, if you assign the name "database" to all your data, with the concatenated manufacturer-model on the left, then, =VLOOKUP(x,database,4, false) would be the cost. The 5 because you want the data in the fifth column. x is the combined manufacturer-model that you picked; alternatively you could make selections form two dropdowns, have them populate two cells that are concatenated in another cell somewhere, and have that cell used in the place of x in the VLOOKUP. The fourth argument, of False, makes is so you you will get an error if there is no exact match. If you were looking up something in a tax table, for example, you would not necessarily be looking for an exact match adn the fourth argument would be True, or omitted. In your case, I assume you want only exact matches. If you make model and manufacturer selections from data that is definitely in the model and manufacturer columns, it shouldn't make any difference though as you should always get an exact match. ---------- FOLLOW-UP ---------- QUESTION: Awesome. Got it... I had to use dependent drop down list which I learned from here:http://www.contextures.com/xlDataVal13.html 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? Steve ANSWER: Steve There is no reason the master data can't be in another workbook. You can see the syntax of the link by simply typing = then pointing the cell on the "master" worksheet. References will be absolute by default when addressing a different workbook. The worksheet containing the "master" data would have to be opened if the data changes. You could set it up so that a read-only version of the master file is opened when the file referencing the data is opened. That would work if the typical user referencing the data will not be making changes to the "master" data. You could also use Get External Data, but, I am not the one to help you on that method as I've never used it. Good luck. ---------- FOLLOW-UP ---------- QUESTION: I think I am going to try the first option you described above. What I can't seem to figure out is how to reference a "name" within the master data workbook. I see that the syntax for linking to another workbook is something like this: ='[proposal test database.xlsx]items'!$C$2 but how do I reference a group of data that is defined by a name with the workbook above? Also, I have found that I can reference workbooks on in different directories. Like this: "In Windows, if the workbook is in a different directory or on a different drive, enter: 'G:\DATA\CHEM\[MAJOR95.XLS]MAJOR95'!$A$4 " I'm assuming that in the above example the "source" workbook does not actually have to be opened to work. Do you know if that is true? Steve QUESTION: I need help determining if what I am trying to do is possible within Excel. 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. 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, and cost. Example: manufacture  : model  : description  : cost Sony XBR42 42" TV 1400 Panasonic KYR58 58" Plasma 2800 Now the question: 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. Is this possible within Excel? Steve ANSWER: Yes it is possible ---------- FOLLOW-UP ---------- QUESTION: What command/tool would I use on the "proposal" sheet to call/lookup the data on the "items" sheet? ANSWER: It depends on how the data where you pull from is arranged and how nuch data would need to be pulled. If you only need to pull one row of data then you can use the =VLOOLKUP function. You would need a separate function for each column of data you need to pull if you are only pulling one row of data. See help on =VLOOKUP ---------- FOLLOW-UP ---------- QUESTION: 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?

• Answer:

  Steve Contextures is a great site; lots of good information. The link to another workbook should look like G:\DATA\CHEM\[MAJOR95.XLS]MAJOR95'!RangeName The remote file doesn't have to be open for the link to work; but, it does have to be open for the link to update. There are several related functions similar to VLOOKUP but they all can only capture one cell's value. VLOOKUP is easy, I think. Here is an example: A1 contains the data you want to find in a table that is in C1:E10, for example. And you want to get the data from column D =VLOOKUP(A1,$C$1:$E$10,2.FALSE) Column C MUST have the data values that you are trying to find. So, whatever is in A1 must be in column C somewhere