Offseting referenced Cells

**kickme** · 09-27-2012, 05:15 AM

Hi,
I have a data set in range A1:D12. I create plots using variouos data from these range: e.q

X Y1 Y2 Y3
Const A1 A4 A7
Const A2 A5 A8

X Y1 Y2 Y3
Const A4 A7 A10
Const A6 A9 A12

and so on

as you can see value in Y2 and Y3 increased by a const no

I use following formula
X Y1 Y2 Y3
Const Indirect("A1") Offset(Indirect("A1"),2,0) Offset(Indirect("A1"),4,0)
Const Offset(Indirect("A1"),0,1) Offset(Indirect("A1"),2,1) Offset(Indirect("A1"),4,1)

as you can see when I have to create next chart I need to change all formulas and offsets (some times)...

Can anybody can suggest optimised method for this....?

Thanks...

**ratcat** · 09-27-2012, 05:39 AM

Not use what your after and what information you have to input to get the needed result but.......

have you tried the INDEX and MATCH combo.

eg =index(B3:C12,MATCH(blah,blah),MATCH(blah,blah))

**kickme** · 09-27-2012, 05:51 AM

No Index will not work, as again I have to type all the index range and values.

I want to enter only a single cell reference e.q Indirect("A1") in a cell (say Z100) Values in ZA101 to ZE101 and ZA102 to ZE105 shall be entered as Z100+2 (which will actually result in value of A1+2 ie A3 and so on. So when I copy this set of formulas I will only change to Indirect("A1") to say Indirect("A5") and all other will now reference relative to A5 instread of A1.