Is Dynamic Addressing Possible?

[ghazghkull]

Hi,

I'm wondering if there's a way to dynamically alter a formula that's displaying the address of a given cell.

Right now I'm running the following formula: =ADDRESS(22,3,4)

The issue is that it's a static formula. If I were to right now do an insert function above this it would continue to display that address, C22, which isn't what I'm looking for.

What I want to do is that I want those numbers to increment every time I insert a whole new line above it, so it's displaying the correct address.

IE: Right now it would display C22. But if I inserted a new line above it, I want it to increment and display C23, and so on and so forth.

Is there a specific function, or a combination of functions I could use that could do this?

Or is this beyond what Excel could do?

Or better yet, has this problem been addressed before and there's a reference I could read?

Thanks

[NickyC]

Hi
Try this
Assuming your formula is in cell C30
Give cell C30 a name e.g. ThisCell
Link the row in your formula to the row of ThisCell:

=ADDRESS(ROW(ThisCell)-8,3,4)


This should add or subtract rows automatically to your formula when the row number of thiscell changes

[dmg2016]

Hi ghazghkull,

In the row and column inputs of address formula use row() and column() with a cell reference to update the address formula automatically as you insert new rows or columns.

Please Login or Register  to view this content.

Another possible solution:

Please Login or Register  to view this content.

DMG

[ghazghkull]

Thanks for all help. I ended up using the Address formula that NickyC suggested, and combined it into an Indirect formula that I was working on.

For anyone who's curious though, here's the formula I ended up using.

=INDIRECT("'"&SHEETOFFSET(-1,$H$1)&"'!"&ADDRESS(ROW('Nov 16'!B22)-0,3,4))

Some of the numbers of course are relevant to my worksheet, and the sheetoffset function was a macro that I found online to reference information on previous sheets, which has now been replaced with that indirect function that I just created. Although the sheetoffset still has its uses as I am still using it to reference the name of previous worksheets.

Again, thanks for all the help!