Copying a formula containing the INDIRECT function
Top posting WITH a sig is SO lame.
I have had good success with simply using the search and replace tools
in notepad when drag style formula copying fails.
A columnar paste from notepad goes in just fine.
It works well when excel wants to auto-morph more figures than the user
wants it to on a drag copy operation.
On Sat, 29 Aug 2009 22:59:55 -0400, "T. Valko"
wrote:
=INDIRECT(ADDRESS(16,COLUMNS($A1:B1),,,$A4))
--
Biff
Microsoft Excel MVP
"Shane Devenshire" wrote in
message ...
Hi,
Try this:
=INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1)))
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Pelham" wrote:
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?
|