Offset Function for Block of Data

C

Cheryl

Is there anyway to apply offset for the following. If not, is there anyway I can do it?
Sheet 1 A1 = Day, ie if I can key 1, it means day 1, 2 means day 2 and so on
Row 2 - Location A, Row 3 - Adjustment, Row 4 - Location B in sheet 1
Row 2 - Location A, Row 3 - Location B in sheet 2
Column B - Product 1, Column C - Product 2 in Sheet 1
Column B - Product 1, Column C - Product 2 in Sheet 2 for Day 1
Column D - Product 1, Column E - Product 2 in Sheet 2 for Day 2 and so on
When I key "1" in sheet 1 A1, it will extract figures
From Sheet 2, B2 to Sheet 1, B2
From Sheet 2, B3 to Sheet 1, B4
When I key "2" in sheet 1 A1, it will extract figures
From Sheet 2, D2 to Sheet 1, B2
From Sheet 2, E3 to Sheet 1, C4
Thanks in advance
 
F

Frank Kabel

Hi
at least for me your description is a little bit confusing. Also you
seam to want the values transfered to different cells?
If you like email me your file and I'll have a look at it
 
F

Frank Kabel

Hi
I sent your file back to you. In your case an array formula with
INDEX/MATCH worked. e.g. for your example use the following array
formula (entered with CTRL+SHIFT+ENTER) in cell B2:
=INDEX('2'!$A$1:$E$4,MATCH('1'!$A3,'2'!$A$1:$A$4,0),MATCH(1,("Day "&
'1'!$B$1='2'!$A$1:$E$1)*('1'!B$2='2'!$A$2:$E$2),0))
 
Top