A friendly forum to get help and support
 
PortalPortal  HomeHome  GalleryGallery  FAQFAQ  SearchSearch  RegisterRegister  Log in  

Share | 
 

 Excel and data validation

View previous topic View next topic Go down 
AuthorMessage
fay47
Royal Geek
Royal Geek
avatar

Number of posts : 1480
Registration date : 2008-10-17
Mood : none

PostSubject: Excel and data validation   1/25/2009, 16:12

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
Back to top Go down
View user profile
Repa
Site Administrator
Site Administrator
avatar

Number of posts : 2376
Location : North Carolina
Humor : Age: Older than Dirt!
Registration date : 2008-09-19

PostSubject: Re: Excel and data validation   1/25/2009, 22:19

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#

_________________
Repa
Back to top Go down
View user profile
fay47
Royal Geek
Royal Geek
avatar

Number of posts : 1480
Registration date : 2008-10-17
Mood : none

PostSubject: Re: Excel and data validation   1/25/2009, 22:38

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
Back to top Go down
View user profile
treate
Geek
Geek
avatar

Number of posts : 13
Age : 40
Location : TSTC-Waco
Humor : Wicked and Raw with pure Sarcasm *snickers*
Registration date : 2009-08-13

PostSubject: Re: Excel and data validation   8/15/2009, 16:18

Hi Fay,


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



Erik
Back to top Go down
View user profile
fay47
Royal Geek
Royal Geek
avatar

Number of posts : 1480
Registration date : 2008-10-17
Mood : none

PostSubject: Re: Excel and data validation   8/15/2009, 16:37

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
Back to top Go down
View user profile
treate
Geek
Geek
avatar

Number of posts : 13
Age : 40
Location : TSTC-Waco
Humor : Wicked and Raw with pure Sarcasm *snickers*
Registration date : 2009-08-13

PostSubject: Re: Excel and data validation   8/15/2009, 18:03

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
Back to top Go down
View user profile
Sponsored content




PostSubject: Re: Excel and data validation   

Back to top Go down
 
Excel and data validation
View previous topic View next topic Back to top 
Page 1 of 1
 Similar topics
-
» ST70 Voltage Chart
» Bogen Amp Data
» Kita will be coming to Excel CS at JCube on 1st October 2011
» Dr Who 50th Celebration Event (London Excel) November 22-24
» SG Showdown Qualifer 6 Aug 2011 (Excel CS)

Permissions in this forum:You cannot reply to topics in this forum
Geeks, Geeks and More Geeks :: Other Help Forums :: Microsoft Office-
Jump to: