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]

Hi, I missed your reply.

I'm dragging it both.

It's the columns I want to increment.

So as you drag it down you don't want to continue returning the value from row 4?

 

[Eamonn100]

Sorry, no it's correct the way it is, thanks. It's rows I want to increment.

I'm a little dislexic and when using F4 always lock columns when I want cells, hahaha.

 

[FormR]

It's still not all that clear - but try this in X2 which will adjust as you drag either across or down.

=INDIRECT($A$3&ADDRESS(ROWS(X$2:X2)+3,COLUMNS($X2:X2)+9772) )

 

[Eamonn100]

It's still not all that clear - but try this in X2 which will adjust as you drag either across or down.

=INDIRECT($A$3&ADDRESS(ROWS(X$2:X2)+3,COLUMNS($X2:X2)+9772) )

No the first formula can be dragged all ways. =INDIRECT($A$3&ADDRESS(ROW($NKW4),COLUMN($NKW4))). It's fine, other than when you incert or delete columns.

 

[FormR]

No the first formula can be dragged all ways. =INDIRECT($A$3&ADDRESS(ROW($NKW4),COLUMN($NKW4))). It's fine, other than when you incert or delete columns.

Hi, can you try and comment on the formula in post 13.

 

[Eamonn100]

Yes that formula also works. I can delete or add columns as well, thanks for that.

 

[Eamonn100]

Wait, something is going on. When I drag to the left it reverse the order of dragging right, as though that first cell is the anchor. The first formula could be dragged left.

 

[FormR]

The first formula could be dragged left.

Replace the X2 cell references for the top left hand cell where the formula will be entered (i.e. the first cell you are going to enter the formula in) and then drag right and down as required.

 

[Eamonn100]

No worries, I understand now what the formula is doing. That last post was a panic post.

Thanks again.