Forum Discussion
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,
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.
11 Replies
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 https://calculadoraalicia.online/ 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.
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
- 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
=SUM(Expenses!D2:D100)
or what?
