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

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

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

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Diddy" wrote:

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

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

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.

"Diddy" wrote:

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

Is it possible to pick up formatting from range using index ma
 

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

"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Diddy" wrote:

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

Is it possible to pick up formatting from range using index ma
 

Thank you Jacob :-)

"Jacob Skaria" wrote:

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.

"Diddy" wrote:

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