Hi,

I am new to the forum and would apreciate some help / guidence if possible.

I have a scnario where i have 2 sheets, and in the first sheet (Data) i want to return a value to cell D3 from an orderbook in orderbook tab.

Data Tab: I need to match a part number in B3 and year in D1 and Month in D2 then match these to values in orderbook tab and return the value.

I have attached an example for reference, hopefully someone can make sense of this, much appreciated for any help.

Excel 2007

Hi,
This is easily done using INDEX and MATCH but it would rely on a slight change to your spreadsheet.

Rather than having just a month in row 2 and a year in row 1, if you combined them so the date in B2 on the orderbook tab showed as Jan-12 and all date cells followed that format, including the date cells on the data tab. Would that be suitable for you?

If so I will show the formula you need. If not I will have another think. :)

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,

On 22/03/2012 7:59 AM, trebor200 wrote:
Hi,

I am new to the forum and would apreciate some help / guidence if
possible.

I have a scnario where i have 2 sheets, and in the first sheet (Data) i
want to return a value to cell D3 from an orderbook in orderbook tab.

Data Tab: I need to match a part number in B3 and year in D1 and Month
in D2 then match these to values in orderbook tab and return the value.

I have attached an example for reference, hopefully someone can make
sense of this, much appreciated for any help.

Excel 2007


+-------------------------------------------------------------------+
|Filename: Trial.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=310|
+-------------------------------------------------------------------+



Hi

Not sure I agree with your layout, but then I am not privy to what it is
your doing with the sheets. That Said! from what I can gather you only
need to do the following:

In Sheet("Data") - Cell("D3") formula is =Orderbook!B3
This gives a result: Jan = 7, which-in-turn gives your other formulas
the following: Fit = 18, Weld = 21

So, to expand this:

Jan-D3: =Orderbook!B3
Feb-G3: =Orderbook!C3
Mar-J3: =Orderbook!D3
Apr-M3: =Orderbook!E3
May-P3: =Orderbook!F3
Jun-S3: =Orderbook!G3
Jul-V3: =Orderbook!H3
Aug-Y3: =Orderbook!I3
Sep-AB3: =Orderbook!J3
Oct-AE3: =Orderbook!K3
Nov-AH3: =Orderbook!L3
Dec-AK3: =Orderbook!M3

Select D3:AM3 and Copy
Select D4:D8 then Right-Click | PasteSpecial | Formula's

Then you can copy the entire range of D3:AM8 and select AN3 and and
repeat PasteSpecial Formula's to 2013. It should continue the Formula's
with no problems as they are not absolute.

HTH
Mick.

I am heading away for a few days so if this is not what you are looking
for be patient and one of the other contributors may help.

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.