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

 

[Polanskiman]

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

Never used Power Query. Let me got get a cold compress first, lay down and come back when my fever is down.

 

[sandy666]

Never used Power Query. Let me got get a cold compress first, lay down and come back when my fever is down.

sure, no problem

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[Peter_SSs]

Try this in Sheet2. My formula in col E, your same one in col F.

Polanskiman_1.xlsm
	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	=VLOOKUP(Sheet1!C5&" *",Table1[Description],1,0)
F5:F12	F5	=INDEX(Table1[mg/week],MATCH(E5,Table1[Description],0))

 

[Polanskiman]

Try this in Sheet2. My formula in col E, your same one in col F.

Polanskiman_1.xlsm
	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	=VLOOKUP(Sheet1!C5&" *",Table1[Description],1,0)
F5:F12	F5	=INDEX(Table1[mg/week],MATCH(E5,Table1[Description],0))

Ahh thank you. We are getting there. Seems to be working now (partially). Again because of me sorry.... I just didn't forsee it as a problem, but some cells in Table1 of Sheet 3 sometimes are referring to the same cells in Sheet 1. Example below see yellow cells. How would I adjust the VLOOKUP formula in Sheet 2?

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

Cell Formulas
Range	Formula
C9,C11:C16	C9	=Sheet1!C5&" "&[@Tag]
C10	C10	=Sheet1!C5&" "&[@Tag]

 

[sandy666]

the same code as in post#9

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

 

[Peter_SSs]

How would I adjust the VLOOKUP formula in Sheet 2?

With that example, what is in Sheet1 (has that changed too?) and what results do you want in Sheet2?

 

[Polanskiman]

With that example, what is in Sheet1 (has that changed too?) and what results do you want in Sheet2?

Sheet 1 hasn't changed. It's just that sometime in Table 1 of Sheet 3 some cells will pick up the same cells in Sheet 1.

The result in Sheet 2 is working as it should except if the same cell is referenced twice in Table 1. Here is what happens in the example we are working on:

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

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

As you can see VLOOKUP is picking up twice the same thing when in fact if you look at Sheet 3 although there are 2 "Fertilizer 1", both have different tags.

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

Cell Formulas
Range	Formula
C9,C11:C16	C9	=Sheet1!C5&" "&[@Tag]
C10	C10	=Sheet1!C5&" "&[@Tag]

 

[Peter_SSs]

So why are any of the formulas on Sheet2 looking at Sheet1 if they are not using that list?
For example, Sheet1 still has "Fertilizer 2" yet you are apparently ignoring that one in Sheet2?
Can you put in words, how you decide what goes in Sheet2 column E?

Is a formula solution that works in Excel 365 but not Excel 2016 acceptable?
In your Excel 365, do you have ..
- the SORTBY function?
- the LET function?

 

[Polanskiman]

I will try my best to explain:
What I am building is a calculator for fertilizers.
Sheet 1: This sheet contains commercial fertilizer names. This is a setting sheet where the user input fertilizer price, volume among other data. Fertilizer names already hardcoded there and the user does not input any new fertilizer names. This is the source for fertilizer names across all other sheets.
Sheet 2: This sheet is a sheet that basically regroups prices of all fertilizers, weekly cost according to users' use of the product etc. This sheet is meant to be seen by the user but no interaction or input is required by the user. It's purely informational.
Sheet 3: This is the backbone of the calculator. Everything is calculated here (prices, mg, ppm, you name it). This is an Excel Table solely aimed at calculating stuff but it's not meant to be seen by the end user but it is seen by people currently working on it. Sheet will be hidden.

There are several other user sheets where the user sets his fertiliser dosing etc etc etc, but my problem lays between these 3. Well more precisely with Sheet 2. The reason of my request is that I want to make things foolproof. As it currently is (before I posted here) everything is "working fine". The only problem is that if for some reason the Excel Table in sheet 3 is sorted then all goes to hell in sheet 2 for a few fertilizers because of the tag column attached to them. I want to avoid this screw up.

So why are any of the formulas on Sheet2 looking at Sheet1 if they are not using that list?

Sheet2 is simply picking up the names of the fertilizers. Some fertilizers need to be duplicated because they may have different dosing regimes, hence the tag column to differentiate them.

For example, Sheet1 still has "Fertilizer 2" yet you are apparently ignoring that one in Sheet2?

I am not ignoring it. It was there before but because of the issue I mentioned in post #14 I edited Fertilizer 2 to show that sometimes I have twice the same fertilizer name in that table. "Fertilizer 2" will also be in the table.

Can you put in words, how you decide what goes in Sheet2 column E?

All fertilizers names in Sheet 2 are coming from Sheet 1. They are all there. It's just that some need to be duplicated because in Sheets 3 were all the calculations are done, some of the fertilizers need to duplicated.

Is a formula solution that works in Excel 365 but not Excel 2016 acceptable?
In your Excel 365, do you have ..
- the SORTBY function?
- the LET function?

I would like this to be the most backward compatible as possible, because the file will be distributed publicly, so anything that is working only with 365 might not be a good solution. Yes both functions are present in my Excel.

Thank you. Hopefully this explains better.

 

[Peter_SSs]

My suggestion would be this.

• Since end user does not see Sheet3 and this whole sheet is about fertilizers, I would include a column in your table for fertilizer name, like you had in post 1. That column may be useful for people working on the table too.
• Notice a change in the Description column formula in the table too.
• Also include a helper column in the table as shown below. If you want, this column could be hidden once you have entered the formula.
• Sheet 2 could then draw all the information from the table, without reference back to Sheet1.

Original order for table

Polanskiman_1.xlsm
	C	D	E	F	G
5	Fertilizer	Description	Tag	mg/week	Idx
6	Fertilizer 1	Fertilizer 1 (2ml/3times week)	(2ml/3times week)	5	1.000001
7	Fertilizer 1	Fertilizer 1 (4ml/5times week)	(4ml/5times week)	2	1.000002
8	Fertilizer 3	Fertilizer 3		8	3.000001
9	Fertilizer 4	Fertilizer 4		1	4.000001
10	Fertilizer 5	Fertilizer 5		6	5.000001
11	Fertilizer 6	Fertilizer 6 (once day)	(once day)	0	6.000001
12	Fertilizer 7	Fertilizer 7		1	7.000001
13	Fertilizer 8	Fertilizer 8		6	8.000001
14	Custom Product A	Custom Product A (every day)	(every day)	5	#N/A
15	Custom Product D	Custom Product D		1	#N/A
16	Custom Product H	Custom Product H		5	#N/A
Sheet3

Cell Formulas
Range	Formula
C6,C8:C13	C6	=Sheet1!C5
C7	C7	=Sheet1!C5
D6:D16	D6	=[@Fertilizer]&" "&[@Tag]
G6:G16	G6	=MATCH([@Fertilizer],Sheet1!C$5:C$12,0)+COUNTIF(INDEX([Fertilizer],1):[@Fertilizer],[@Fertilizer])/10^6

Results:

Polanskiman_1.xlsm
	E	F
4	Description	mg/week
5	Fertilizer 1 (2ml/3times week)	5
6	Fertilizer 1 (4ml/5times week)	2
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
13
14
Sheet2

Cell Formulas
Range	Formula
E5:E14	E5	=IFERROR(INDEX(Table1[Description],MATCH(AGGREGATE(15,6,Table1[Idx],ROWS(A$1:A1)),Table1[Idx],0)),"")
F5:F14	F5	=IF(E5="","",INDEX(Table1[mg/week],MATCH(E5,Table1[Description],0)))

Re-order table:

Polanskiman_1.xlsm
	C	D	E	F	G
5	Fertilizer	Description	Tag	mg/week	Idx
6	Fertilizer 6	Fertilizer 6 (once day)	(once day)	0	6.000001
7	Fertilizer 4	Fertilizer 4		1	4.000001
8	Fertilizer 7	Fertilizer 7		1	7.000001
9	Custom Product D	Custom Product D		1	#N/A
10	Fertilizer 1	Fertilizer 1 (4ml/5times week)	(4ml/5times week)	2	1.000001
11	Fertilizer 1	Fertilizer 1 (2ml/3times week)	(2ml/3times week)	5	1.000002
12	Custom Product A	Custom Product A (every day)	(every day)	5	#N/A
13	Custom Product H	Custom Product H		5	#N/A
14	Fertilizer 5	Fertilizer 5		6	5.000001
15	Fertilizer 8	Fertilizer 8		6	8.000001
16	Fertilizer 3	Fertilizer 3		8	3.000001
Sheet3

Cell Formulas
Range	Formula
C6	C6	=Sheet1!C10
C7	C7	=Sheet1!C8
C8	C8	=Sheet1!C11
C10,C14	C10	=Sheet1!C5
C11	C11	=Sheet1!C5
D6:D16	D6	=[@Fertilizer]&" "&[@Tag]
C15	C15	=Sheet1!C12
C16	C16	=Sheet1!C7
G6:G16	G6	=MATCH([@Fertilizer],Sheet1!C$5:C$12,0)+COUNTIF(INDEX([Fertilizer],1):[@Fertilizer],[@Fertilizer])/10^6

Results:

Polanskiman_1.xlsm
	E	F
4	Description	mg/week
5	Fertilizer 1 (4ml/5times week)	2
6	Fertilizer 1 (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
13
14
Sheet2

Cell Formulas
Range	Formula
E5:E14	E5	=IFERROR(INDEX(Table1[Description],MATCH(AGGREGATE(15,6,Table1[Idx],ROWS(A$1:A1)),Table1[Idx],0)),"")
F5:F14	F5	=IF(E5="","",INDEX(Table1[mg/week],MATCH(E5,Table1[Description],0)))