cw18's spreadsheet Q&A thread

ljaneyr

Hi, this is my first post!

I have just downloaded the spreadsheet and filled in Jan and Feb from my old fashioned paper version and all seems fine except that the T & Cs total hasn't appeared in the spent column on the totals page. Also I've just noticed that Feb's gorceries total hasn't come up either.

I have little experience with excel but should be able to follow any instructions without too much trouble!

I hope you can help - the spreadsheet is really great, I forsee many happy hours spent looking at my spendings/savings!

cw18

Did you download the version for Excel 2007, or the one for earlier versions and Open Office? It's either not totally them up properly on the T&Cs / Groceries pages (do you see a total at the top of these pages?), or the formula on the Totals page for carrying them through is missing.

ljaneyr

I think I got the excel 2007 one. I had to copy the headings for feb and march groceries etc from january as they weren't there already so maybe that's it? There does appear to be formulas for them on the totals page but I have no idea if they are correct!

ljaneyr

Oh - I just downloaded the 2010 version which I couldn't work out yesterday! And I think of myself as fairly computer literate!

Thanks for the amazing spreadsheet!

cw18

Are you sorted now? If not, then please let me know (here or PM) and I'll try and get you running..... will be heading for an early night tonight (ie. pretty soon) and out at work most of tomorrow though (worked 1pm-8:30pm today and back at 7am tomorrow until 4pm-ish, so have to grab what sleep I can in between )

ljaneyr

Hi,
I have typed in all of this years spending into the 2010 version but unfortunately the T&C totals are still not showing on the totals page. The total shows in yellow at the top of every month and everything else seems fine but it's just the totals page that has gaps,

Thanks for your patience!

cw18

On each T&C page there should be a total in cell I1 - which has a formula reading =SUM(F2:F301) - which it sounds like you're seeing.

On the totals page the formula should read

In cell F25 (for January) : =IF('Jan T+C'!$I1=0,"",'Jan T+C'!$I1)
In cell G25 (for February) : =IF('Feb T+C'!$I1=0,"",'Feb T+C'!$I1)

and so on along row 25 (changing the month reference in both places in the forumla)) until

cell Q25 (for December) which reads =IF('Dec T+C'!$I1=0,"",'Dec T+C'!$I1)


All the month references are 3 letters - with the exceptions of June and July, where I used the full 4 letters.

ljaneyr

The formulas are all there for each month, the only difference is that there is an extra set of brackets around the first part:

=IF(('Jan Reg'!$C6=""),"",'Jan Reg'!$C6) but I changed this and it didn't make any difference

cw18

The cell referred to in the forumla shouldn't be $C6 - try changing it to $I1 and see if that helps

ETA: Just spotted it's using the wrong sheet too.... shouldn't be 'Jan Reg', should be 'Jan T+C'

ljaneyr

I'm so sorry, I'm very tired today and obviously clicked on the completely wrong cell!

It actually says =IF('Jan T+C'!$I1=0,"",'Jan T+C'!$I1) and then the next

one says =IF('Feb T+C'!$L1=0,"",'Feb T+C'!$L1) and so on.