• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

updating one cell value using values from another worksheet

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
vthunder70
Asked:
vthunder70
  • 3
  • 2
  • 2
1 Solution
 
gbanikCommented:
vthunder70 ... seems straight forward. please post a sample file with the sample sheets as well as a sample desired sheet.
0
 
gbanikCommented:
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
 
TommySzalapskiCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TommySzalapskiCommented:
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
 
gbanikCommented:
Thanks Tommy... didnt see that. U must have identified it immediately as u had the accepted solution in the other one :)
0
 
vthunder70Author Commented:
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
 
vthunder70Author Commented:
sorry I though I had already accepted this answer... oops!

thanks it works great.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now