Monday, July 8, 2013

Google Spreadsheet referencing from one sheet to another

Question:

( by dollfacedx )

I want to import one single cell - NOT a range of cells
How do I link the value of one single cell from one spreadsheet to the next so that when I type a figure in the cell on one page of a spreadsheet, that same value is automatically added to the cell on the second page of the same spreadsheet?
Thank you.


Solution:


If you want to copy a value from one sheet to another, and have it update automatically, try the following formulas:

='sheet2 name'!A1

for a single cell.

Or

=arrayformula('sheet2 name'!A3:C4)

for a range of cells.

Or

=arrayformula('sheet2 name'!A3:C)

for whole columns.

Or

=arrayformula('sheet2 name'!A3:4)

for whole rows.

Put any one of the above formula (as per your requirement) to the first Sheet.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

7 comments:

  1. I'm trying to do the same function as dollfacedx but it's not working. I have a spreadsheet with 12 weekly tabs and I need to add a cells total from week 1 to a cells total on week 2. So it will continue adding my Totals cell from week to week.

    The name of the sheet is "WK 1 9/25-10/2" and "WK 2 10/3-10/9" and the cells I'm trying to add together from wk 1 and wk 2 is cell J15. Can you please give me the exact formula to use to complete this function?

    Thank you!
    DN

    ReplyDelete
    Replies
    1. Try the following formula:
      ='WK 1 9/25-10/2'!J15 + 'WK 2 10/3-10/9'!J15

      Delete
  2. When I entered your suggested formula I got a circular dependency detected error. I think maybe I wasn't clear enough, I'm adding a row of numbers (C15:I15) that have a total cell (J15) on 'WK 1'. I want that (J15) cell to add to the combined total of (C15:I15) on 'WK 2' and show the combined total of both weeks in cell J15 on WK 2.

    ReplyDelete
    Replies
    1. Okay, I got it now...

      Try the following formula:
      =sum('WK 1 9/25-10/2'!C15:I15) + sum('WK 2 10/3-10/9'!C15:I15)

      Or:
      ='WK 1 9/25-10/2'!J15 + sum('WK 2 10/3-10/9'!C15:I15)

      Or:
      =sum( 'WK 1 9/25-10/2'!C15:I15 ; 'WK 2 10/3-10/9'!C15:I15 )

      Or:
      =sum( 'WK 1 9/25-10/2'!J15 ; 'WK 2 10/3-10/9'!C15:I15 )

      Delete
    2. And as you are using this formula on Sheet "'WK 2 10/3-10/9'", then you can try any of the following formulas:

      =sum('WK 1 9/25-10/2'!C15:I15) + sum(C15:I15)

      Or:
      ='WK 1 9/25-10/2'!J15 + sum(C15:I15)

      Or:
      =sum( 'WK 1 9/25-10/2'!C15:I15 ; C15:I15 )

      Or:
      =sum( 'WK 1 9/25-10/2'!J15 ; C15:I15 )

      Delete
  3. Perfect, it worked!! Thank you so much!!

    ReplyDelete
  4. Hi,
    I have a different issue. I want to reference sheet cell value across page.
    When i type drive.google.com at url i find two link and both link has multiple sheets (as tab). is it possible to reference another page - sheet - cell value to different page - sheet - cell.
    Thanks
    Santosh Mishra

    ReplyDelete