Forcing a Relative Reference

[thaichef]

Is it possible to force a relative reference for the following:

=INDIRECT("a"&1)

I understand that this is an odd thing to try to do. What I'm trying to accomplish is too complicated for me to attempt to explain.

Here is one of the formulas that I'm trying to force to be a relative reference:

INDIRECT(("Backpacking!"&CHAR(RIGHT(A1,LEN(A1)-FIND(" ",A1))+67)&3)

Thanks for any help that you can provide.

--ThaiChef

 

[taigovinda]

Welcome to the board!

What do you mean, you want to force it to be a relative reference? This:

=Indirect("a"&1)

is the same as this

=Indirect("A1")

...which will always return the same as this:

=$A$1

So... what are you trying to do??

 

[thaichef]

Thanks for the welcome!

I understand that INDIRECT("A1") will always result in $A$1. I'm trying to find a way to build a reference from a string that results in A1.

Can anyone think of a way to do this?

Thanks,

--ThaiChef

 

[thaichef]

Ok, I think I have a better way to explain it...

For columns A | B

My current cell contents are:

2 | INDIRECT("Sheet"&$A1&"!B1")
3 | INDIRECT("Sheet"&$A2&"!B2")
4 | INDIRECT("Sheet"&$A3&"!B3")

So you can see that by using the partially relative address of $A1, I can copy and paste new rows going down. However, I'd like to figure out a way to not have to change the values in the string. If I were to copy and paste the next row, I would get:

5 | INDIRECT("Sheet"&$A4&"!B3") and I'd have to change the "!B3" to "!B4" manually.

Any thoughts on solving this?

Thanks,

--ThaiChef

 

[taigovinda]

Hello ThaiChef, you can use the row() function for this. So this:

4 | INDIRECT("Sheet"&$A3&"!B3")

...becomes this:

4 | INDIRECT("Sheet"&$A3&"!B" & Row(A3))

...A3 could be B3 or C3, it is just returning the row #. Hope that resolves it for you - sorry it took me awhile to get back on here!

 

[mikerickson]

Perhaps
=INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),4))

 

[thaichef]

Hello ThaiChef, you can use the row() function for this. So this:

4 | INDIRECT("Sheet"&$A3&"!B3")

...becomes this:

4 | INDIRECT("Sheet"&$A3&"!B" & Row(A3))

...A3 could be B3 or C3, it is just returning the row #. Hope that resolves it for you - sorry it took me awhile to get back on here!

That worked perfectly...thanks very much for your help!

--ThaiChef