Indirect function and quotation marks.

[Eamonn100]

I'm using the indirect function with this formula, =INDIRECT($A$3&"$NKW4")

$A$3 is a reference to a sheet name and "$NKW4" is a reference to a cell on that sheet.

Is there a way that I can put "$NKW4" into the formula without the quotation marks "". I'm looking for this because when I drag the formula to other cells it stays the same because of the quotation marks.

 

[FormR]

I'm looking for this because when I drag the formula to other cells it stays the same because of the quotation marks.

Hi, what is the first cell in which you place the formula, where are you dragging it to and how do you want the range to change as you drag it?

 

[Eamonn100]

Hi,

if it helps remove the $ from NKW4.

When I drag it down it will stay at "$NKW4" rather than changing to NKW5 but I will be dragging it left, right, up and down.

This formula will be in many cells so need it to works anywhere.


PS.

This is my actual formula and I just included the part that needs to be changed. It's the quotation marks that are holding me back.

=IF((YEAR(INDIRECT($A$3&"$NKW4"))-YEAR(INDIRECT($A$3&"$NJG4")))*12+MONTH(INDIRECT($A$3&"$NKW4"))-MONTH(INDIRECT($A$3&"$NJG4"))>=12,INDIRECT($A$3&"DH4"),INDIRECT($A$3&"DG4"))

Thanks

 

[AlanY]

to copy down, modify

INDIRECT($A$3&"$NKW4") to

INDIRECT($A$3&"$NKW"&Row(4:4))

similar for the others

 

[FormR]

HI, I understand the issue - the questions remain though.

In what cell will you first place the formula?
Where are you dragging it - i.e. are you dragging across the columns or down the rows or both?
How do you want the range to change - i.e. do you want the rows to increment as it's dragged or the columns?

 

[FormR]

INDIRECT($A$3&"$NKW"&Row(4:4))

Hi, this construct is not robust and is best avoided if possible.

 

[Eamonn100]

HI, I understand the issue - the questions remain though.

In what cell will you first place the formula?
Where are you dragging it - i.e. are you dragging across the columns or down the rows or both?
How do you want the range to change - i.e. do you want the rows to increment as it's dragged or the columns?

The first cell will be X2.

I'm dragging it both.

It's the columns I want to increment.

 

[AlanY]

The first cell will be X2.

I'm dragging it both.

It's the columns I want to increment.

in that case

modify

INDIRECT($A$3&"$NKW4") to

INDIRECT($A$3&ADDRESS(ROW($NKW4),COLUMN($NKW4)))

similar for the others

 

[FormR]

Hi - still not robust. Inserting rows or columns in the sheet where the formula is will alter where the results are obtained from the sheet referred to by the INDIRECT() function.

 

[Eamonn100]

Oh I see that, thanks for pointing that out.

I was in the middle of writing this, "Thanks for that. It's working just fine. I'll play about with it some more just to see if anything goes wrong but I can drag it anyway I want so it's probably good. Thanks for the solid." LOL!

Is there anyway to fix this?