Excel Database

Posted on 2014-01-29
Medium Priority
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.
Question by:glophillips1
  • 2
LVL 22

Expert Comment

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.


Author Comment

ID: 39821032
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.
LVL 22

Accepted Solution

Flyster earned 2000 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

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

588 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