Getting a cell's formula...

[ErikJan]

I'm getting rusty probably...

In a cell I have a formula, e.g. =Data!N10 which results in a number (no problem).

In the cells next to this one, I want the content of the cells adjacent to that cell. So e.g. I would like in the cell to the right: =Data!N8 for example.

What my plan was is to get the formula as string ("Data!N10"), and then enter in the cell to the right something like:

=indirect("=" & "Data!N10") but then with an offset to get to N8.

I know, I can simply put the formula =Data!N8 there. Sometimes things could change to e.g. "Data!S10" and then I'd like all the adjacent cells to 'go with that' and therefore now display "Data!S8"

Sorry, I hope I'm not too cryptic... ;-)

[HansV]

Why would the cell to the right of the cell with formula =Data!N10 contain =Data!N8 ? What is the logic behind that?

[ErikJan]

Oh, it was just an example. The origin lists the variable name, unit, description and value. Maybe I'd like to display always the unit next to it for the value I display :-)

[HansV]

See if the attached example does what you want. It uses a combination of INDIRECT and OFFSET (plus ROW).

IndirectSample.xlsx

[ErikJan]

Thanks Hans but this is not quite what I'm looking for. I apologize for not being able to explain better what I need...

Let me try again...

In a big workbook with many sheets, I select from one sheet a variable to display. The Cell-entry is e.g. Data!N10, the cell content however is e.g. 3.4 (so I don't have the cell address as text; which is what you used to start - I wish I had...).
Now after making that selection/update; I would like a cell adjacent to the cell now displaying 3.4, to display 'kg'. This unit-display is taken from a place two cells to the left of the cell I chose (automatically). So if I set the cell that displays 3.4 to Data!N10, I would like to have a formula in the cell next to it that evaluates to "Data!N8" (and therefore displays 'kg').
I think I can work that out if I'd only remember how to get the text of a cell formula; so how do I get the string "Data!N10"...

[HansV]

I think you're approaching this the wrong way, but anyhow...
You can use a custom VBA function:

Code: Select all

Function GetFormula(rng) As String
    GetFormula = rng.Formula
End Function

[ErikJan]

OK, like I said, I'm rusted and might be missing something quite simple. Please explain how I could / should do this better ("the right way") :-)

[HansV]

I don't understand what you want to accomplish if OFFSET and MATCH cannot solve it, as in the sample that I posted...

[ErikJan]

Ah, OK. I have a formula in a cell displaying a value. If I use a cell with the text of that formula ("Data!N10") as in your example, that won't update if I edit the source sheet (e.g. adding a column will make the formula "Data!O10"; your example will then fail). The VBA code will work; I was hoping there might be a simple formula

[HansV]

If your source data has column headings, you could use MATCH search for the column(s) to use. That way, it'll still work if you move the columns in the source data around.

[Goshute]

ErikJan, I'm probably missing your point, but if your objective is to add text to an existing value for display purposes, why not use the =TEXT function, something like:

=TEXT(Data!N10,"###0.00")&"Kg"

If cell Data!N8 contains the text "Kg" this can be changed to

=TEXT(Data!N10,"###0.00")&Data!N8

(Change the =TEXT display format argument to suit your local numeric conventions.)