Excel and data validation

I am using EXCEL 2007
I am working on a spread sheet for a friend of mine - one that she can use to keep her check register and balance her checkbook. I did a little research on pre-made ones but didn't see one that would do what I wanted

One things I want to do is provide a pick list that she can use to fill in the TO column. The list will contain places that she frequently writes check to. Will need the ability to type a value in the TO column that is not on the list.

I looked at some of the controls such as combo box etc. But I don't think these controls will work well on this spread sheet. I want to give her her the ability to add rows. From what little I tried - the controls will not work well in this situation.

I think I have found another way that will work and just wondered if anyone else had done anything similar. I can add a data validation - using a dropdown list. I enter the values for the picklist in another sheet of the same workbook- and give the range a name. I put a space as one of the values. On the validation - I select ignore blanks. The effect of this - is that - a value can be selected from the drop down box or any value can be typed directly into the box, whether in the list or not. Has anyone done anytning similar to this before or know a better way?

Another thing I want is a column where she can mark checks as cleared. I think I have this working by also usng the data validation - and hard coding the list. It is a little awkward but I don't know how else to do it. Doesn't seem to allow a blank to be typed in the the column but the delete key can be used to remove the *. In this case, I looked at using the checkbox control, but as stated above it doesn't seem to work well when lines are inserted. I am not using the dropdown list here - to me it seemed kind of awkward for this condition. Any comments on a better way here?

Thanks,
Fay

Seems to me Fay, that you would want to take a look at Access as being an application more suitable for what you want to do. I haven't used Access in years, but it is very powerful and gives you a lot of options for data validation routines. There are a number of examples on the Internet - just do a google search on "access and data validation." A lot of stuff will come up. There are a number of video tutorials at:

http://video.google.com/videoplay?docid=-7968609500356762448

You can do the same for Excel if you don't have Access - do a search on "excel and data validation" and you will get a list of links to explore. There are video tutorials at:

http://video.google.com/videosearch?hl=en&q=excel+and+data+validation&um=1&ie=UTF-8&sa=X&oi=video_result_group&resnum=10&ct=title#

Hi Repa,

I am not sure whether or not she has access, but I don't have it, and have never used it. I started out just to do something "quick and dirty" for her. But when I got into it I wanted to make it a little better. At this point Access is not an option. I could probably write her something in FOXPRO. I thought about that, but I really don't have the time/energy to get into it that deep. I think what I have will work - just wondered if anyone was familiar with a better way - or knew of anything that might "break" what I am doing. I have already encountered a few kinks that I have worked out.

I have will not be all that sophisticated and if she decided she doesn't want to use it, that it will not hurt my feelings.

Thanks for the links.

Fay

Hi Fay,


Don't you think it would be easier if Quicken was used to check and balance her checkbook/finances?



Erik

HI Erik,

She doesn't have quicken and neither do I.

I got something to sort of work in EXCEL, but it was a bit more complex to use than she wanted.

She doesn't do a lot on the computer except for e-mail, messenger and looking for stuff on the Internet.

I could do something in fox-pro for her but she doesn't have fox pro and I don't have a distributor license.

If I can ever get her interested I may try to come up with something simpler for her in EXCEL . She uses a calculator but if she messes up then has to re-enter everything. I should have probably just set up something for her to enter the amounts and calc the balance and forget the actual balancing. I think I even found some templates that do that part - so all I would have to do would be to show her how to get one and use it. But right now she doesn't seem interested in learning to use EXCEL


Thanks,
Fay

Hi Fay,


I can understand why she wouldn't want to learn EXCEL and I'm not crazy about EXCEL myself.

That brings me to mind that I been wanting to create a Air Application that can calculate any formulas and input values for the desktop. I will keep in mind that it oughta have past calculations once I get the Actionscript code done correctly.


Erik