How to use a formula on cells pulled from another sheet

Copper Contributor



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.


11 Replies


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




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


='Expenses Sheet'!D2

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?


Which exactly formula do you use? Like

=SUM(ExpensesA!D2, ExpensesB!D2)



or what?


I would then add ,NextsheetXX, Nextsheet2XX


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.

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.
best response confirmed by Hans Vogelaar (MVP)


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.

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!".


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

Thank you this worked for me! @Sergei Baklan