Linking Question (Excel 2003 SP1)

Topic

New Reply

While linking to cells from another worksheet, is there a way to prevent a linked cell from populating with a zero when the information in the original cell has no data (null) while, at the same time, a linked cell will populate with a zero when the original cell has a zero? I am using a median calculation on the linked information so the linked cell has to coincide with the original cell. Thank you.

Reply | Quote

Replies

You could use formulas like this one:

=IF(ISBLANK(Sheet3!A1),"",Sheet3!A1)

Functions such as SUM, AVERAGE and MEDIAN ignore text values, so they should return the desired value.

Reply | Quote

Thank you Hans

Reply | Quote

Is there a way of insuring that a blank cell stays blank when analyzing a range of data that includes the blank cell in a pivot table? This particular range of data is calculated from another column on the same worksheet with the following formula: =IF(L676=0,””,(C676/L676)*22.

Thank you.

Reply | Quote

I’m sorry, I don’t understand your question. Could you explain in more detail what you mean, and perhaps attach a sample workbook illustrating the problem?

Reply | Quote

I apologize Hans,

I will attach a workbook.

I am trying to analyze data in a pivot table that will be eventually used for a median calculation. However, the pivot table is replacing blank cells from the referenced data (Sheet 1 Column D) with zeroes. The referenced data (in column D of Sheet 1) is derived by using a calculation involving columns C and L of Sheet 1 by the use of the formula: =IF(L676=0,””,(C676/L676)*22. Is there a method that will prevent the pivot table from populating zeroes into what should be blank cells? Can the double quotes (“”) be replaced with something else in the formula to prevent this from happening?

It does not seem that I can send this response with the attachment. Is there a way around this?

Thank you.

Reply | Quote

The maximum size of an attachment is 100 KB. If your workbook is over that size, create a zip file from it and attach that.

Reply | Quote

Thank you Hans,

The workbook is attached.

I am trying to analyze data in a pivot table that will be eventually used for a median calculation. However, the pivot table is replacing blank cells from the referenced data (Sheet 1 Column D) with zeroes. The referenced data (in column D of Sheet 1) is derived by using a calculation involving columns C and L of Sheet 1 by the use of the formula: =IF(L676=0,””,(C676/L676)*22. Is there a method that will prevent the pivot table from populating zeroes into what should be blank cells? Can the double quotes (“”) be replaced with something else in the formula to prevent this from happening?

Thank you.

Reply | Quote

The SUM function in Excel never returns a blank, even if all its arguments are blank – it returns 0 instead. You cannot avoid that.
So this is not a problem of the pivot table, it is a consequence of the way Excel calculates sums.

If you want to calculate the median of a column in the pivot table excluding 0 values, you can use an array formula (confirm with Ctrl+Shift+Enter) with MEDIAN and IF. See the attached version.
It would be similar if you want to calculate the median of a row.

Reply | Quote

Thank you Hans,

Is there another function besides SUM that will return the actual value to the pivot table and yet still maintain a blank cell? The default seems to be COUNT which, in this case, returns a 1 in every cell.

Thank you

Reply | Quote

No, all these aggregate functions return a number, never a blank, and that’s as it should be, even if you don’t want it that way. The sum or count of something isn’t blank, it’s a number that can be 0 if there is nothing to sum or count.

Reply | Quote

Thank you Hans,

Reply | Quote

Can you think of another way to derive this four hour block pattern without the use of a pivot table? In particular, I am looking for a quick way to auto fill values, from a column of data, (Column D in this case), for a specific four hour block from each day of the month. The four values of each day would be distributed in cells beside each other in one of the 28 to 31 rows like they were in the pivot table.

Thank you.

Reply | Quote

An alternative would be to enter an apostrophe in the blank linked cell which forces excel to see null text.

Reply | Quote

You could use INDEX and MATCH in array formulas, e.g. in B5:

=INDEX(‘Sheet 1′!$D$3:$D$674,MATCH($A5&B$4,’Sheet 1′!$A$3:$A$674&’Sheet 1’!$B$3:$B$674,0))

Because these are lookup formulas, not sums, they simply copy the blanks. See the attached version.

Reply | Quote

WOW! This is perfect! Thank you Hans.

Reply | Quote