Copying a formula containing the INDIRECT function
If N/A is found in cell B16 of that worksheet then
N/A appears in the cell where the formula is written,
if not the contents of B16 of that worksheet appear instead.
=IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16"))
You don't need the IF function.
Assume you enter the first formula in cell A1:
=INDEX(INDIRECT("'"&$A4&"'!B16:IV16"),COLUMNS($A1: A1))
Copy across as needed.
--
Biff
Microsoft Excel MVP
"Pelham" wrote in message
...
Dear All
I have the following formula in cell B4:
=IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16"))
It refers to cell B16 in a worksheet where the name of that worksheet
is in cell A4. If N/A is found in cell B16 of that worksheet then N/A
appears in the cell where the formula is written, if not the contents
of B16 of that worksheet appear instead.
I want to drag this formula across to the right of my worksheet (to
cells C4, D4 etc) and I need the B16 to change to C16, D16 etc
accordingly. How do I modify formula so that B16 changes accordingly,
please?
|