Is it possible to pick up formatting from range using index match?


D

Diddy

Hi everyone,

I'm using index match to pick up data from another sheet in the same workbook.


=INDEX(_2007Low,MATCH($B$1,Room,0))
=INDEX(_2007Res,MATCH($B$1,Room,0))
=INDEX(_2007Upp,MATCH($B$1,Room,0))

Data
A B C D E
4 315 Blank 80 83 88
5 316 Blank 64 Blank 75
6 317 Blank 65 70 76

C is _2007Low
D is _2007Res
E is _2007Upp

The way the data sheet has been set up is that the Result in D is formatted
with shading but when it falls on the upper or lower limit D is left blank
and the value in either C or D is shaded.

So I'm pulling data from C,D and E but would like to know if It's possible
to pull any shading along with it?

Many thanks
Diddy
 
Ad

Advertisements

L

Luke M

This is actually a bit of a double whammy. First, XL does not have any native
formulas for detecting formatting. You could create a UDF in VisualBasic that
detects the formatting, and possibly use that, however...

It sounds like you're using Conditional Formats, which come with their own
set of headaches regarding trying to find out what format is applied!
CPearson has an article about trying to find out, but due pay attention to
the "NOTES" he calls out.

http://www.cpearson.com/excel/CFColors.htm
 
J

Jacob Skaria

There are no built-in functions to do this....Using a UDF you can get the
colorindex of the lookup cell but dont think you can return the color to the
formula cell.
 
D

Diddy

Hi Luke,

Thanks for replying :)

Not sure how the data worksheet was generated but it's not using Conditional
formatting.

Looks like a lost cause

Thank you anyway
 
Ad

Advertisements

D

Diddy

Thank you Jacob :)

Jacob Skaria said:
There are no built-in functions to do this....Using a UDF you can get the
colorindex of the lookup cell but dont think you can return the color to the
formula cell.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top