Evaluate cell or have cell refer to itself after being transposed via. lookup functions

[AshJames]

Hey all,

I have a super puzzling question to put forward; I want to re-evaluate a cell after it has been transposed from one sheet to another, using a lookup function, the function method doesn't matter, be it; INDEX/MATCH, XLOOKUP or VLOOKUP, the catch is that the cell which the data is transposed into, needs to be able to use data from it's own sheet not the originating sheet.

For an example of how to put it into context of what I want to achieve;

• I have data in Sheet2, a prefilled table. One of the columns within that table contains a description of the way something works. Within that description are simple multiplicative formulas, data used in those formulas are derived from nearby columns in the same row, alongside data which is derived from Sheet1 which is a dynamic sheet of information.
• The data in Sheet1 which is used in the description of Sheet2 is dynamic and changes based on a selection that is made by selections and dropdowns. I need the description cell from Sheet2 to be present in Sheet1, which can be achieved using a the variety of lookup function, however I then need to that new cell in Sheet1 to be able to either re-evaluate it's contents when the contents of the dynamic data in the Sheet1 changes, or, I need to be able to put in a cell self-referencing workaround.

At the moment, I have two ways for the cell in Sheet1 to refer to itself once the lookup is transposed; however, when they are placed in the description cell in Sheet2, they don't dynamically update to the new cell in Sheet1, returning #VALUE as it's only copying the already formulated cell's contents and not updating the formula when called in Sheet1. The idea is, when the cell is called with a lookup, it will transpose just the cell's contents and re-evaluate on the other sheet.

=INDIRECT(ADDRESS(ROW(),COLUMN()-3)

=OFFSET(INDIRECT(CELL("address")),0,-3)

I don't have any other methods for the cell to refer to itself, and as neither of these work as 'is', I can put this as a text string in the hopes I can force a function to re-evaluate the text string into a formula. However, there is no function to re-evaluate a cell; using methods such as SUBSTITUTE, LAMBDA, INDIRECT, and VBA modules designed to re-evaluate don't seem to re-evaluate the cell at all, or keeps it as a text string.

To get the full scope of what I'm trying to achieve these are my 2 sheets; TLDR;

• cell D3 of Sheet1 displays and functions correctly, dynamically changing when A3 and B3 are changed, however what I need to do is not have it refer directly to the cells but have it reference to the 'active cell' and offset it, which is what cell D2 is doing, however it does not carry the ability to re-evaluate when it's transposed over from Sheet2!B2

Book1
	A	B	C	D	E	F
1	Increase 1	Increase 2	Name	Description		Function
2	40	5	Example 1	#VALUE!		using ADDRESS to pull cells A2 and B2 in new cell -> does not re-evaluate when transposed
3	40	5	Example 2	Increases primary parameters by 200% and secondary parameters by 30%		directly instancing A2 and B2 on Sheet2 -> confirmation that the lookup works correctly
4	40	5	Example 3	="Increases primary parameters by "&59.6+(Table1[@[Increase 1]]-1)*Sheet2!C4&"% and secondary parameters by "&6.06+(Table1[@[Increase 1]]-1)*Sheet2!D4+Table1[@[Increase 2]]*Sheet2!F4&"%"		CONCANATE = infront of the sentence and using ADDRESS, does not re-evaluate
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=INDEX(Table2[@Description],MATCH([@Name],Table2[@Name],0))
D4	D4	=CONCATENATE("=",INDEX(Table2[@Description],MATCH([@Name],Table2[@Name],0)))

Book1
	A	B	C	D	E	F	G	H
1	Name	Description	Param. 1	Param. 2	Param. 3	Param 4.	Param. 5	Param. 6
2	Example 1	#VALUE!	3.6	0.36		1.98
3	Example 2	Increases primary parameters by 200% and secondary parameters by 30%	3.6	0.36		1.98
4	Example 3	"Increases primary parameters by "&59.6+(Table1[@[Increase 1]]-1)*Sheet2!C4&"% and secondary parameters by "&6.06+(Table1[@[Increase 1]]-1)*Sheet2!D4+Table1[@[Increase 2]]*Sheet2!F4&"%"	3.6	0.36		1.98
Sheet2

Cell Formulas
Range	Formula
B2	B2	="Increases primary parameters by "&59.6+(INDIRECT(ADDRESS(ROW(),COLUMN()-3))-1)*Sheet2!C2&"% and secondary parameters by "&6.06+(INDIRECT(ADDRESS(ROW(),COLUMN()-3))-1)*Sheet2!D2+INDIRECT(ADDRESS(ROW(),COLUMN()-2))*Sheet2!F2&"%"
B3	B3	="Increases primary parameters by "&59.6+(Table1[@[Increase 1]]-1)*Sheet2!C3&"% and secondary parameters by "&6.06+(Table1[@[Increase 1]]-1)*Sheet2!D3+Table1[@[Increase 2]]*Sheet2!F3&"%"

 

[AshJames]

Since I am unable to edit my post; I think I have simplified explaining what it is I want achieved;

• I want to have a description in Sheet1 which can dynamically change when the data in Sheet 1 column A is changed
• However, the description must be copied from Sheet2 using a lookup function, which uses data from Sheet2 column D

I am able to correctly reference the cell independently, Example 6, however this cannot be used in a formula as shown in Example 5. I have tried multiple methods of formulas in the Sheet2 description to pull the data from Sheet1, however they all seem to reference the Sheet1 data as TEXT and doesn't evaluate. I have added an evaluation column in Sheet1, column D, which is supposed to force a re-evaluation of the description. It doesn't matter how I do it, I cannot find a way to have the Sheet1 description refer to it's own cell with an offset and pull data from it's adjacent data column.

Yes, Example 2 does work as intended, if I directly reference the data in Sheet1, however there will be multiple tables in Sheet1 and I don't have the ability to switch between tables, and the entries in the name column in Sheet1 will have duplicated, so they aren't unique entries.

TEST.xlsm
	A	B	C	D
1	Data	Name	Description	Evaluate
2	40	Example 1	59.6+INDIRECT("RC[-4]",FALSE)*Sheet2!D2	#REF!
3	40	Example 2	59.6+Table1[@[Data]]*Sheet2!D3	203.6
4	40	Example 3	59.6+CELL("CONTENTS",ADDRESS(ROW(),COLUMN()-4)*Sheet2!D4	#VALUE!
5	40	Example 4	OFFSET(INDIRECT(CELL("ADDRESS")),0,-4)	#REF!
6	40	Example 5	59.6+ADDRESS(ROW(),COLUMN()-4)*Sheet2!D6	#VALUE!
7	40	Example 6	$A$7	40
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=INDEX(Table2[@Description],MATCH([@Name],Table2[@Name],0))
D2:D7	D2	=EVAL

TEST.xlsm
	A	B	C	D
1		Name	Description	Data
2		Example 1	59.6+INDIRECT("RC[-4]",FALSE)*Sheet2!D2	3.6
3		Example 2	59.6+Table1[@[Data]]*Sheet2!D3	3.6
4		Example 3	59.6+CELL("CONTENTS",ADDRESS(ROW(),COLUMN()-4)*Sheet2!D4	3.6
5		Example 4	OFFSET(INDIRECT(CELL("ADDRESS")),0,-4)	3.6
6		Example 5	59.6+ADDRESS(ROW(),COLUMN()-4)*Sheet2!D6	3.6
7		Example 6	$A$7	3..6
Sheet2

Cell Formulas
Range	Formula
C7	C7	=(ADDRESS(ROW(),COLUMN()-2))

 

[Sam_D_Ben]

May be something like this,

Book2
	A	B	C	D
1	Column1	Name	Description	Data
2		Example 1	59.6+INDIRECT("RC[-4]",FALSE)*Sheet2!D2	3.6
3		Example 2	59.6+Table1[@[Data]]*Sheet2!D3	3.6
4		Example 3	59.6+CELL("CONTENTS",ADDRESS(ROW(),COLUMN()-4)*Sheet2!D4	3.6
5		Example 4	OFFSET(INDIRECT(CELL("ADDRESS")),0,-4)	3.6
6		Example 5	203.6	3.6
7		Example 6	$A$7	3..6
Sheet2

Cell Formulas
Range	Formula
C6	C6	=59.6 + INDIRECT("Table1[@[Data]]") * Sheet2!D6