I want Excel to look into the formula of a cell and then display a different cell based on that. confused yet?

Column A on sheet 1 has a size and column B on sheet 1 has a tolerance.

Could be same numbers in both column throughout. Now on sheet 2 I want to be able in column A to reference, lets say A4/1 with a simple =sheet1!A4 that I can manually put in and then I want column B on sheet 2 to display B4 from sheet 1.

Is there a formula I can put into sheet 2 cell B4 to make this happen? Does it make a difference if I reference A4/1 and I want B4/2 to display C4/1?

I'm looking to create a relationships between the 2 cells essentially. I hope it was clear enough to understand.


Not sure why would you ever need it, but this is my answer how I would access the contents of the formula and get another value based on that.

enter image description here

In Sheet2!A2 there is the formula =Sheet1!A4
In Sheet2!B2 there is the formula =OFFSET(INDIRECT(MID(FORMULATEXT(A2),2,999)),0,1)

Using that formula you access

FORMULATEXT returns the formula in text format (=Sheet1!A4).
MID part removes the = from the beginning (Sheet1!A4).
INDIRECT makes Sheet1!A4 a valid reference instead of just text.
OFFSET gets the value from 1 cell to the right (Sheet1!B4).

  • If the answer helped to solve the problem please check the ✓ symbol next to the answer. – ZygD Mar 2 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.