Dynamic cell and sorting

[Polanskiman]

So here is the deal. I need that cell in E4 to be dynamic because the "Description" column on the Excel table is supposed to be sortable. By dynamic I mean that no matter the order of the Excel table, cell E4 should always report the correct tag associated to the description. How can I achieve that?

Any help would be appreciated.

Thank you.

Book1.xlsx
	B	C	D	E	F	G	H	I
1
2		In Sheet 1		In Sheet 2		In Sheet 3
3
4		Product 1		Product 1 new		Description	Tag
5		Product 2				Product 1	new
6		Product 3				Product 2	old **
7		Product 4				Product 3	used
8		Product 5				Product 4
9		Product 6				Product 5
10						Product 6	new
11						Product 7
12						Product 8
13
Sheet2

Cell Formulas
Range	Formula
E4	E4	=Sheet2!C4&" "&Sheet2!H5

 

[Peter_SSs]

Assuming your values are in the cells shown but in the 3 different sheets, try this in cell E4 of Sheet2.
Edit the table name in the formula to match your table name in Sheet3 if required.

=Sheet1!C4&" "&VLOOKUP(Sheet1!C4,Table1,2,0)

 

[EXCEL MAX]

Add sign "$" to fix value location "=Sheet2!$C$4&" "&Sheet2!$H$5"

 

[Polanskiman]

Assuming your values are in the cells shown but in the 3 different sheets, try this in cell E4 of Sheet2.
Edit the table name in the formula to match your table name in Sheet3 if required.

=Sheet1!C4&" "&VLOOKUP(Sheet1!C4,Table1,2,0)

Thank you. I did as suggested but it's throwing back an #N/A error. I also tried on my actual file where all data are in 3 different sheets and same error occurs.

I have slightly updated the data for more context but that shouldn't change my initial request. Here is what I get:

Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1
2		In Sheet 1		In sheet 2				In Sheet 3
3
4		Product 1		Description	Value			Description	Tag	Value
5		Product 2		#N/A	#N/A			Product 1 new	new	2
6		Product 3		Product 2 old **	5			Product 2 old **	old **	5
7		Product 4		Product 3 used	8			Product 3 used	used	8
8		Product 5		Product 4	1			Product 4		1
9		Product 6		Product 5	6			Product 5		6
10		Product 7		Product 6 new	0			Product 6 new	new	0
11		Product 8		Product 7	1			Product 7		1
12				Product 8	6			Product 8		6
13								Custom Product A		5
14								Custom Product D		1
15								Custom Product H		5
16
Sheet2

Cell Formulas
Range	Formula
E5	E5	=C4&" "&VLOOKUP(C4,Table1,2,0)
F5:F12	F5	=INDEX(Table1[Value],MATCH(E5,Table1[Description],0))
E6:E12	E6	=C5&" "&J6
I5:I12	I5	=C4&" "&[@Tag]

 

[Polanskiman]

Add sign "$" to fix value location "=Sheet2!$C$4&" "&Sheet2!$H$5"

That will not fix the issue as table in Sheet 3 is sortable. When sorted it doesn't matter if there is an $ or not. It will keep pick the same cell (H5)

 

[Peter_SSs]

It does change your initial request in that the table now no longer has a column for 'Description'.

Instead of combining all on to one sheet when you really have 3 sheets, why not put the sample data on the 3 sheets where they belong and post the small sections of each sheet with XL2BB and also make it clear which cells you require help with but fill those cells in manually with the expected results.

For example here are my 3 sheets with your original request - after I have sorted the table in a different way.

Polanskiman.xlsm
	C
4	Product 1
5	Product 2
6	Product 3
7	Product 4
8	Product 5
9	Product 6
Sheet1

Polanskiman.xlsm
	G	H
4	Description	Tag
5	Product 3	used
6	Product 2	old **
7	Product 1	new
8	Product 6	new
9	Product 4
10	Product 5
11	Product 7
12	Product 8
Sheet3

Polanskiman.xlsm
	E
4	Product 1 new
5	Product 2 old **
6	Product 3 used
7	Product 4
8	Product 5
9	Product 6 new
Sheet2

Cell Formulas
Range	Formula
E4:E9	E4	=Sheet1!C4&" "&VLOOKUP(Sheet1!C4,Table1,2,0)

 

[Polanskiman]

It does change your initial request in that the table now no longer has a column for 'Description'.

Instead of combining all on to one sheet when you really have 3 sheets, why not put the sample data on the 3 sheets where they belong and post the small sections of each sheet with XL2BB and also make it clear which cells you require help with but fill those cells in manually with the expected results.

For example here are my 3 sheets with your original request - after I have sorted the table in a different way.

Apologies. Thought I was simplifying things for others to make it easier. Obviously made it more complicated. Again sorry.

 

[Peter_SSs]

Apologies. Thought I was simplifying things for others more easily.

What you did was fine but I think what I gave you satisfied your original request - as evidenced by my last post? Given that you said it didn't work, I figure it wasn't as simple as it looked so asked for more 'actual' circumstances. BTW, I assume that your products are not 'Product 1', 'Product 2' etc so if you give more samples can you make up some more realistic names as it may influence the best course of action.

 

[sandy666]

maybe

In Sheet 1		In sheet 2			In Sheet 3
Product		Description	Value		Description	Tag	Value
Product 1		Product 1 new	2		Product 8		6
Product 2		Product 2 old **	5		Product 7		1
Product 3		Product 3 used	8		Product 6	new	0
Product 4		Product 4	1		Product 5		6
Product 5		Product 5	6		Product 4		1
Product 6		Product 6 new	0		Product 3	used	8
Product 7		Product 7	1		Product 2	old **	5
Product 8		Product 8	6		Product 1	new	2
					Custom Product H		5
					Custom Product D		1
					Custom Product A		5

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Join = Table.NestedJoin(tbl1,{"Product"},tbl2,{"Description"},"Table",JoinKind.Inner),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Tag", "Value"}, {"Tag", "Value"}),
    Merge = Table.CombineColumns(Expand,{"Product", "Tag"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Description"),
    Sort = Table.Sort(Merge,{{"Description", Order.Ascending}})
in
    Sort

 

[Polanskiman]

What you did was fine but I think what I gave you satisfied your original request - as evidenced by my last post? Given that you said it didn't work, I figure it wasn't as simple as it looked so asked for more 'actual' circumstances. BTW, I assume that your products are not 'Product 1', 'Product 2' etc so if you give more samples can you make up some more realistic names as it may influence the best course of action.

So here it is. I have updated names to reflect more what I am working on. I wish I could provide the actual file but I am not allowed to do so. I need help on the highlighted yellow cells. I need to make sure that even if table in Sheet 3 is sorted that is doesn't screw up the table in Sheet 2.

Book1.xlsx
	C
4	Commercial fertilizer
5	Fertilizer 1
6	Fertilizer 2
7	Fertilizer 3
8	Fertilizer 4
9	Fertilizer 5
10	Fertilizer 6
11	Fertilizer 7
12	Fertilizer 8
Sheet1

Book1.xlsx
	E	F
4	Description	mg/week
5	Fertilizer 1 (4ml/5times week)	2
6	Fertilizer 2 (2ml/3times week)	5
7	Fertilizer 3	8
8	Fertilizer 4	1
9	Fertilizer 5	6
10	Fertilizer 6 (once day)	0
11	Fertilizer 7	1
12	Fertilizer 8	6
Sheet2

Cell Formulas
Range	Formula
E5:E12	E5	=Sheet1!C5&" "&Sheet3!D6
F5:F12	F5	=INDEX(Table1[mg/week],MATCH(E5,Table1[Description],0))

Book1.xlsx
	C	D	E
5	Description	Tag	mg/week
6	Fertilizer 1 (4ml/5times week)	(4ml/5times week)	2
7	Fertilizer 2 (2ml/3times week)	(2ml/3times week)	5
8	Fertilizer 3		8
9	Fertilizer 4		1
10	Fertilizer 5		6
11	Fertilizer 6 (once day)	(once day)	0
12	Fertilizer 7		1
13	Fertilizer 8		6
14	Custom Product A	(every day)	5
15	Custom Product D		1
16	Custom Product H		5
Sheet3

Cell Formulas
Range	Formula
C6:C13	C6	=Sheet1!C5&" "&[@Tag]