Help in Linking Excel data from different excel files.

[jay1990]

Hi Everyone,

I have a problem with the below table. Request you to please help me.

There are 2 sheets.

1st Sheet contains predefined details mentioned below

Row 1: Product Name / Details
Row 2: Price for State 1
Row 3: Price for State 2
Row 4: Price for State 3

2nd Sheet is a Daily Report, wherein we calculate the total orders.

If I enter Product Name with State details, automatically the price which is allocated in the sheet 1 has to be reflected in the sheet 2.

Regards,
Jay

 

[yosegmail]

in sheet 1 you may have

A B C D
13 product price1 price2 price3
14 product1 19 34 15
15 product2 48 30 44

name the range cell A14:A21 as product ..

on sheet2

A B C D
1

on cell A1 do data validation .. Alt + D + L... list ... tab f3... select product..
then on cell B1 copy and drag this formula
=INDEX(Sheet1!$B$14:$D$21,MATCH($A2,Sheet1!$A$14:$A$21,0),MATCH(B$1,$B$1:$D$1,0))

 

[jay1990]

in sheet 1 you may have

A B C D
13 product price1 price2 price3
14 product1 19 34 15
15 product2 48 30 44

name the range cell A14:A21 as product ..

on sheet2

A B C D
1

on cell A1 do data validation .. Alt + D + L... list ... tab f3... select product..
then on cell B1 copy and drag this formula
=INDEX(Sheet1!$B$14:$D$21,MATCH($A2,Sheet1!$A$14:$A$21,0),MATCH(B$1,$B$1:$D$1,0))

Dear, Could you pls help me out in the below format.

SHEET 1: Predefined Values and Product details

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Price 1[/TD]
[TD]Price 2[/TD]
[TD]Price 3[/TD]
[TD]Price 4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]125[/TD]
[TD]150[/TD]
[TD]185[/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]256[/TD]
[TD]350[/TD]
[TD]375[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]235[/TD]
[TD]270[/TD]
[TD]325[/TD]
[TD]380[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]290[/TD]
[TD]350[/TD]
[TD]420[/TD]
[TD]480[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]78[/TD]
[TD]95[/TD]
[TD]125[/TD]
[TD]185[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]95[/TD]
[TD]125[/TD]
[TD]150[/TD]
[TD]195[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]869[/TD]
[TD]920[/TD]
[TD]965[/TD]
[TD]989[/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2: Daily working sheet

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Price Type[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Price 1 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Price 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Price 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Price 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Price 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Price 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Price 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Price 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Price 3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please let me know the formulae in the Price column =INDEX(Sheet1!$B$2:$E$9,MATCH($A2,Sheet1!$A$2:$A$9,0),MATCH(B$2,$C$1:$C$8,0))