|
formula problem
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 |
formula problem
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 |
formula problem
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 |
formula problem
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 |
formula problem
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 |
| All times are GMT +1. The time now is 07:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com