Ref., row data to column

[djc]

hi i am trying to use column data on sheet 1 to reflect calculation in sheet
2 in column. for exp.
=IF('55LHX'!$K$6=0,'55LHX'!$J$6,'55LHX'!$K$6)*'IRPlan And ST'!$B$6
trying to avoid type in each sell ref., on the sheet2

 

[Max]

=IF('55LHX'!$K$6=0,'55LHX'!$J$6,'55LHX'!$K$6)*'IRPlan And ST'!$B$6

It really depends how the above start cell formula in Sheet2 should be
propagated. The $ signs for the cell refs make it absolute, ie fixed (doesn't
change). Remove the $ sign(s), and it becomes relative, meaning it will
increment correspondingly as you copy it across or down.

As an example, for your expression, if: 'IRPlan And ST'!$B$6 is to remain
fixed while the rest are to increment correspondingly as you copy down, make
it:
=IF('55LHX'!K6=0,'55LHX'!J6,'55LHX'!K6)*'IRPlan And ST'!$B$6
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Max]

On 2nd thought, focusing on your subject line ..

Maybe you want to increment it down as you copy across
=IF(OFFSET('55LHX'!$K$6,COLUMNS($A:A)-1,)=0,OFFSET('55LHX'!$J$6,COLUMNS($A:A)-1,),OFFSET('55LHX'!$K$6,COLUMNS($A:A)-1,))*'IRPlan And ST'!$B$6

Or the other way around,
maybe you want to increment it across as you copy down
=IF(OFFSET('55LHX'!$K$6,,ROWS($1:1)-1)=0,OFFSET('55LHX'!$J$6,,ROWS($1:1)-1),OFFSET('55LHX'!$K$6,,ROWS($1:1)-1))*'IRPlan And ST'!$B$6

Both expressions above presume this term remains fixed:
'IRPlan And ST'!$B$6
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[djc]

Max
I didn't make the question clear.
I have ref., sheet1 sorted rows, and trying apply those row ref., to
calculation in column format in sheet2 without kept on typing the cell ref.,
in sheet 1
A
data 1
data 2
data 3
data 4

in sheet 2

a b
c
=if('sheet1'data1**) =if('sheet1'data2***)
=if('sheet1'data3***)


in sheet

 

[Max]

Yes, think I realized that, on 2nd thought. Pl see my other response to you
for one way to achieve the dynamic transpose using OFFSET.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[djc]

Thanks... Max this is the exact solution i was looking for~~~~

 

[Max]

Welcome, pl press the YES button (like the one below) in that response
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik