# Dynamic cell and sorting

#### Polanskiman

##### Board Regular
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
BCDEFGHI
1
2In Sheet 1In Sheet 2In Sheet 3
3
4Product 1Product 1 newDescriptionTag
5Product 2Product 1new
6Product 3Product 2old **
7Product 4Product 3used
8Product 5Product 4
9Product 6Product 5
10Product 6new
11Product 7
12Product 8
13
Sheet2
Cell Formulas
RangeFormula
E4E4=Sheet2!C4&" "&Sheet2!H5

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Peter_SSs

##### MrExcel MVP, Moderator
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.

Excel Formula:
``=Sheet1!C4&" "&VLOOKUP(Sheet1!C4,Table1,2,0)``

#### EXCEL MAX

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

#### Polanskiman

##### Board Regular
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.

Excel Formula:
``=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
BCDEFGHIJKL
1
2In Sheet 1In sheet 2In Sheet 3
3
4Product 1DescriptionValueDescriptionTagValue
5Product 2#N/A#N/AProduct 1 newnew2
6Product 3Product 2 old **5Product 2 old **old **5
7Product 4Product 3 used8Product 3 usedused8
8Product 5Product 4 1Product 4 1
9Product 6Product 5 6Product 5 6
10Product 7Product 6 new0Product 6 newnew0
11Product 8Product 7 1Product 7 1
12Product 8 6Product 8 6
13Custom Product A5
14Custom Product D1
15Custom Product H5
16
Sheet2
Cell Formulas
RangeFormula
E5E5=C4&" "&VLOOKUP(C4,Table1,2,0)
F5:F12F5=INDEX(Table1[Value],MATCH(E5,Table1[Description],0))
E6:E12E6=C5&" "&J6
I5:I12I5=C4&" "&[@Tag]

#### Polanskiman

##### Board Regular

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

##### MrExcel MVP, Moderator
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
4Product 1
5Product 2
6Product 3
7Product 4
8Product 5
9Product 6
Sheet1

Polanskiman.xlsm
GH
4DescriptionTag
5Product 3used
6Product 2old **
7Product 1new
8Product 6new
9Product 4
10Product 5
11Product 7
12Product 8
Sheet3

Cell Formulas
RangeFormula
E4:E9E4=Sheet1!C4&" "&VLOOKUP(Sheet1!C4,Table1,2,0)

#### Polanskiman

##### Board Regular

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.

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### Well-known Member
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

Power Query:
``````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

##### Board Regular
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
4Commercial fertilizer
5Fertilizer 1
6Fertilizer 2
7Fertilizer 3
8Fertilizer 4
9Fertilizer 5
10Fertilizer 6
11Fertilizer 7
12Fertilizer 8
Sheet1

Cell Formulas
RangeFormula
E5:E12E5=Sheet1!C5&" "&Sheet3!D6
F5:F12F5=INDEX(Table1[mg/week],MATCH(E5,Table1[Description],0))

Book1.xlsx
CDE
5DescriptionTagmg/week
6Fertilizer 1 (4ml/5times week)(4ml/5times week)2
7Fertilizer 2 (2ml/3times week)(2ml/3times week)5
8Fertilizer 3 8
9Fertilizer 4 1
10Fertilizer 5 6
11Fertilizer 6 (once day)(once day)0
12Fertilizer 7 1
13Fertilizer 8 6
14Custom Product A(every day)5
15Custom Product D1
16Custom Product H5
Sheet3
Cell Formulas
RangeFormula
C6:C13C6=Sheet1!C5&" "&[@Tag]

Replies
13
Views
182
Replies
15
Views
429
Replies
2
Views
624
Replies
0
Views
278
Replies
8
Views
619