= formula across 2 worksheets results in "0" in blank cells

**bravobravoau** · 09-02-2010, 06:51 PM

Hello - like most newbies, I apologise for the simple question I am surely about to ask. Your collective expert assistance is very much appreciated!!

I have an Excel 2007 workbook, containing two worksheets. The first worksheet basically contains the information, and the second is a cover page (for when printed). Importantly, the workbook is shared for several users - I have tried to minimise what can be recorded to ensure consistency.

'Sheet 1' is formatted as a series of small tables (normally around 10 in total), each of which has five essential cells which I need to capture in 'Sheet 2'. Three of the five essential cells are a list (drop down box) and the other two are free text. The actual sheet name does have a [space] in the name, hence 'Sheet 1'.

For the purpose of the cover page ('Sheet 2'), ideally I would like the entries to be recorded in the same order as 'Sheet 1'.

I have attached a dummied up version of the workbook - hopefully this assists.

While I am happy to manually enter the "='Sheet 1'!A1" formula, as the location of data from 'Sheet 1' is separated by a number of other cells (e.g. A3, A13, A23) BUT the cells in the table in 'Sheet 1' that are blank, appear in 'Sheet 2' as "0". Can I stop this by using a different formula (preferably not a macro if that is applicable)??

The actual spreadheet has different spacing between useful cells (e.g. A7, A25, A44), but I'm pretty sure thant I can work that part out.

Thank you in advance for your assistance.

**TMS** · 09-02-2010, 06:57 PM

I ususally take a simplistic approach:

Please Login or Register  to view this content.

Regards

**JBeaucaire** · 09-02-2010, 07:02 PM

Put this formula in Sheet2 cell A4, then copy down and across.

=IF(INDEX(Sheet1!A:A, ROW(A1)*10-7)=0, "", INDEX(Sheet1!A:A, ROW(A1)*10-7))

Personally, I would just use this:

=INDEX(Sheet1!A:A, ROW(A1)*10-7)

...then click on Tools > Options > View > [ ] Zero values and uncheck that. This will suppress displaying zeros without the lengthy formula, and it only applies to this one sheet, it's not a global setting.

**daddylonglegs** · 09-02-2010, 07:05 PM

One way is to append &"" to the formula, i.e.

=Sheet1!A3&""

That will get rid of zeroes but also converts numeric values to text values - the latter won't be a problem if you just want the data for display purposes.......