SOLVED

# How to use a formula on cells pulled from another sheet

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,

11 Replies

# Re: How to use a formula on cells pulled from another sheet

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

# Re: How to use a formula on cells pulled from another sheet

Hi Hans,

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?

# Re: How to use a formula on cells pulled from another sheet

Which exactly formula do you use? Like

=SUM(ExpensesA!D2, ExpensesB!D2)

or

=SUM(Expenses!D2:D100)

or what?

# Re: How to use a formula on cells pulled from another sheet

=SUM(MSSSG!B17)

I would then add ,NextsheetXX, Nextsheet2XX

# Re: How to use a formula on cells pulled from another sheet

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.

# Re: How to use a formula on cells pulled from another 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)
Solution

# Re: How to use a formula on cells pulled from another sheet

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.

# Re: How to use a formula on cells pulled from another sheet

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

# Re: How to use a formula on cells pulled from another sheet

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

# Re: How to use a formula on cells pulled from another sheet

Thank you this worked for me! @Sergei Baklan

# Re: How to use a formula on cells pulled from another sheet

@esjay15 , you are welcome