Offset based on cell reference

[scottjn]

Tried searching for this, but I'm not even sure what to search for.

Here's what I would like to do:
1. Let's say A1 has the simple formula =B4
2. I want A2 to be whatever A1 refers to offset by x-columns and y-rows (say x is 1 and y is 1 for this example), i.e. A2 would be whatever is in cell C5

What formula would I use to do this? I tried playing around with offset, address and indirect without much luck.

Thanks!!!

[6StringJazzer]

If A1 has the formula

=B4

and NOT the formula

="B4"

then there is no built-in Excel formula that will tell you what cell A1 is referring to.

This could be done using a user-defined function written in VBA. It would be something like this:

Please Login or Register  to view this content.

If a cell refers to one other cell, as in your example, you can strip off the leading "=" and then use INDIRECT and OFFSET on the result.

[ChemistB]

Using Defined Names
With your cell selected as A2,
Defined Name = GetFormula
Refers to: =GET.CELL(6,Sheet1!A1)

Then in A2 (with A1 containing =B4)
=OFFSET(INDIRECT(MID(GetFormula,SEARCH("=",GetFormula)+1,5)),1,1)
Those last two 1's can be replaced by cell references to offset rows and columns.
Does that work for you?

[6StringJazzer]

Hey ChemistB, that's pretty slick. I have never seen GET.CELL before and it doesn't seem to work as a plain vanilla formula. How does this work in the context of a named formula?

[6StringJazzer]

Well, I seem to have sorted it out. This is an old Excel 4.0 macro call, which apparently is still supported if you use it in a named formula but not on a worksheet. Here the Help entry from Microsoft. The complete 4.0 macro Help file can be downloaded this page: http://support.microsoft.com/default...S;Q128185&FR=1

Code tags used to preserve formatting.

Please Login or Register  to view this content.

[DonkeyOte]

XL2007+: Use of XLM calls necessitates .xlsm save on which basis: use a UDF

[6StringJazzer]

Here is a mod to the UDF that more closely matches conventions for Excel functions.

Please Login or Register  to view this content.

If a cell refers to one other cell, as in your example, you can strip off the leading "=" and then use INDIRECT and OFFSET on the result.[/quote]

[ChemistB]

Yes, as you found out, it's an old macro 4 command that can still be used in defined names. I use them sometimes when verifying a spreadsheet to conditionally format formulas and/or locked/unlocked cells.

[scottjn]

Thanks, that does work. Although it is a bit cumbersome since I would like to do it for many cells and many worksheets. Thanks again.

Using Defined Names
With your cell selected as A2,
Defined Name = GetFormula
Refers to: =GET.CELL(6,Sheet1!A1)

Then in A2 (with A1 containing =B4)
=OFFSET(INDIRECT(MID(GetFormula,SEARCH("=",GetFormula)+1,5)),1,1)
Those last two 1's can be replaced by cell references to offset rows and columns.
Does that work for you?

[ChemistB]

The formula is relative so you can paste that into any cell within that spreadsheet. 6String's UDF is more versitile.

[gm200833]

Please Login or Register  to view this content.