Table Extract based on Font

**Aquamore** · 09-09-2014, 10:58 AM

Hi, I have a table that lists region numbers in column A, month numbers as headers and it's filled with values that represent sales (or blanks if there were no sales in the region in a particular month)
Some of the sales numbers are in bold font, I need to create a different table that would only include bold values along with the corresponding region number and month. The sample file is attached.
Tab "Records" has the data, tab "Summary" is what I need to create.
I would very much appreciated if somebody could help.
Thanks.

**Richard Buttrey** · 09-09-2014, 11:20 AM

Hi,

Personally I avoid trying to make Excel make decisions based on cell/font colours, backgrounds, Bold, etc. There's just too much area of risk. Someone somewhere will reformat something and screw things up. Always look for a more robust solution.

In this case one way in C" copied down would be

Formula:

=INDEX(Records!$A$1:$M$20,MATCH(A2,Records!$A:$A,FALSE),MATCH(B2,Records!$2:$2,FALSE))

Even better would be a restructuring of your data in a 3 column Table for Region, Month, Value.
You could then use a Pivot Table which opens up all sorts of possibilities for you and is far more efficient for this sort of analysis.

**sktneer** · 09-09-2014, 11:56 AM

Pay attention what Richard has advised you.
But if you want to stick on this, I think you need a VBA code to achieve this task. Please find the attached sheet to see if this is what you are trying to achieve. Click on the Green Button on the Records Sheet to get the desired output on the Summary Sheet.