Insert value based on color of the cell

[aberdonian user]

Dear Experts,

I wonder if it is possible to insert value from a another cell based on the
color of the cell?

For example in A1:

=IF([Green],B1*30,IF([Yellow],B2*30,0))

Thank you in advance.

Regards,

Jim

 

[T. Valko]

IMHO you should not base calculations on cell formats. The reason is that
changing a cell format does not trigger a calculation so if you change the
cell color your formula *will not* update until some other event triggers a
calculation.

Why are the cells either green or yellow? You can probably build a formula
to do what you want based on the logic of *why* a cell is either green or
yellow.

That being said, look here for the technique:

http://xldynamic.com/source/xld.ColourCounter.html

Make sure you read the section: Constraints!

 

[aberdonian user]

Thanks Biff,

You are right. I made it too complicated. I found another, easy way to do
what I need. Quite happy with that.

Thanks anyway.

Regards,
Jim

IMHO you should not base calculations on cell formats. The reason is that
changing a cell format does not trigger a calculation so if you change the
cell color your formula *will not* update until some other event triggers
a calculation.

Why are the cells either green or yellow? You can probably build a formula
to do what you want based on the logic of *why* a cell is either green or
yellow.

That being said, look here for the technique:

http://xldynamic.com/source/xld.ColourCounter.html

Make sure you read the section: Constraints!

--
Biff
Microsoft Excel MVP

Dear Experts,

I wonder if it is possible to insert value from a another cell based on
the color of the cell?

For example in A1:

=IF([Green],B1*30,IF([Yellow],B2*30,0))

Thank you in advance.

Regards,

Jim

 

[T. Valko]

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks Biff,

You are right. I made it too complicated. I found another, easy way to do
what I need. Quite happy with that.

Thanks anyway.

Regards,
Jim

IMHO you should not base calculations on cell formats. The reason is that
changing a cell format does not trigger a calculation so if you change
the cell color your formula *will not* update until some other event
triggers a calculation.

Why are the cells either green or yellow? You can probably build a
formula to do what you want based on the logic of *why* a cell is either
green or yellow.

That being said, look here for the technique:

http://xldynamic.com/source/xld.ColourCounter.html

Make sure you read the section: Constraints!

--
Biff
Microsoft Excel MVP

Dear Experts,

I wonder if it is possible to insert value from a another cell based on
the color of the cell?

For example in A1:

=IF([Green],B1*30,IF([Yellow],B2*30,0))

Thank you in advance.

Regards,

Jim