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

Product	Price 1	Price 2	Price 3	Price 4
A	125	150	185	225
B	256	350	375	450
C	235	270	325	380
D	290	350	420	480
E	78	95	125	185
F	95	125	150	195
G	869	920	965	989

<tbody>
</tbody>

SHEET 2: Daily working sheet

Product	Price Type	Price
B	Price 1
C	Price 2
A	Price 1
E	Price 1
A	Price 3
C	Price 2
D	Price 1
E	Price 2
D	Price 3

<tbody>
</tbody>

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))