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)*'IRP lan And ST'!$B$6
trying to avoid type in each sell ref., on the sheet2

=IF('55LHX'!$K$6=0,'55LHX'!$J$6,'55LHX'!$K$6)*'IRP lan 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
---
"djc" wrote:
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)*'IRP lan And ST'!$B$6
trying to avoid type in each sell ref., on the sheet2

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" wrote:
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)*'IRP lan And ST'!$B$6
trying to avoid type in each sell ref., on the sheet2

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" wrote:

=IF('55LHX'!$K$6=0,'55LHX'!$J$6,'55LHX'!$K$6)*'IRP lan 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
---
"djc" wrote:
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)*'IRP lan And ST'!$B$6
trying to avoid type in each sell ref., on the sheet2

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

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

"Max" wrote:

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" wrote:
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)*'IRP lan And ST'!$B$6
trying to avoid type in each sell ref., on the sheet2

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
---
"djc" wrote:
Thanks... Max this is the exact solution i was looking for~~~~