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
Solved

Excel Database

Posted on 2014-01-29
3
121 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
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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