Solved

updating one cell value using values from another worksheet

Posted on 2010-11-19
7
377 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
[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
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

734 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