Linked cells display a zero (Excel 2003)

Topic

New Reply

I have a workbook with two worksheets. On Sheet2 I want to display the values of some cells on Sheet1, so I type the formula =Sheet1!A1. I now expand the formula by dragging into adjacent cells so that the values of contiguous cells on Sheet1 will appear.
The problem is that some of the cells on Sheet1 are empty, so on Sheet2 they display as 0 (zero). This is the case even though the cell format is General, not Number (even Text has the same result).
How do I get rid of the 0’s from my cells without having to go in and delete them manually?

Another question: If I expand Sheet1 by adding rows and/or columns, is there any way I can have an automatic, corresponding update occur on Sheet2? Or at least prevent overwriting of the cells on Sheet2?

Roger Shuttleworth
London, Canada

Reply | Quote

Replies

You can use formulas like this:

=IF(ISBLANK(Sheet1!A1),””,Sheet1!A1)

Reply | Quote

You can use formulas like this:

=IF(ISBLANK(Sheet1!A1),””,Sheet1!A1)

Reply | Quote

<>

Yes, use this formula instead (starting from cell A1 on sheet2):

=INDIRECT(“‘Sheet1!'” & CELL(“Address”,A1))

Reply | Quote

Thanks to you, Jan, and Hans too.

Both the answers you gave are good, but is there any way I can combine the formulas so that:

The cell contents on Sheet2 automatically update if columns and rows are added to Sheet1, and
Cells on Sheet2 that are linked to blank cells on Sheet1 are displayed as blank?

Sorry if I’m asking for the moon! Your help is appreciated.

Roger

Reply | Quote

Like this?

=IF(ISBLANK(INDIRECT(“‘Sheet1!'” & CELL(“Address”,A1))),””,INDIRECT(“‘Sheet1!'” & CELL(“Address”,A1)))

Reply | Quote

Hello Hans

Thankyou so much. This works fine, though I had to remove a few odd spaces that crept in during copy and paste.

Roger

Reply | Quote

Hello Hans

Thankyou so much. This works fine, though I had to remove a few odd spaces that crept in during copy and paste.

Roger

Reply | Quote

Like this?

=IF(ISBLANK(INDIRECT(“‘Sheet1!'” & CELL(“Address”,A1))),””,INDIRECT(“‘Sheet1!'” & CELL(“Address”,A1)))

Reply | Quote

Thanks to you, Jan, and Hans too.

Both the answers you gave are good, but is there any way I can combine the formulas so that:

The cell contents on Sheet2 automatically update if columns and rows are added to Sheet1, and
Cells on Sheet2 that are linked to blank cells on Sheet1 are displayed as blank?

Sorry if I’m asking for the moon! Your help is appreciated.

Roger

Reply | Quote

[indent]

---

=INDIRECT(“‘Sheet1!'”&CELL(“Address”,A1))

---

[/indent]

Just a question regarding this formula. I tried it this way, but received #REF. However, if I removed the single quotes (‘) around “Sheet1” then it worked. Is there are reason the single quote?

Reply | Quote

The second single quote should be before the exclamation.

The single quotes are not needed (but are not a problem) if the sheet name has no spaces. If the sheet name has any spaces the single quotes are required to mark the full name (excel will get “confused” by the space otherwise).

Steve

Reply | Quote

The second single quote should be before the exclamation.

The single quotes are not needed (but are not a problem) if the sheet name has no spaces. If the sheet name has any spaces the single quotes are required to mark the full name (excel will get “confused” by the space otherwise).

Steve

Reply | Quote

[indent]

---

=INDIRECT(“‘Sheet1!'”&CELL(“Address”,A1))

---

[/indent]

Just a question regarding this formula. I tried it this way, but received #REF. However, if I removed the single quotes (‘) around “Sheet1” then it worked. Is there are reason the single quote?

Reply | Quote