Solved

updating one cell value using values from another worksheet

Posted on 2010-11-19
7
371 Views
Last Modified: 2012-06-27
Hi Experts,

I'm creating a family budget with google spread sheets.

I've gotten helped with a big chunk and now I believe I'm on the final stage.

I have a spread sheet where I collect data under the following headers:
Timestamp   |      Description  |      Purchase Type    |      Expense    |      Deposite     |      Balance

My second sheet is from a Budget template that I found in google and is got all sorts of options for expenses types.

My idea is to bring values from the first google sheet into specific cells in my second sheet(Budget sheet). Example:

The budget sheet has a something like this: Fun Cash:   (cell for projected)    |  (cell for actual). Inside the cell for actual I would like values from the first spread sheet of "Purchase Type=Fun Cash" to add themselves inside that cell, so if I add 5 entries of type "fun cash" of 20 then my "Fun Cash actual column in my Budget sheet" would update to 100.

After a lot of googling I've only figure it out how to bring 1 specif cell from my first sheet into my Budget sheet like this

=importrange("MY_SHEET_ID", "CELL_COL_AND_NUMBER") ... but obviously this not what I want.

Is what I'm after possible?

Thanks,
0
Comment
Question by:vthunder70
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34179188
vthunder70 ... seems straight forward. please post a sample file with the sample sheets as well as a sample desired sheet.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34179466
Didnt wait for you to post your sheet... Here is an example
There are many ways your can accomplish them... few being SUMIF, SUMPRODUCT or through SUM (Array formula)
See attached file.
Expenses.xlsx
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34181395
Are they in different sheets on the same spreadsheet or different spreadsheets entirely? I recommend placing them both on the same spreadsheet in different sheets.
See the sample
https://spreadsheets.google.com/ccc?key=0Ap6DDF7eIq58dHZoT19acEtxM25kMjQ4UDRWRV9GT1E&hl=en&authkey=CJLN0K0B
I put the transactions on one sheet and the budget on the other.
Click on C2 in the budget sheet to see the sumif formula at work.
On the transactions sheet, right-click on column C and hit 'Validate Data...' to see another cool trick. This way if you misspell a type,it will warn you so you don't miss numbers in your total.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34181401
Since I won't leave that spreadsheet up forever, for the benefit of anyone who might stumble across this after it's closed the formula is
=arrayformula(if(A2:A<>"",sumif(Transactions!C:C,A2:A,Transactions!D:D),""))
Where A is the list of transaction types on the budget sheet and C and D are the type and total on the transactions sheet.

gbanik, your function is good but he's using Google Docs. He just posted this in the Excel zone because it's the closest we have.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34181451
Thanks Tommy... didnt see that. U must have identified it immediately as u had the accepted solution in the other one :)
0
 

Author Comment

by:vthunder70
ID: 34188160
Hi, I"m sorry

My honey due list this weekend was waaaaaaay long and I just got a chance to look at this. I'll reply back asap
0
 

Author Closing Comment

by:vthunder70
ID: 34273296
sorry I though I had already accepted this answer... oops!

thanks it works great.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now