Hi,
This formula has references to other sheets in the same file.
I have this formula in A1 : =IF('47'!$B$1<0;'47'!$B$1;" ")
I have this formula in A2 : =IF('48'!$B$1<0;'48'!$B$1;" ")
and so on and so on.
I want to copy this formula in about 50 rows. So i tried to click it and
move it to others cells. But the formula stays the same. I thought the
numbers 47 and 48 should increase with 1, but they don't.
How could I solve this ?

Bart

Change it to

=IF(INDIRECT("'"&ROW(A47)&"'!$B$1"<0;INDIRECT("'" &ROW(A47)&"'!$B$1;" ")

and copy down


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bart" wrote in message
...
Hi,
This formula has references to other sheets in the same file.
I have this formula in A1 : =IF('47'!$B$1<0;'47'!$B$1;" ")
I have this formula in A2 : =IF('48'!$B$1<0;'48'!$B$1;" ")
and so on and so on.
I want to copy this formula in about 50 rows. So i tried to click it and
move it to others cells. But the formula stays the same. I thought the
numbers 47 and 48 should increase with 1, but they don't.
How could I solve this ?

Bart

Thank you Bob, but it's not the solution I think.
You refer to A47, which is a cell, but i want to refer to a sheet which is
called '47'. To make it easier to understand i changed the formula :

I have this formula in A1 : =IF('sheet47'!$B$1<0;'sheet47'!$B$1;" ")
I have this formula in A2 : =IF('sheet48'!$B$1<0;'sheet48'!$B$1;" ")

So i want to make 'sheet47' variable, so it counts to sheet49, sheet50,
sheet51, ...

Is this more clear ?



"Bob Phillips" wrote:

Change it to

=IF(INDIRECT("'"&ROW(A47)&"'!$B$1"<0;INDIRECT("'" &ROW(A47)&"'!$B$1;" ")

and copy down


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bart" wrote in message
...
Hi,
This formula has references to other sheets in the same file.
I have this formula in A1 : =IF('47'!$B$1<0;'47'!$B$1;" ")
I have this formula in A2 : =IF('48'!$B$1<0;'48'!$B$1;" ")
and so on and so on.
I want to copy this formula in about 50 rows. So i tried to click it and
move it to others cells. But the formula stays the same. I thought the
numbers 47 and 48 should increase with 1, but they don't.
How could I solve this ?

Bart

I know what you want, but there was an error ijn my previous post

Try

=IF(INDIRECT("'"&ROW(A47)&"'!$B$1")<0;INDIRECT("' "&ROW(A47)&"'!$B$1");"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bart" wrote in message
...
Thank you Bob, but it's not the solution I think.
You refer to A47, which is a cell, but i want to refer to a sheet which is
called '47'. To make it easier to understand i changed the formula :

I have this formula in A1 : =IF('sheet47'!$B$1<0;'sheet47'!$B$1;" ")
I have this formula in A2 : =IF('sheet48'!$B$1<0;'sheet48'!$B$1;" ")

So i want to make 'sheet47' variable, so it counts to sheet49, sheet50,
sheet51, ...

Is this more clear ?



"Bob Phillips" wrote:

Change it to

=IF(INDIRECT("'"&ROW(A47)&"'!$B$1"<0;INDIRECT("'" &ROW(A47)&"'!$B$1;" ")

and copy down


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bart" wrote in message
...
Hi,
This formula has references to other sheets in the same file.
I have this formula in A1 : =IF('47'!$B$1<0;'47'!$B$1;" ")
I have this formula in A2 : =IF('48'!$B$1<0;'48'!$B$1;" ")
and so on and so on.
I want to copy this formula in about 50 rows. So i tried to click it
and
move it to others cells. But the formula stays the same. I thought the
numbers 47 and 48 should increase with 1, but they don't.
How could I solve this ?

Bart

Thank you Bob, it works now

"Bob Phillips" wrote:

I know what you want, but there was an error ijn my previous post

Try

=IF(INDIRECT("'"&ROW(A47)&"'!$B$1")<0;INDIRECT("' "&ROW(A47)&"'!$B$1");"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bart" wrote in message
...
Thank you Bob, but it's not the solution I think.
You refer to A47, which is a cell, but i want to refer to a sheet which is
called '47'. To make it easier to understand i changed the formula :

I have this formula in A1 : =IF('sheet47'!$B$1<0;'sheet47'!$B$1;" ")
I have this formula in A2 : =IF('sheet48'!$B$1<0;'sheet48'!$B$1;" ")

So i want to make 'sheet47' variable, so it counts to sheet49, sheet50,
sheet51, ...

Is this more clear ?



"Bob Phillips" wrote:

Change it to

=IF(INDIRECT("'"&ROW(A47)&"'!$B$1"<0;INDIRECT("'" &ROW(A47)&"'!$B$1;" ")

and copy down


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bart" wrote in message
...
Hi,
This formula has references to other sheets in the same file.
I have this formula in A1 : =IF('47'!$B$1<0;'47'!$B$1;" ")
I have this formula in A2 : =IF('48'!$B$1<0;'48'!$B$1;" ")
and so on and so on.
I want to copy this formula in about 50 rows. So i tried to click it
and
move it to others cells. But the formula stays the same. I thought the
numbers 47 and 48 should increase with 1, but they don't.
How could I solve this ?

Bart