Auto replicate formula across tabs

[JDC8457]

I have workbook with multiple tabs. Each tab is formatted the same.

I wrote this formula to pull or omit data from cell C4 on each tab. In this formula, it's pulling data from tab S1.

=IF('S1'!$F$4="",'S1'!$C$4,"")

In Column A1, I have the tab names, S1 to S30. In Column B1, I have the formula.

I want to replicate this formula by copying down in column B, but I don't know how to change the tab reference in the formula.

Thanks in advance for the help.

 

[bosco_yip]

Perhaps,

=IFERROR(IF(INDIRECT("'S"&ROWS($1:1)&"'!$F$4")="",INDIRECT("'S"&ROWS($1:1)&"'!$C$4"),""),"")

Copied down

Regards
Bosco

 

[JDC8457]

Many thanks. Looks like it works!!!

 

[JDC8457]

Let me request a change in the formula.

If the value in F4 and the value in f16 are "", then to to the second half of the above formula.

Hope you can help with this too.

Thanks

 

[bosco_yip]

Let me request a change in the formula.

If the value in F4 and the value in f16 are "", then to to the second half of the above formula.

Hope you can help with this too.

Thanks

Then,

change to :

=IFERROR(IF((INDIRECT("'S"&ROWS($1:1)&"'!$F$4")="")*(INDIRECT("'S"&ROWS($1:1)&"'!$F$16")=""),INDIRECT("'S"&ROWS($1:1)&"'!$C$4"),""),"")

Regards
Bosco