Linking Sheets shows 0's for blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

magmike - 28 Apr 2008 21:58 GMT

I have a workbook on the server, that links to multiple files from
different users.

I used Paste Link to acheive this. Because the number of rows can
increase, I selected the columns in use, and paste linked those.
However, where there are blank fields, the new file shows zeros. Is
there a way to link without showing zeros for blank cells?

Note - all cells are formatted as text, and when I select Ignore Blank
Cells in the Paste Special box, the Paste Link button is not
available.

Thanks in advance!

magmike

Reply to this Message

Gord Dibben - 28 Apr 2008 22:51 GMT

To ignore the blanks and not return zeros you must manually create a trap in the
formula.

Assume your formula is similar to this..='[12months.xls]1stQ'!A2

You need to add the trap.

=IF('[12months.xls]1stQ'!A2="","",'[12months.xls]1stQ'!A2)

which cannot be done through Paste Link.

You could run a macro on the sheet with the links.

Sub Blank_Link_Trap()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(" & myStr & "="""",""""," & myStr & ")"
End If
End If
Next
End Sub

Gord Dibben MS Excel MVP

>I have a workbook on the server, that links to multiple files from
>different users.
[quoted text clipped - 11 lines]
>
>magmike

Reply to this Message

magmike - 30 Apr 2008 00:41 GMT

> To ignore the blanks and not return zeros you must manually create a trap in the
> formula.
[quoted text clipped - 41 lines]
>
> - Show quoted text -

Could this be done automatically after the update occurs? On protected
sheets? In a protected workbook? Without the user having to do
anything?

Reply to this Message

Gord Dibben - 30 Apr 2008 20:28 GMT

According to the initial post, you are the user who is doing the updating by
selecting from the other books and paste-linking to a single book.

Which books and sheets would be the protected ones?

I would say what you want is possible with enough code but need much more detail
about which books would be opened and which sheets and ranges would be copied to
the single book before running the Blank_Link_Trap code.

I'm not much of a coder so we would need help for this.

Gord

>> To ignore the blanks and not return zeros you must manually create a trap in the
>> formula.
[quoted text clipped - 45 lines]
>sheets? In a protected workbook? Without the user having to do
>anything?

Reply to this Message

magmike - 30 Apr 2008 20:54 GMT

> According to the initial post, you are the user who is doing the updating by
> selecting from the other books and paste-linking to a single book.
[quoted text clipped - 60 lines]
>
> - Show quoted text -

The master file (Leads.xls) is the only one with protection. There are
five sheets in this file and they are all protected as is the
workbook. Those sheets are named for and linked from Tracy.xls,
Ed.xls, Keith.xls, Nicole.xls and Mike.xls. The five seperate files
are not protected in anyway.

In the individual files, there may be, for example, 200 rows and 3
columns. Any cells in the individual files not containing data,
display as a blank cell, as would be expected. When someone opens the
master file (Leads.xls) a box comes up that asks the user if they want
to update, and of course, they choose to update. At that point, I
assume it is reaching out to the individual files (each named for the
salesman) to get the latest data. However, any of the cells in the
three colums, including those in rows without any data at all, display
as a "0".

I hope that answers all of your questions.

magmike

Reply to this Message

Gord Dibben - 01 May 2008 03:14 GMT

When you open Leads.xls you get just the standard "this workbook contains links
to other data sources" then the choices to update or not.

Is that correct?

If so, Master.xls already has the links formulas waiting for updating with new
data from the other 5 workbooks.

I would suggest you open Leads.xls and run the macro on each of the 5 sheets to
add the Blank_Link_Trap to your links.

Make a copy of Leads.xls first to experiment with.

Gord

>The master file (Leads.xls) is the only one with protection. There are
>five sheets in this file and they are all protected as is the
[quoted text clipped - 15 lines]
>
>magmike

Reply to this Message

magmike - 01 May 2008 14:43 GMT

> When you open Leads.xls you get just the standard "this workbook contains links
> to other data sources" then the choices to update or not.
[quoted text clipped - 32 lines]
>
> - Show quoted text -

But can I have it do that automatically, or do I have to go to each
sheet and go the macro toolbar and select the macro to run? I'm hoping
for seemless use of this file as the other users aren't as computer
savvy.

Reply to this Message

Gord Dibben - 01 May 2008 22:48 GMT

I guess I just don't understand what's happening with your linked cells to other
books.

I am thinking that you would do this just once to add the 0 trap in Lead.xls
linked cells.

Then when you open Lead.xls it will ask to update and pick up the data from the
other workbooks.

That's the purpose of linking.............you don't have to re-build the link
formulas every time you open the workbook.

Your users do nothing but open Lead.xls and say yes to "update links".

Are you needing to re-build the link formulas each time and for why?

Gord

>But can I have it do that automatically, or do I have to go to each
>sheet and go the macro toolbar and select the macro to run? I'm hoping
>for seemless use of this file as the other users aren't as computer
>savvy.

Reply to this Message