cw18's spreadsheet Q&A thread

I thought it might be handy to have somewhere central for all the queries, so that a] Cheryl can spot and answer them and b] so everyone else knows where to look for the answers instead of ferreting through the 2010 budget challenge thread.

I'll go and tell Cheryl now;)
If you see me on here - shout at me to get off and go and get something useful done!! :D
«134

Replies

  • edited 11 January 2010 at 12:06AM
    cw18cw18 Forumite
    8.4K Posts
    Part of the Furniture 1,000 Posts Name Dropper Photogenic
    Forumite
    edited 11 January 2010 at 12:06AM
    There have been a couple of 'bugs' turn up in the spreadsheet - nothing MAJOR, but possibly things that will annoy folks a little ;)
    ................................................................................................

    Bug 1 - information copied from the 2010 Challenge Intro thread - now in Post #8 of this one as well :)
    Not relevant for anyone who got their copy of the spreadsheet from 29th Dec 2009 onwards
    ................................................................................................


    Bug 2 - instructions for trying to fix this yourself is in Post #7

    Anyone who got hold of a copy of the spreadsheet before 9th January may notice some categories flagging up as having an overspend when they haven't (and vice versa) on the Totals page. This is indicated by the figure in the 'spent' column turning red.

    This switch of colour is controlled by 'Conditional Formatting', which people who are confident with Excel may be able to fix themselves - but if anyone hits this and isn't confident of repairing it "in-house", please feel free to contact me re: sending it back for repair (ie. I'll let you know as soon as I have some free time, you can email me your spreadsheet, I'll fix it, then I'll send it right back to you). Best done 'by arrangement' so you're not without it for too long if I get several back on the same day :)
    Cheryl
    Grocery Budget : January £124. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirreled away = £34.47
    Grocery Budget : 2022 £1560. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirrelled away = £34.47
  • jtb2412jtb2412 Forumite
    1.8K Posts
    Cheryl, I've got the overspend on one column. Is there any chance you could pop instructions on here? I can give it a go - I think I might be able to do it lol
    :jWeight loss to date 1st 11.5lb :j
  • cw18cw18 Forumite
    8.4K Posts
    Part of the Furniture 1,000 Posts Name Dropper Photogenic
    Forumite
    oooooerrrrrr!

    Conditional Formatting is something younger DS taught me about 8 months ago, so this could be fun :rotfl:

    About to finish making tea, but will try and update the my earlier post with detailed instruction after that :)

    Only problem is I've only ever used it in Excel 2007, so the screen shots may not be right for earlier versions (and I'm not even sure how to do it in Open Office - perhaps I may have to look at downloading that as a number of people seem to be using it!)
    Cheryl
    Grocery Budget : January £124. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirreled away = £34.47
    Grocery Budget : 2022 £1560. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirrelled away = £34.47
  • jtb2412jtb2412 Forumite
    1.8K Posts
    Hehehe ..... no hurry Cheryl, the words conditonal formatting don't excite me for some reason :rolleyes: :rotfl:. I'm glad we've all got you :beer:
    :jWeight loss to date 1st 11.5lb :j
  • JayJay14JayJay14 Forumite
    1.9K Posts
    Thanks Cheryl - I've sorted mine.
  • cw18cw18 Forumite
    8.4K Posts
    Part of the Furniture 1,000 Posts Name Dropper Photogenic
    Forumite
    Correcting the formatting on the cells in the ‘Spent’ column on the ‘Totals’ page

    These instructions are written for Excel 2007, as that’s what I have. If someone with an earlier version can confirm whether these are accurate for earlier versions (or Open Office) or not, I’d be very interested in the info.

    (But don’t worry if you can’t follow these instructions – just send me back your completed spreadsheet and I’ll do the fixes for you. Best to check with me if I’m free first though, as I’d hate to leave you without a spreadsheet for updating for longer than absolutely necessary!)

    Open your spreadsheet, and go to the ‘Totals’ page
    Make sure you’re on the ‘Home’ tab on the menu bar

    HomeTab.jpg

    Sit yourself on cell D2 (ie. The first cell under the ‘Spent’ heading)
    Click on the down arrow next to the title for the ‘Conditional Formatting’ icon (in the ‘Styles’ section of the tool bar

    CFIcon.jpg

    Then from the drop down menu select the bottom option – ‘Manage Rules’

    This may or may not include rules in the pop-up box.
    If it looks like this.....

    InsertedRule.jpg

    Then you can click on OK and move down to the next row

    If there’s anything in there that doesn’t look like the above, then click on the line in question and then ‘Delete Rule’

    If you need (and want) to put the rule in, then do the following

    Click on ‘New Rule’, and a new window will appear

    NewRule.jpg

    Click on the second line down – ‘Format only cells that contain’

    You’re now looking at the window with some changes in the bottom section

    ThatContain.jpg

    In the bottom section, click on the down arrow on the second box
    Using the drop down menu, change ‘between’ to ‘greater than’

    The bottom section now changes again to

    GreaterThan.jpg

    Now click in the empty box to the right, and then click back onto your spreadsheet in cell C2
    It should complete the empty box with =$C$2

    Now click on the ‘Format’ button, at which point you’ll get yet another window

    Format.jpg

    Make sure you’re on the ‘Font’ tab at the top (mine defaults to this, but yours may not if it’s the first time you’ve used this)

    Change the ‘Color’ from Automatic to Red, at which point the text in the Preview box will change

    Red.jpg

    Click OK on this window

    You’ll be returned to the window on which you clicked on ‘Format’, but the preview on here will now be in red as well
    Click on OK again

    The Rules window will now reappear, and will look like this

    InsertedRule.jpg

    Click on OK again, and you’ll be back in the main spreadsheet


    Repeat this for each cell in column D (my personal choice is to include the Totals row), but make sure that the two cell references refer to cells on the row you’re working on – referring to one or more cells on other rows (or multiple or total absent rules) are what are causing the display problems, and seem to have come about due to me doing too many Copy/Paste actions in that column !!!

    InsertedRule-CellReferences.jpg
    Cheryl
    Grocery Budget : January £124. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirreled away = £34.47
    Grocery Budget : 2022 £1560. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirrelled away = £34.47
  • cw18cw18 Forumite
    8.4K Posts
    Part of the Furniture 1,000 Posts Name Dropper Photogenic
    Forumite
    *** Possible problem with spreadsheet ***
    (rectified in the blank copy as of 29th December 2009)

    I had a query yesterday, where one person was getting an "inconsistent formula" error message. This happened on the 'Totals Page', and was coming up on cell D24 (ie. Groceries row, Spent column)

    I wasn't seeing the error, so I suspect it will only be happening for people using the backwards compatible version (the attachment file name will contain '97' rather than '2007') on a version of Excel below 2007 - though the inconsistency is actually in both versions I've sent out.... oops!


    The formula in that cell does (for some reason) differ slightly to those in the rest of that column, but I'm surprised it's causing a problem......

    If you look closely it says

    =IF(SUM(F24:Q24)=0,"",(SUM(F24:Q24)))

    But to match the others in the column it should be

    =IF(SUM(F24:Q24)=0,"",SUM(F24:Q24))

    ie. there's an extra set of brackets around the latter part


    Doing a copy/paste from D23 should fix it just fine (I hope) - or you can edit the formula in the cell and remove the extra brackets.
    Cheryl
    Grocery Budget : January £124. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirreled away = £34.47
    Grocery Budget : 2022 £1560. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirrelled away = £34.47
  • jtb2412jtb2412 Forumite
    1.8K Posts
    Mines now fixed, thanks so much Cheryl. You're instructions were great ! xxx
    :jWeight loss to date 1st 11.5lb :j
  • cw18cw18 Forumite
    8.4K Posts
    Part of the Furniture 1,000 Posts Name Dropper Photogenic
    Forumite
    Thanks for letting me know you could follow them :)

    I used to have to write User Guides as part of my job in IT -- and those had to be written in baby steps with screen shots, so I just 'wound the clock back' 11 years to try and remember the level we did them at. The theory was that, given the document, anyone could enter data into the system without needing someone sat alongside them for hours of training - and some of our Key Users affectionately called them our "Idiot Guides" because of the level we had to write them at (which would probably have seen even more screen shots than I included above!!!) :rolleyes2
    Cheryl
    Grocery Budget : January £124. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirreled away = £34.47
    Grocery Budget : 2022 £1560. Spent £21.53 in shops + £8.84 from stocks + £4.10 discounts squirrelled away = £34.47
  • edited 20 January 2010 at 10:02PM
    rozeepozeerozeepozee Forumite
    2K Posts
    Part of the Furniture 1,000 Posts Combo Breaker
    edited 20 January 2010 at 10:02PM
    Hi Cheryl, I'm having a bit of trouble again :o

    As you know I've used your wonderful spreadsheet for a variety of things, not just the challenge but my fixed expenses also but because I did this early in the year when I was full of motivation, I think I must have the earlier version of the spread sheets with some "glitches" OR (very possibly) I've done something when setting them up to mess them up! I didn't want to bother you so though I'd just download a new version from Google docs and start again as we're only in January but I can't seem to save the downloaded version to my own computer. Do you know what I'm doing wrong please? Is it possible to save it on my computer or does it have to stay in Google docs? Would it be possible/easier for you to emai lme the new version please?

    I'm afraid post #7 just alarms me - I don;t think my computer skills are anywhere up to doing it...
This discussion has been closed.
Latest News and Guides