One cell multiple formula / conditional formatting

carladdo6

New Member
Joined
Jul 6, 2016
Messages
3
Hello,

I'm trying to set up a spread sheet with multiple sheets all linking back to a summary sheet and its nearly complete I just need help and to see if its possible for this function to work.

I have set up a cell using the formula '=IF(Summary!E2>C4+30,1,0)' which changes the next cell along to a 1 or 0 depending if 30 days have passed since the invoice date, this works perfectly. if the cell is populate with a 1 I know to move it over so its due to be paid and I will process it, once processed I will move the figure on the sheet from unpaid to paid. I have set up conditional formatting so that when the cell is populated with a 1 it shows as red so it stand out that it needs to be paid. I now want that cell to change from a 1 to 0.5 as soon as I move the figure into the paid box and I can set the conditional formatting to show green if the cell is equal to 0.5. I have tried the formula '=IF(Summary!E2>C4+30,1,0)*((IF($G4>1,0.5,0)))' but unfortunately it wont work.

Any ideas?

Many thanks.
 
So if you looking to change the value of the cell containing the formula then try this.... =IF(AND(Summary!E2>C4+30, $G4>1),1,0.5)
Is this what you looking for?
 
Upvote 0
Assuming your table is set up similar to this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Unpaid[/TD]
[TD]Paid[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]5/20/16[/TD]
[TD]Formula[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]6/15/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]7/5/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

In Cell D2 try:
Code:
=IF(G2>0,0.5,IF(Summary!E2>C2+30,1,0))

and copy down. May need to change cell references depending on what your sheet and summary look like.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top