I have a spreadsheet that is produced from another programme that can be directly dumped into excel. This si sheet "Prog dump" in the example. The first sheet, called "AS" is a shortened version of this sheet. For those that are interested, this is a Primavera P6 programme copied over as an excel dump, with the "AS" sheet being a cost-only sheet so that costs can be calculated. There is a second part to this sheet that organises these costs by week to give a running total of weekly costs, but this is not part of the current sheet.



The Problem
I need to take the value in column A (Activity ID) on sheet "AS" then copy the values for "finish date", "budgeted material cost", "actual total cost" from sheet "prog dump" (columns G, I, J) to sheet "AS" (Columns D, F, G) for the same value of Activity ID".

I am fairly sure that this is easily achievable with a lookup function, but my knowledge of this is extremely limited, and the helps I have found so far don't really clarify it. There are another 1200 or so lines in the spreadsheet, so a manual isn't possible, and the values will change on a 2 weekly basis, so it needs to update when new prog dump data is entered.

any help would be greatly appreciated.

thanks.

EDIT: Also posted at
http://forums.techguy.org/business-a...ies-value.html

Attached Files

example.xlsx (94.1 KB, 10 views)

Enter into the AS sheet for Finish:

=VLOOKUP(AS!Axx,'Prog dump'!A:L,7,FALSE)

Where xx is the row number. Copy to the rest of the sheet.

The 7 in the above equation is the column index number Finish in 'Prog dump'. For the Budgeted Material Cost, it is 9. For Annual Total Cost, it is 10. Otherwise, the equations are identical.

thanks Gebobs

I had a playaround with vlookup, and reached about the same conclusion. I was having problems with the true/false at the end of the statement as it seemed counter intuitive, but your confirmation has givnm me a bit more confidence. Thanks

I agree that it is counterintuitive.

I always use false because I am always looking for an exact match and don't want the equation to return a result that is not really for that value. Also, when using false, the lookup table does not have to be sorted.

Let me know if you need any more help.