Microsoft Excel 2007

[Bill Sublette]

Okay so I know this isn't a question for MS Access, but this is as close as I could find for Excel. So here's my question:

I have 2 pages set up with data pulling from one to populate the other. That formula works without an issue, but one formula seems to be kicking my butt. I need it to read a date on one page and when you put in that specific date it will then populate pre-filled data onto the first sheet. For example the second page has the date 11/24/10 with 100 hrs and 1000 units. I then enter in the 11/24/10 date on the first page. I would like the 100 hrs and 1000 units to populate on the first page. Quick note; I have multiple dates on the second page for data population along with multiple other data fields that fills in on the first page... I think I explained it for the most part. I need it to read all the dates on the second page, and when the date on the first page matches one of those dates, I need it to pull data from the second page and populate the first.

Any assistance would be greatly appreciated.

Thanks!

[copleyuk]

Not sure if I'll get in to trouble for answering this... sorry in advance if I shouldn't have!

However, I think that you need the VLookup function.

The Lookup_Value being either the cell or range of cells you want to look for (i.e the cell you are putting the date in)

Table_Array being the range of cells with the values to search and return (i.e 100hrs and 1000 units)

Col_index_num is the number of the column you want the data on that row to be returned (i.e if data is stored as 12/24/10 in col A and 100 Hrs in col B and 1000 units in col C and you want to return 100hrs set this value as 2)

Range_Lookup is a logic test, do you want to find an exact match or one similar...

Once you have the expression for one cell copy and paste for the rest of the table, remember to increase the Col_Index value to return the value stored in different columns.

Hope this makes sense - it's not the easiest of things to explain without something to point at!

Carl

[NeoPa]

You're absolutely right Carl :-) Here is a link to Using VLookUp in Excel to Link to Excel 'Tables'.

Each of the values in the table which is required to be transferred, would need its own formula in a separate cell. VLookup() only returns a single value. Carl has it pretty well covered though.

Let us know if you still have any difficulties.