Quote:
Originally Posted by trebor200
Hi Thanks for your reply, that sound like a good idea, i am not sure how to use index and match as i usually just use the Vlookup and Hlookup.
I would be bring the date in via SQL into a Excel template where a pivot table would be updated using refresh or a macro, the reason for the layout of the spreadsheet that it would be a pivot table of the order-book.
If we could not format in pivot table i could lok at bring the data into the spreadsheet via query and change the date there to Jan-12.
Ill have a look at this also,
|
Right, if you change the date formats so on the "Data" tab cell
D2 shows as
Jan-12,
G2 is
Feb-12,
J2 is
Mar-12 etc...
Then on the "Orderbook" tab,
B2 is
Jan-12,
C2 is
Feb-12,
D2 is
Mar-12 etc...
The formula you'll need in cell D3 on the Data tab is :
=INDEX(Orderbook!$A$2:$Y$8,MATCH(Data!$A3,Orderboo k!$A$2:$A$8,0),MATCH(Data!D$2,Orderbook!$A$2:$Y$2, 0))
Make sure all the $'s are in the right place and you will be able to copy this formula to all of the orange cells in rows 3-8 on the Data tab and it's job done.
If you need any help with that the formula is actually doing just let me know :)
Micks suggestion above would work too, and would probably be simpler, but if you ever have to change the dates, add 'parts' to the list or a number of other changes, then this approach will be more easily adapted to fit that.