Forum Home
Press F1
Thread ID: 2	1998-09-01 00:11:00	Birthdays in Microsoft Works	Guest (0)	Press F1

Post ID	Timestamp	Content	User
64	2002-08-28 06:48:00	Susan, a "flag" is just a "1" or a "0" as an indicator for you to apply conditional formatting-or not. i.e.if the "flag" cell is 1, you want to put conditional formatting on the cell, but if its 0, no formatting. Its not a special cell, its just a 1 or 0 as a factor to use in the next part. Nobody has covered conditional formatting formula as we all thought you knew it (or looked it up in "help") Format - conditional format If Cell is -- Greater Than --- 0 select format button and choose format. Now the cell you are conditionally formatting can be the "flag" cell, and if its not 0 it will be coloured as you have chosen. If you want several dates, they should be off the screen, on a line of their own each, and you should sum them up to a total cell (say c24), and then your "flag" cell is just =c24. If you were testing for 5 birthdays and they all fell within the date window, the total would be 5 for that day, and the flag cell would read 5, which is also more that 0, so it still works.	godfather (25)
65	2002-08-28 07:10:00	What's your email Susan? We can send your a spreadsheet each and you can pick whichever you like? I'm still aiming to claim that chocolate fish :-) If you don't want to publish your email here send it to my ICQ 9596596 Capt Jimbo	Capt Jimbo (17)
66	2002-08-28 07:23:00	Was being a bit silly about the flags godfather, I did think they might be just sort of markers . I can get the flags to change colour no problem but was trying to get the birthdates to change colour as well and I can't . Am I right in surmising that your idea is to look at the flags and when they change colour (or if there is a 1 instead of a 0) then I know there's a birthday that week? Hmmm, it's not quite as sophisticated as I hoped but it will do the job . But wait!! This is really weird but I've just tried changing "today's date" to various random samples and the flags now don't work! Gee this is complicated . . . Capt'n my email is topazz10 at hotmail dot com if you'd like to have another go . Thanks!	Susan B (19)
67	2002-08-28 07:32:00	As the birthdates are already numbers, its a bit complicated to get them to change (possible but not nice), but you could have a flag cell next to them and have that change... Wasn't sure what you wanted, but sounds like you have the basics mastered. Must say I have never used conditional formatting myself. Hard enough managing a 54,000 row spreadsheet today, have to make a cup of coffee while it recalculates!	godfather (25)
68	2002-08-28 11:24:00	54,000 rows? Man, that would be a nightmare to manage. What if there was an error in one formula? :O I can't imagine what you'd have in such a spreadsheet, but I do know I wouldn't like to be in charge of it...	Susan B (19)
69	2002-08-28 12:08:00	> 54,000 rows? Man, that would be a nightmare to > manage. What if there was an error in one formula? > :O > > I can't imagine what you'd have in such a > spreadsheet, but I do know I wouldn't like to be in > charge of it... LOL..... go on Susan, who are you kidding. All Women want to be in charge!	Baldy (26)
70	2002-08-28 12:23:00	You just had to bump up the second thread on record here didn't you Susan.	-=JM=- (16)
71	2002-08-29 02:18:00	> You just had to bump up the second thread on record > here didn't you Susan. :8} Where did that crystal ball come from JM? Come on, reveal your sources... I got the surprise of my life when that post came up after fiddling - it was just what I have been wanting to know in the past couple of weeks. Why reinvent the wheel when I already had one? :D Baldy: women don't like to be in charge, they are in charge. ;-)	Susan B (19)
72	2002-08-29 10:29:00	Hi Susan did you work this out? In case you didnt taking Godfathers formula (so all cf to him) do the following ... Highlight the cells where you have the birthdays ( for example you can highlight all column a if this is where they are) then select conditional formatting. Choose the IsFormula option and enter the following formula... =IF(ABS(A1-TODAY())<8,1,0) Now select the font and colour red and press OK and your away. In this example I have put reference to cell a1 as the date even though I may have had dates in cell a5 as well. Because you selected all of column A before entering the formula in conditional formatting, Excel is correctly assuming you want the conditional formula based on reference a1, a2 , a3 etc. I have changed Godfathers formula slightly to do away with the flags. Today() means return todays date. Note that you will get #Name? errors or alike when dealing with dates unless you have correct regional settings - should be English(new Zealand) cheers Parry	parry (27)
73	2002-08-29 10:56:00	Hi Parry, no, the virtual chocolate fish is still up for grabs as I didn't get it sorted . Thanks for offering your version, I tried it and it works perfectly as long as the person was born this year . :D If I put in any other year it stays the default . Obviously, like godfather says, I need to convert the year somehow but that's way over my head . Thanks anyway . :-)	Susan B (19)
1 2 3 4