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]

Thank you I will try to implement it that way. Will report when I am done.

 

[Polanskiman]

My suggestion would be this.

• 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.

I am trying to make this helper column but running into some issues. Do the two tables (table 1 and table 3) need to be the size, row wise?

 

[Polanskiman]

=MATCH([@Fertilizer],Sheet1!C$5:C$12,0)+COUNTIF(INDEX([Fertilizer],1):[@Fertilizer],[@Fertilizer])/10^6

After some fiddling it seem that the @ is supposed to be before the bracket not inside.

For me it worked like this:

=MATCH(@[Fertilizer],Sheet1!C$5:C$12,0)+COUNTIF(INDEX([Fertilizer],1):@[Fertilizer],@[Fertilizer])/10^6

else I was getting a syntaxe error. The other way that worked was to add additional brackets that would wrap the @ such as:

=MATCH([@[Fertilizer]],Sheet1!C$5:C$12,0)+COUNTIF(INDEX([Fertilizer],1):[@[Fertilizer]],[@[Fertilizer]])/10^6

 

[Peter_SSs]

Do the two tables (table 1 and table 3) need to be the size, row wise?

There isn't two tables as far as I am aware. You only ever showed one and I simply increased the number of columns in it.

After some fiddling it seem that the @ is supposed to be before the bracket not inside.

That is not so if the sheet is set up like mine.
You can have a look at a copy of my file here if you want to.

 

[Polanskiman]

There isn't two tables as far as I am aware. You only ever showed one and I simply increased the number of columns in it.

I meant the data in sheet 1. I called that a table, ok not an Excel table.

That is not so if the sheet is set up like mine.
You can have a look at a copy of my file here if you want to.

Yes, strange for me it said syntax error until I change the @ places. Thank you for the file.

Everything is working as you designed. One snag though. In the first Sheet a few of the fertilizers are on a different colum. Both lists start at the same row. Such as:

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

It's like that for visual purposes since that sheet is formated to look nice to the user. Can this be adapted to the formulas you provided above? I tried adding multiple ranges to the formula in the idx column but that didn't turn out so well.

 

[Peter_SSs]

Could this work for you?

Make a combined list on Sheet1 like this (& hide the column if you want) and have the formulas that were referencing column C of Sheet1 point to column J instead.

Polanskiman_1.xlsm
	C	D	E	F	G	H	I	J
4	Commercial fertilizer			Commercial Traces				Combined List
5	Fertilizer 1			Fertilizer 9				Fertilizer 1
6	Fertilizer 2			Fertilizer 10				Fertilizer 2
7	Fertilizer 3			Fertilizer 11				Fertilizer 3
8	Fertilizer 4							Fertilizer 4
9	Fertilizer 5							Fertilizer 5
10	Fertilizer 6							Fertilizer 6
11	Fertilizer 7							Fertilizer 7
12	Fertilizer 8							Fertilizer 8
13								Fertilizer 9
14								Fertilizer 10
15								Fertilizer 11
16
17
18
Sheet1

Cell Formulas
Range	Formula
J5:J18	J5	=IF(ROWS(J$5:J5)<=COUNTA(C$5:C$20),INDEX(C$5:C$20,ROWS(J$5:J5)),IF(ROWS(J$5:J5)<=COUNTA(C$5:C$20)+COUNTA(F$5:F$20),INDEX(F$5:F$20,ROWS(J$5:J5)-COUNTA(C$5:C$20)),""))

 

[Polanskiman]

Nice! Smart. This said I do not wish to have a hidden data table in that sheet as I like to have user sheets clean of any essential information. I'm a maniac! This said, please let me know if what I did is ok. I basically used that formula and created a combined list in a separate sheet which is meant to be hidden. It's working fine but just thought I would ask if this could have any repercussions in the future.

 

[Peter_SSs]

That sounds fine, or you could put it somewhere in the existing Sheet3 since that is already hidden & not for end users.

 

[Polanskiman]

That sounds fine, or you could put it somewhere in the existing Sheet3 since that is already hidden & not for end users.

Indeed. Could add it as a helper column. One bit that gets me curious though. In the index/match formula:

=IFERROR(INDEX(Table1[Description],MATCH(AGGREGATE(15,6,Table1[Idx],ROWS(A$1:A1)),Table1[Idx],0)),"")

the ROW function points to A$1:A1. What exactly does it mean? I ask because that range is blank in the example you provided.

 

[Peter_SSs]

the ROW function points to A$1:A1

It is actually the ROWS function, not ROW.

It is counting rows & what is in the cells is irrelevant

So ROWS(A$1:A1) returns 1
When that formula is copied down to the next row it becomes
ROWS(A$1:A2) which returns 2
Copied down again it becomes
ROWS(A$1:A3) which returns 3
etc

It is providing an incremental value as the 4th argument of the AGGREGATE function so that function returns the smallest Idx number then the 2nd smallest Idx number, 3rd smallest etc