Consider this scenario.
A vlookup on Sheet1 returns a value from Sheet2. The cell it successfully finds and returns on Sheet2 is this formula =E1&” is good”
I want the vlookup to display that on Sheet1 and the formula to look at E1 on Sheet1. Currently it’s still referring to E1 on Sheet2.
Obviously if the formula on Sheet2 was =Sheet1!E1&" is good" then the result of vlookup would work but I want to use Sheet2 to be accessed from multiple sheets. Therefore Sheet2 must stay generic and refer to E1 and the other sheets will each have the correct value in their own E1.
A simple example is attached.
How do I achieve this? I hope my question is clear.