payment staus sheet

**jon@karhu** · 12-02-2008, 01:55 PM

Good afternoon
This is a new payment status sheet that my company has put together to use with future clients. There are a few things that we would like fixed but can not figure out ourselves. I have attatched the document along with some notes as to what we can not get to work. Any help would be great.

**NBVC** · 12-02-2008, 02:00 PM

To make a cell empty until a value is filled elsewhere, you need an IF() statement...

for example in L45 replace your formula with: =IF(J43="","",L43+L44)

similarly for other cells.

**JBeaucaire** · 12-02-2008, 03:19 PM

Any particular reason your sheet is 3 megabytes? I couldn't figure it out. I cut and pasted it into another sheet and it dropped to 25k... Very strange.

Also, the last two formulas don't seem to draw final numbers on your original sheet. I would recheck them.

**jon@karhu** · 12-03-2008, 04:50 PM

Im not sure why it was that big, I didnr create it. What happens right now is someone creates a form like this that suits their needs and gets it as close to working as possible then I make the last few formulas to work. I will try the IF thing and see if I can get it to work.

**JBeaucaire** · 12-03-2008, 04:52 PM

It's all working in the one I uploaded, except you need proper formulas in the last two cells.

**jon@karhu** · 12-03-2008, 05:38 PM

The last 2 formulas dont work because they arent connected to the rest of the sheet yet. I cant seem to get the IF staments to work. I dont really understand how it is supposed to work. Please explain it more clearly.

**JBeaucaire** · 12-03-2008, 05:42 PM

Which IF statement are you having trouble with? In the one I uploaded, cell D46 has this formula: =IF($C$45>0,SUM(D45:D46),"")

This breaks down logically as: If ( [test] , [do this if true] , [do this if false] )

The test looks to see if cell C45 is greater than zero. If it is, it sums the cells above D46 and shows the total. If cell C45 is still zero, it presents a null response, shown in the formula as quotes with nothing (null) between them.

EDIT: If you tell me what the final two cells are supposed to add up, I can fix them as well.

**jon@karhu** · 12-04-2008, 08:00 AM

ok So I managed to get the IF statements to half work. They show the number I want when there is a number in the cell but it just reads false until there is a number placed in the appropriate cell. Is there a way for it to just read 0?

**JBeaucaire** · 12-04-2008, 08:04 AM

=IF(test,true,false)

Your formulae are missing the last section, so put a 0 after the second comma to get a zero for the "false" result.

The one I posted didn't have any of those showing, so what are you doing differently? Just use the one I posted and fix those last two cells. (nudge)

No reason to keep using a 3mb version of a doc that only needs to be 25k.

**JBeaucaire** · 12-04-2008, 08:14 AM

=IF(test,true,false)

Your formulae are missing the last section, so put a 0 after the second comma to get a zero for the "false" result.

The one I posted didn't have any of those showing, so what are you doing differently? Just use the one I posted and fix those last two cells. (nudge)

No reason to keep using a 3mb version of a doc that only needs to be 25k.

**jon@karhu** · 12-04-2008, 09:11 AM

I was going to use the one you reposted but it is missing several formulas from different parts of the sheet. Any idea why my version is so big? Is there anyway to reduce it?

**jon@karhu** · 12-04-2008, 10:00 AM

Ok so I got all of the If formulas working as I want them to now I have 2 formulas left to get working but I cant seem to wrap my head around how to make them work.
The first one being "total amount invoiced to date" I need this cell to add all of the invoices that have been issued (AG36:AG42, AG45). The first part is easy it just adding up the numbers but when I add in AG45 it wont work because there is no value there until the final invoice has been written. So what needs to happen is the cell totals the invoices written excluding the final draw (AG36:AG42) and then when the final draw is issued it adds in AG45.
The second issue is "Aamount still to be invoiced(plus any new change orders). This adds the quote amount (AG22), the change order amount (AG33), and then subtracts the amount already invoiced. I have no idea why this formula doesnt worh I thought it was pretty straight forward.
I think all the rest of the formulas work at this point.
The last question I have is how could I go about making the file smaller? as it has been stated above it is far bigger than it needs to be, but I dont understand why.

Thanks for all your time.