linking spreadsheets - different format

[capri]

Hi,

I have a couple of large multi paged spreadsheets. One contains data for our dashboard. The other is a testing spreadsheet to verify that the data was loaded successfully to the dashboard.

Previously they have been copying the data from the main spreadsheet (Workbook A) into the testing spreadsheet (Workbook B). I suggested linking the two so that each month we would only have to copy the links for the current month rather than the entire data set. In the process of setting up the links I have noticed some strange behaviour and wonder if anyone can tell me what causes it.

Some of the columns of data in Workbook A contain blanks. There is no data and no zero's are entered. On the first several links I created (from various worksheets), the link came through as a blank. On some of the later worksheets the link came through as a zero. Some later worksheets came through as blanks. There is no commonality that I can find as to why some link properly as blanks and others link as zero's. I checked the formatting on all cells involved and they are all formatted as numbers with no decimal places.

I haven't got far enough along to determine whether the blank versus zero will make a difference to my testing, but can see in other situations where it might cause problems. I would like to understand what causes this to occur so that in the event there are problems I can decide how to deal with the situation.

Thanks for any help

capri

[HansV]

Hello capri,

Welcome to Eileen's Lounge!

A link to a blank cell always returns 0, but it is possible to suppress the display of zeroes by clearing the Zeros check box in the View tab of Tools | Options... in Excel 97-2003 or by clearing the "Show a zero in cells that have zero value" check box under "Display options for this worksheet" in the Advanced category of (Excel) Options in Excel 2007-2010. The screenshot below is for Excel 2003:

x107.png

This setting is specific to each worksheet, so it can be "on" for one sheet and "off" for another.

A (cumbersome) workaround is to modify the link formulas:

=IF(Sheet1!C37="","",Sheet1!C37)

This will return a blank if the original cell is blank, regardless of the setting of the check box mentioned above.

[capri]

Thanks Hans,

It's always a bit difficult working with spreadsheets created by others. Now that I know what it causing the problem I can fix it. It just seems strange that whoever created the spreadsheet would not have been consistent.

capri