I want to set a cell reference up such that I can look at the current cell line number and minus a certain constant, k, from that cell line number to reference another cell.
Why do I want to do this? So that I can copy and paste a chunk of table and have cells, call them X, reference other cells, call them Y, a certain number of lines above cells X. I would usually set up a fixed line number (i.e. G$145 is referenced by G175), but I don't always want to reference G145; I only want G175 to reference G145. If I copy and paste this chunk of table 40 lines down (G185), I want G215 to reference G185, not G145--hence, I can't use G$145 in my formula.
In theory, it would look like this:
IF( G(linenumber(thiscell) - 30) = 1, "cool", "not cool")
This would look at this current cell, check its line number, and reference a cell 30 lines above it (I'll be using an IF statement, so this should be illustrative of my issue).
I hope my explanation was helpful, I know it might look a bit convoluted. Any help would be appreciated!
Bookmarks