Home » Blogs » vypinac2's blog

Conditional formating based on the sum of cells

Submitted by vypinac2 on 25 January, 2016 - 23:13

Hi, I am trying to identify cells sum of which equals to cerain value/amount. Is there a way of doing this at all? Thank you.

»

Thank you very much, we will

Submitted by vypinac2 on 31 January, 2016 - 15:35.

Thank you very much, we will have to try it that way. I was already told by my colleuge that this is very challenging due to countless amount of calculations and results but thought I would try anyway. Thanks again.

»

Conditional Formatting

Submitted by Excel4Business on 26 January, 2016 - 17:01.

Hello,
You could use a formula within the Conditional Formatting

IF A1 has a value of 5 and you want this (or another cell to be formatted) based on this value, you could select A1,
Open up Conditional formatting, select New Rule, then Use Formula to determine which cells to format
Then inside the formula bar you can write
=$A$1=5
then add some formatting such as a background color.
Save your new conditional format
When A1 becomes 5, it will be formatted with the format you sent.
I hope this helps. Should you have any additional questions, please do not hesitate to ask.

»

Hi, Thank you for your reply

Submitted by vypinac2 on 31 January, 2016 - 11:14.

Hi, Thank you for your reply but this is not really what I meant. Imagine you have 50 cells with different values in each of them and I need to find out which of these 50 cells sum up to certain amount, 1123.47 for example.

Here is what I am dealing with. Restaurant declares every day an amount of notes and coins they took but the deposit into the bank account is done as a lump sum of 4 or 5 days worht of all the monies they took. Now I need to find out which deposit relates to which days. Thank you again.

»

Hmm

Submitted by Excel4Business on 31 January, 2016 - 14:12.

Hi thanks for the details, as far as i know that is not that easy as there could be thousands of combinations without a specific amount.
My suggestion is (and you wont like it) Tell the Restaurant to submit a daily cash report, and each day is a single line item, if they have 5 days, they have to write down which days, and amounts are being deposited.
I know that does not solve your problem, and frankly i dont know how excel could do that if i understand your question correctly that any # of days could be or could not be included in that balance, and all you have to go on is a single total, then its extremely difficult.
The right way, and what every other business does, is include a daily cash manifest, and that deposit total must include which days and which amounts.
sorry i could not be more helpful.

»

Hmm

Submitted by Excel4Business on 31 January, 2016 - 14:12.

Hi thanks for the details, as far as i know that is not that easy as there could be thousands of combinations without a specific amount.
My suggestion is (and you wont like it) Tell the Restaurant to submit a daily cash report, and each day is a single line item, if they have 5 days, they have to write down which days, and amounts are being deposited.
I know that does not solve your problem, and frankly i dont know how excel could do that if i understand your question correctly that any # of days could be or could not be included in that balance, and all you have to go on is a single total, then its extremely difficult.
The right way, and what every other business does, is include a daily cash manifest, and that deposit total must include which days and which amounts.
sorry i could not be more helpful.

»