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]

Got you. Excellent! Thank you very much. This closes my question. I will do my best to be more precise the next time I post a question.

 

[Peter_SSs]

Cheers. Glad we got there in the end.

 

[Polanskiman]

G6:G16

G6

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

Hi, this formula although working for Excel 365 is not for earlier versions of Excel. I get the _xlfn.SINGLE in place of the @ in Excel 2010. I suppose this is due to the fact that dynamic array functions are not supported by earlier versions. So I tried updating the formula to something like this:

{=MATCH($G7:$G58,$E$7:$E$58,0)+COUNTIF(INDEX($G$7:$G$58,1):$G7:$G58,$G7:$G58)/10^6}

Only problem is, it is not giving a unique IDx to fertilizers that have the same name. For instance:

How should the formula be updated so that it works with Excel 2010 up?

Thank you.

 

[Peter_SSs]

I don't have an earlier version to test on so diagnosis may be difficult.

Do you get _xlfn.SINGLE everywhere in the formula there is an @ or only one/some of the places?

So I tried updating the formula to something like this:

I can't comment on that alternative unless you tell us what cell the 'Fertilizer' heading is in in the Sheet3 table.

 

[Polanskiman]

Do you get _xlfn.SINGLE everywhere in the formula there is an @ or only one/some of the places?

Yes everywhere, such as:

=MATCH(_xlfn.SINGLE([products+DIY]),[raw_products_import],0)+COUNTIF(INDEX([products+DIY],1):_xlfn.SINGLE([products+DIY]),_xlfn.SINGLE([products+DIY]))/10^6

I can't comment on that alternative unless you tell us what cell the 'Fertilizer' heading is in in the Sheet3 table.

"Fertilizer" heading is on cell G6. In my case the heading is called "products+DIY" as you can see in the above formula.

 

[Peter_SSs]

"Fertilizer" heading is on cell G6.

So, what happens if you put this in row 7 of the Idx column of the table in Sheet3?

=MATCH(G7,Sheet1!C$5:C$12,0)+COUNTIF(G$7:G7,G7)/10^6

 

[Polanskiman]

So, what happens if you put this in row 7 of the Idx column of the table in Sheet3?

=MATCH(G7,Sheet1!C$5:C$12,0)+COUNTIF(G$7:G7,G7)/10^6

I think you know the answer to that. It works perfectly. Thank you so much. I sent you the actual file in PM including your last mod (RCFerts Sheet, column F) in case you are curious to know what I have been working on for that past 6 months. Many of the formulas in that file I owe them to you.

Anyway thank you again.

 

[Peter_SSs]

It works perfectly.

Glad it worked for you.

BTW, I don't have time to look at the file. As long as it is working for you, I'm happy.