Solved

updating one cell value using values from another worksheet

Posted on 2010-11-19
7
373 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

920 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

12 Experts available now in Live!

Get 1:1 Help Now