Data lookup between sheets using a lookup reference

[Caldew]

I have a 2-sheet spreadsheet: Sheet 1 contains data which is refreshed using ODBC. Col D contains report codes (each code might be repeated several times in col D). Sheet 2 is to be used to report on the sheet 1 data, accumulated by report code - effectively sheet 2 is a P&L account based on range data in sheet 1. If the lines of data in sheet 1 were constant, then bringing in the data sorted would solve the problem. However, regularly, additional lines of data will be imported and the trick is to ensure that the new lines are included in the sheet 2 analysis for each report code. The sheet 1 data for each report code will be accumulated over a range. For example, a report code EMS1 might be repeated 20 times in 1000 lines in sheet 1. The data to accumulate for the 1st quarter will be in columns Q, R and S. I have tried using VLOOKUP but this has not worked.

Any help on this one would be appreciated.

Thankyou

 

[koresnordic]

Have you tried a pivot table? Place the report code in the left hand side and columns Q R & S in the middle with the sum option.



Graham

 

[RogADog]

If I understand the problem correctly then a number of conditional sum formulas might do the trick. If you've never used one, go to Tools > AddIns and install the Conditional Sum Wizard then go to Tools > Wizards > Conditional Sum to run it. Make sure that the ranges you use include sufficient spare rows to cover the new lines of data you might need. Above relates to Excel 97 but I would imagine later versions are similar.

Hope it helps.

 

[Caldew]

Thanks for that. Yes I did consider a pivot table, but it is not appropriate for the type of formatted output I am looking for. However, I have, I think, solved the problem, using SUMPRODUCT.

Many thanks.

 

[Caldew]

Thanks, I will also have a look at conditional sum formulas.

 

[xlbo]

Have a look at the SUMIF function
As to referencing a moving range - the query should have an option to set a name - this name can be referenced as a normal named range. However, this will not work for you as it will cover ALL columns, not just starting from D so....you need to set up some dynamic range names.
I would suggest 1 for col D and another for QR&S:
If the sheet is called Data, then go
Insert>Name>Define
Give the range a name - dyCodes
and enter this in the box provided:
=OFFSET(Data!$D$1,0,0,COUNTA(Data!$D:$D),1)
Then for QRS, give it a name - dyVals
=OFFSET(Data!$Q$1,0,0,COUNTA(Data!$Q:$Q),3)

then your sumif formula would look like this (if you have an object code in A2, in B2:
=SUMIF(dyCodes=A2,dyVals)

this will sum all values in cols Q, R and S where the code is the same as in A2...

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244