Solved

Excel Question Referring to Table Column in Condtional Formatting Formula bar

Posted on 2014-09-16
9
265 Views
Last Modified: 2014-09-16
plz see the attached file.

i have two sheets that i have highlighted ListB with Conditional Formatting COUNTIFS formula and i used named range.
=COUNTIFS(Product,$A2)   Product is the named range.  it perfectly works.

Now, i tried this with another senario, i have two sheets and in the " ListB HighlightNEED" i want to highlighted similar to the previous senario but this time using the table column name  =COUNTIFS(Table[Product],$A2) but excel does not allow me to use this.  i was wondering how to refer the table column in the abovementioned formula inside condtional formatting.
Book1.xlsx
0
Comment
Question by:ProfessorJimJam
  • 5
  • 2
  • 2
9 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40325593
What do you mean by:
table column name  =COUNTIFS(Table[Product],$A2)

Are you referring to the Table in sheet ListA Table ?

Not clear what you want to achieve if you can explain.
gowflow
0
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40325770
please see the example attached.  you can see that the referrence to the table works as input into the cell but when i use the same formula inside condtional formatting then it wont accept it.

Capture.PNGBook1.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40325820
I see what you mean but still for me Table[Product] is same as Product ??? not sure !!!

anyhow I found something that maybe would help regarding Tables and conditional formatting.

http://excelsemipro.com/2011/04/excel-tables-styles-conditional-formatting-and-data-validation/

What are you after in the formula that you have ? can you clarify ?
gowflow
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40326684
gowflow.

I do not think you understand my question at all.    the link you shared is completely irrelevant to the question I asked.


perhaps the other two sheets got you confused.  now I attached the workbook and I deleted the irrelevant sheets.

please look at the attached workbook.  it has two sheets only.  in sheet ListB  range A2:A13  there is a conditional formatting and the formula used to trigger the conditional formatting is =COUNTIFS(ListA!$A$2:$A$9,$A2)  and you can see it works perfectly.  any value in the range A2:A13 of ListB sheet is highlighted in yellow if it exists in sheet ListA Range A2:A9.    now, you can see that the ListA sheet data is in the excel table and I am sure you know how the columns of table are referenced in cells for example ListA range A2:A9 can be referenced in formulas like this Table[Product]
so, what exactly I am looking for is that the formula =COUNTIFS(ListA!$A$2:$A$9,$A2) that I currently have. I want to use the table reference instead of using "ListA!$A$2:$A$9"  .   you can see in the attached workbook ListB cell C2 and D2 shows the formula that I used =COUNTIFS(Table[Product],$A2) instead of =COUNTIFS(ListA!$A$2:$A$9,$A2)
and in cell it works. however, when I put the =COUNTIFS(Table[Product],$A2) into the conditional formatting then it does not accept and gives me warning that it cannot accept the table reference.  
my main concern is that if the formula =COUNTIFS(Table[Product],$A2)  can work in a cell, why cannot it work in the conditional formatting.

I hope this time it Is crystal clear what my goal is to achieve.
0
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40326688
the attachment uploaded
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40326703
You have to use an INDIRECT function in order to use structure referencing for conditional formats:
conditional formatting - table
=COUNTIF(INDIRECT("Table[Product]"),A2)

I've run into this before with Data Validation and don't know any other way to do it.

Regards,
-Glenn
0
 
LVL 25

Author Closing Comment

by:ProfessorJimJam
ID: 40326796
you are Genius Glenn.

you solved the mysterious issue
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40326803
Nope, not a Genius; just happened to have experienced this before you did.  I was just a frustrated and surprised by the solution.

-Glenn
0
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40326819
indeed you are very experienced expert in Excel.  I was beating my brain against the wall trying different methods and it was not working.  from what I understood, the same solution applies if faced with the data validation. right?

thank you again for saving my day
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now