[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Excel Database

Posted on 2014-01-29
Medium Priority
127 Views
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
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
• 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.

Flyster
ExcelDatabase.xlsx
0

Author Comment

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

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
0

## Featured Post

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 …
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month12 days, 17 hours left to enroll