Cell Addresses derived from concatenated values in separate cells (column and row numbers derived from different cells)

[Joshua McNichols]

I need to return a value from a cell in Column H, but with a row number that varies with each entry.

I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:

=Display Value from in column H: (row number per cell B1)

And cell B1 would include the number 3789.

So the returned result is the value from cell H:3789

Any help appreciated

 

[CodeNinja]

Not sure if I inverted this or not.... but you can use an index and truncate the division or multiply by 5 for the row...

=INDEX(Sheet3!$1:$1048576,1 +(TRUNC(ROWS($A$1:A1)/5)),2)

If I have 1 in B1 and 2 in B2 on sheet3, this formula would give me a 1 for the first 5 times i copied it then a 2 for the next 5...

If you are going the other way around and want to skip five rows...

=INDEX(Sheet3!$1:$1048576,ROWS($A$1:A1)*5,2)

That formula would give me the value of B5 in the first cell i entered it in, then copied down would give me B10 etc...

You could also subtract 4 to get B1 then B6...

=INDEX(Sheet3!$1:$1048576,(ROWS($A$1:A1)*5)-4,2)

 

[Joshua McNichols]

Hmm. I'm not sure I understand. Is there code I can use to simply replace a cell address in an existing formula with a column name that stays the same and a row number that is found in a certain cell?

For example, I have a cluster of formulas that I've spent a lot of time making. As you can see, every cell address mentioned in this cluster is in row four, with references to various columns within that row 4. (In reality, my formula cluster has about three times as many formulas as this.)

=INDIRECT("'Content List'!j4")&" "&TEXT(INDIRECT("'Content List'!k4")," mm:ss")
=IFERROR(IF(INDIRECT("'Content List'!t4")=0," ",B28+INDIRECT("'Content List'!k4")),"")
="TAG: "&INDIRECT("'Content List'!q4")
=IF($F$5="N"," ","CREDIT: "&INDIRECT("'Content List'!r4"))

When I copy this formula cluster, the columns stay the same, as they should. But the next instance needs to contain all references to row 5. Because my cluster of formulas is so big, they take up 5 rows. The problem is when I paste a second instance of this cluster below, it will reference row 10.

I need a simple way to replace every "4" in the above addresses with "the row number found in cell B27".

Then, every time I copy the cell number down, it would draw the row number from B27 + 5 rows down. That's perfect, because it's easy for me to enter the number only ONCE per formula cluster in the cell B27 or whatever. I can even have the contents of that cell automated. For example,
B33=B27+1
B38=B27+2
B43=B27+3

The tedious work of creating the above list is vastly superior to what I'm doing now, which is replacing the "4" in my formula cluster with +1 numbers until I get to number 157 or so.

 

[CodeNinja]

Sorry I did not make myself clear. I was trying to give you a tool to fix your formulas as I did not know your formulas (they were not provided).

You will need to adjust any of the 4 formulas that reference a cell with the first version of the formula. Once you have done that, you will need to highlight all 4 of them and copy them down. I thought you had 5 formulas, but it seems you only have 4 so you will have to trunc(/4)instead of trunc(/5).

For the purposes of this example, I am going to assume the formulas you posted in your reply are the first instance of these formulas and you wish to copy them down.

=INDIRECT("'Content List'!j4")&" "&TEXT(INDIRECT("'Content List'!k4")," mm:ss")
=IFERROR(IF(INDIRECT("'Content List'!t4")=0," ",B28+INDIRECT("'Content List'!k4")),"")
="TAG: "&INDIRECT("'Content List'!q4")
=IF($F$5="N"," ","CREDIT: "&INDIRECT("'Content List'!r4"))

So for the first one, you have 2 cell references... "'Content List'!j4" and "'Content List'!k4". When you copy these down, they will increment too much, so you wish to reduce the increment to 1/4...

Content List'!j4 becomes INDEX('Content List'!$1:$1048576,4 +(TRUNC(ROWS($A$1:A1)/4)),10)
Content List'!k4 becomes INDEX('Content List'!$1:$1048576,4 +(TRUNC(ROWS($A$1:A1)/4)),11)

so the formula now looks like:
=INDIRECT(INDEX('Content List'!$1:$1048576,4 +(TRUNC(ROWS($A$1:A1)/4)),10))&" "&TEXT(INDIRECT(INDEX('Content List'!$1:$1048576,4 +(TRUNC(ROWS($A$1:A1)/4)),11))," mm:ss")

I hope this explains it better...

 

[Joshua McNichols]

Thank you!