How to use a formula on cells pulled from another sheet

[esjay15]

Hi, 

This is probably quite simple but i can't find the workaround. 

I am pulling in separate budgets in different spreadsheets - populating them into their own tab on an overall spreadsheet (using =cell).

Then I want the first tab to cumulate all the pots of money and total it up - but I can't use the sum(cell) on the tabs because it is not recognising the individual figures.

I also want it to be updated allthe time so i don't want to paste values.

Thanks, 

[#2]

@esjay15 

If you want to return the value of cell D2 on a sheet named Expenses, you can use the formula

=Expenses!D2

If the sheet name contains spaces or looks like a number, enclose it in single quotes:

='Expenses Sheet'!D2

[#3]

Hi Hans,

Thanks for your comment - I have got this part already.
It is when I am trying to use these values in a sum formula - the formula is returning 0 when I know the value is more than this.

Does that make sense?

[#4]

@esjay15 

Which exactly formula do you use? Like

=SUM(ExpensesA!D2, ExpensesB!D2)

or

=SUM(Expenses!D2:D100)

or what?

[#5]

=SUM(MSSSG!B17)

I would then add ,NextsheetXX, Nextsheet2XX

[#6]

@esjay15 

If =SUM(MSSSG!B17) returns zero that most probably means you have the text in MSSSG!B17, SUM() ignores everything but number. Could you please check what is returned by =ISNUMBER(MSSSG!B17) ?

Or =ISNUMBER(B17) within MSSSG sheet.

[#7]

Ooh yes - it says FALSE! So I guess it is not pulling through as a number - I wonder is it because I added the & "" to the end of the =sheet ? I wanted it to not show the blanks as 0s.

[#8 · Accepted solution]

@esjay15 

Yes, you have text which is not summed. Two variants

1) Use =SUM( VALUE(sheet!B17) )

2) In sheet use =IF( SUM(range), SUM(range), "") instead of =SUM(range) & ""

Actually you don't need SUM with referencing, =VALUE(sheet!B17) is enough. If you don't do any calculations with that, when =sheet!B17.

[#9]

In the cell where you want to apply the formula, start by typing the equal sign (=) to indicate that you're entering a formula. Then, navigate to the source calculadoraalicia sheet by typing the sheet name followed by an exclamation mark (!). For example, if the source sheet is named "Sheet2", you would type "Sheet2!".

[#10]

@AugustineChaim 

That's out of scope. The question is why =SUM(<such reference as you suggested>) returns 0.

[#11]

Thank you this worked for me! @Sergei Baklan 

[#12]

@esjay15 , you are welcome