NEW BLOG. Featuring tips and pics from pet owners of the MSE Forum, we present to you Homemade pet toy ideas. Take a look
cw18's spreadsheet Q&A thread
DdraigGoch
Forumite 730 Posts
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;)
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!! 
0
This discussion has been closed.
Latest MSE News and Guides
Replies
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
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!)
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
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
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.....
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
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
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
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
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
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
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 !!!
(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.
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
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...