INDEX and format of cells

[uriel78]

Is it possibile to obtain that INDEX functions give back a value with its
format (colour, text...)?

 

[David McRitchie]

I do not quite understand the question,
but with User Defined Functions you can obtain the
You can get the Color Index
value of the font or the interior (pattern) of cell,
see Chip Pearson's "Functions For Cell Colors
http://www.cpearson.com/excel/colors.htm

However, there should be better ways of keeping track of
things than trying to match colors. Values of cells would
be a lot better.

For more information on Color see
http://www.mvps.org/dmcritchie/excel/colors.htm

 

[uriel78]

Thank you for the answer!
I've already seen Pearson's suggestions, but still I don't know how to apply
them to my case.

In sheet 1 column A:A I've got a series of values called (e101,e302...and so
on) formatted with two colours
In sheet 2, I use index function to retrieve those values

for example: in sheet2

D2=IF(C3="";"";INDEX(sheet1$A:$A;C3))

In this case, in D2 I obtain "e302"

Now, I just want that D2 value is formatted as its corripondent ("e302") in
sheet1 A:A

I hope to be more clear this time...

I do not quite understand the question,
but with User Defined Functions you can obtain the
You can get the Color Index
value of the font or the interior (pattern) of cell,
see Chip Pearson's "Functions For Cell Colors
http://www.cpearson.com/excel/colors.htm

However, there should be better ways of keeping track of
things than trying to match colors. Values of cells would
be a lot better.

For more information on Color see
http://www.mvps.org/dmcritchie/excel/colors.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Is it possibile to obtain that INDEX functions give back a value with its
format (colour, text...)?

 

[David McRitchie]

See my page on color. It will show you how to use Chip's
functions. http://www.mvps.org/dmcritchie/excel/colors.htm

If it is a matter of how to install a user function see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thank you for the answer!
I've already seen Pearson's suggestions, but still I don't know how to apply
them to my case.

In sheet 1 column A:A I've got a series of values called (e101,e302...and so
on) formatted with two colours
In sheet 2, I use index function to retrieve those values

for example: in sheet2

D2=IF(C3="";"";INDEX(sheet1$A:$A;C3))

In this case, in D2 I obtain "e302"

Now, I just want that D2 value is formatted as its corripondent ("e302") in
sheet1 A:A

I hope to be more clear this time...

I do not quite understand the question,
but with User Defined Functions you can obtain the
You can get the Color Index
value of the font or the interior (pattern) of cell,
see Chip Pearson's "Functions For Cell Colors
http://www.cpearson.com/excel/colors.htm

However, there should be better ways of keeping track of
things than trying to match colors. Values of cells would
be a lot better.

For more information on Color see
http://www.mvps.org/dmcritchie/excel/colors.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Is it possibile to obtain that INDEX functions give back a value with its
format (colour, text...)?

 

[David McRitchie]

Sorry I didn't really read you reply post and it was too late to delete
my reply. I guess you've seen Chip's page on MATCH and INDEX
Lookups
http://www.cpearson.com/excel/lookups.htm

You want the same format in a cell that retrieves the value of another
cell and you cannot do that with a function. A function cannot change
the formatting of any cell including itself. If there are only 3 possible
colors (4 with the default) you might be able to use conditional formatting.
If the formatting is a change of numeric formatting or something other
than CF you would need to use a macro.

A change event macro and you would have to program it from when the
E302 cell changes. http://www.mvps.org/dmcritchie/excel/event.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thank you for the answer!
I've already seen Pearson's suggestions, but still I don't know how to apply
them to my case.

In sheet 1 column A:A I've got a series of values called (e101,e302...and so
on) formatted with two colours
In sheet 2, I use index function to retrieve those values

for example: in sheet2

D2=IF(C3="";"";INDEX(sheet1$A:$A;C3))

In this case, in D2 I obtain "e302"

Now, I just want that D2 value is formatted as its corripondent ("e302") in
sheet1 A:A

I hope to be more clear this time...

I do not quite understand the question,
but with User Defined Functions you can obtain the
You can get the Color Index
value of the font or the interior (pattern) of cell,
see Chip Pearson's "Functions For Cell Colors
http://www.cpearson.com/excel/colors.htm

However, there should be better ways of keeping track of
things than trying to match colors. Values of cells would
be a lot better.

For more information on Color see
http://www.mvps.org/dmcritchie/excel/colors.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Is it possibile to obtain that INDEX functions give back a value with its
format (colour, text...)?