OMG Mental Block!

[Dumbfounded]

Can someone please help me with this oh so simple question - I have done it before but so long ago and now can't remember how to do it.

I am in the process of streamlining month end for my colleague to get rid of all the duplication that currently happens. I want one Excel sheet that they fill in and this links to all the other sheets - this isn't a problem as the majority of it is figures so that's just a sum function. However there is a section where they have to put comments and I need that text to transfer across to another sheet and I just cannot remember how to get it to do that and all I am getting is '0' return. I have looked at the other functions but nothing comes to mind (or seems to work) and I've changed my cell settings to text on both of the sheets.

Please help

 

[techsupport1]

My initial guess is that it is pointing to the incorrect cell...could you send some sample files?

 

[Dumbfounded]

I have uploaded two files - I am trying to use the information entered into the month end master to populate the kpi sample which it does until it comes to text. I have had to change the format to general otherwise it just shows the formula.

 

[NBVC]

You don't need even the SUM() function to return the numbers....

In the comments box (cell A18), try:

='[Month End Sample.xls]Depot'!$C$10

If the comment is empty you will get a 0, so you can use an IF function to return a blank when empty:

=IF('[Month End Sample.xls]Depot'!$C$10="","",'[Month End Sample.xls]Depot'!$C$10)

or you can use the original formula and format the cell as Custom, using format as: 0;-0;;@

for the numbers, you do the same. No need for SUM() unless you are summing multiple cells.

for example to get value from C3 in the Month End Sample workbook.. you need just:

='[Month End Sample.xls]Depot'!$C$3

if you don't want a 0 when the cell is really blank, you can use the IF as above, or you can Custom format as Above...

P.S. I am not sure about rule enforcement, but your title should be more of a brief description of the problem. This improves search capabilities for future users seeking similar solutions...

 

[Dumbfounded]

thank you so much NBVC - I knew it was something simple and that's worked a treat!