formula problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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 ?
 
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)
 
Thank you Bob, it works now

Bob Phillips said:
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)
Click to expand...
 
Back
Top