Dynamic cell and sorting

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
107
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
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
 

Some videos you may like

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
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 11, 2020
Messages
243
Office Version
  1. 2007
Platform
  1. Windows
Add sign "$" to fix value location "=Sheet2!$C$4&" "&Sheet2!$H$5"
 

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
107
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
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
Joined
Nov 29, 2011
Messages
107
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 29, 2011
Messages
107
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,415
maybe
In Sheet 1In sheet 2In Sheet 3
ProductDescriptionValueDescriptionTagValue
Product 1Product 1 new2Product 86
Product 2Product 2 old **5Product 71
Product 3Product 3 used8Product 6new0
Product 4Product 41Product 56
Product 5Product 56Product 41
Product 6Product 6 new0Product 3used8
Product 7Product 71Product 2old **5
Product 8Product 86Product 1new2
Custom Product H5
Custom Product D1
Custom Product A5

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
Joined
Nov 29, 2011
Messages
107
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,118,780
Messages
5,574,199
Members
412,576
Latest member
abdulla88
Top