Copying a formula containing the INDIRECT function
 

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?

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?

Copying a formula containing the INDIRECT function
 

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?

Copying a formula containing the INDIRECT function
 

=INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1)))

If you're going to use the ADDRESS function:

=INDIRECT(ADDRESS(16,COLUMN(B1),,,$A4))

I would not use COLUMN(B1).

=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?

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?

Copying a formula containing the INDIRECT function
 

On Aug 30, 3:02*am, Shane Devenshire
wrote:
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?- Hide quoted text -

- Show quoted text -

Brilliant, Shane - thank you!

Copying a formula containing the INDIRECT function
 

On Aug 30, 11:59*am, "T. Valko" wrote:
=INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1)))

If you're going to use the ADDRESS function:

=INDIRECT(ADDRESS(16,COLUMN(B1),,,$A4))

I would not use COLUMN(B1).

=INDIRECT(ADDRESS(16,COLUMNS($A1:B1),,,$A4))

--
Biff
Microsoft Excel MVP

"Shane Devenshire" wrote in
...



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?- Hide quoted text -

- Show quoted text -

Thank you Biff - terrific! How on earth do you know this stuff so
easily?!?

Copying a formula containing the INDIRECT function
 

How on earth do you know this stuff so easily?!?

I do this stuff every day, all day!

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Pelham" wrote in message
...
On Aug 30, 11:59 am, "T. Valko" wrote:
=INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1)))

If you're going to use the ADDRESS function:

=INDIRECT(ADDRESS(16,COLUMN(B1),,,$A4))

I would not use COLUMN(B1).

=INDIRECT(ADDRESS(16,COLUMNS($A1:B1),,,$A4))

--
Biff
Microsoft Excel MVP

"Shane Devenshire" wrote in
...



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?- Hide quoted text -

- Show quoted text -

Thank you Biff - terrific! How on earth do you know this stuff so
easily?!?