Solved

Excel Database

Posted on 2014-01-29
3
124 Views
Last Modified: 2014-03-24
I don't know a lot about Excel databases and I don't have Access but can a database be set up in Excel that I can keep a running balance.  I work at children's home and the children have spending and savings that are all kept in one account at the bank but I keep balance on each child for there spending and savings.  Currently it has been set up in excel files, one child for each file.  I think we could maybe streamline with a database but not too sure about adding and subtracting from the balances.  If it would work it would need to easily add amounts and subtract amount from the two balances from a form..  If it will work I will attempt to setup but don't want to spend a lot of time if it will not work.   Any suggestions would be greatly appreciated.
Thanks,
Gloria
0
Comment
Question by:glophillips1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39819564
I really depends on exactly how you have your data set up. The SUMIF function could be used to keep a running sum for you. In the attached, columns A & B show the transactions, A has the names and B has the amounts. Comune E has the name of each member and F has the balances. The SUMIF function syntax is:

SUMIF(Range, Criteria,Sum Range)

Range - That's the range in which the names will appear
Criteria - That's the data (Name) you're looking for
Sum Range - The range in which the nummbers you want added to appear.

In column B, the values are positive for deposites and negative for withdrawals. If you use two columns, say column B for deposits and C for withdrawals, then the formula in column F would be:

=SUMIF($A$2:$A$100,E2,$B$2:$B100) - =SUMIF($A$2:$A$100,E2,$C$2:$C100)

This is just one way to do it.

Flyster
ExcelDatabase.xlsx
0
 

Author Comment

by:glophillips1
ID: 39821032
Flyster,
I'll be honest, it has been a while since I worked with Excel in the "advanced" mode.  I just didn't need to so I haven't progressed as "Excel" has progressed.  At one time in my career I designed a 31 file (needed 31 pages and at time Excel only had one page per file, does that date me or what, lol).  I want to have database where someone else could enter the data with a form.  Then data would be recalled using that form or another form.  I just need to know that it is possible and then I will get knowledge to complete.  I do appreciate you response.
Thanks,
Gloria
0
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 39821612
Unfortunately, Excel is not a "Database", it's a spreadsheet, and even though what you are asking for is not totally out of the realm of possibility, it is labor intensive. Access would be more suitable for this. I would suggest going to Office.Microsoft.com and look at the templates they have to offer.  They just might have something that meets your needs.

Microsoft Excel Templates
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question