Solved

updating one cell value using values from another worksheet

Posted on 2010-11-19
7
374 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
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…

815 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