Forum Discussion

Copper Contributor

Jun 07, 2023

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 (us...

Formulas and Functions

SergeiBaklan
Jun 07, 2023
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.

esjay15

Copper Contributor

Jun 07, 2023

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?

SergeiBaklan

Diamond Contributor

Jun 07, 2023

esjay15

Which exactly formula do you use? Like

=SUM(ExpensesA!D2, ExpensesB!D2)

=SUM(Expenses!D2:D100)

or what?

esjay15
Copper Contributor
Jun 07, 2023
=SUM(MSSSG!B17)

I would then add ,NextsheetXX, Nextsheet2XX
- SergeiBaklan
  Diamond Contributor
  Jun 07, 2023
  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.
  - esjay15
    Copper Contributor
    Jun 07, 2023
    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.