My data transferred to the other spreadsheet with no problem, it lost the
format it had in the base spreadsheet such as dates, percentages, etc. The
problem is at the top of each of my columns it calculates totals and
percentages, and the total is counting all rows transferred whether they are
blank or not. I unclicked the "zero values" in the "Tools/Options" tab but
this didn't help. If I "clear contents" on the blanks cells the calculations
are correct but it also clears out the link to the other spreadsheet. One
other thing....there is a formula worksheet in the workbook that controls
calculations for some things (but not all). As far as I can tell this
worksheet isn't causing the "count the blanks" problem.

=IF(Sheet1!A1="","",Sheet1!A1) in a cell on Sheet2

keeps the cell blank until a value is entered in A1 of Sheet1


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 07:09:02 -0800, Bernie R. <Bernie
wrote:

My data transferred to the other spreadsheet with no problem, it lost the
format it had in the base spreadsheet such as dates, percentages, etc. The
problem is at the top of each of my columns it calculates totals and
percentages, and the total is counting all rows transferred whether they are
blank or not. I unclicked the "zero values" in the "Tools/Options" tab but
this didn't help. If I "clear contents" on the blanks cells the calculations
are correct but it also clears out the link to the other spreadsheet. One
other thing....there is a formula worksheet in the workbook that controls
calculations for some things (but not all). As far as I can tell this
worksheet isn't causing the "count the blanks" problem.

Appreciate your reply.

Have this in each row.

Problem is the counter that I have for each column is counting blanks. For
example, I have data for 28 people in 28 consecutive rows. The first 5 rows
contain Header/Label info, row 6 contains the total # of those who attended
training, row 7 calculates the percentage for those who attended training.
Strangely, this doesn't happen in every column but does for the majority.
If I "clear contents" on the blanks the total is correct, but then I loss the
link to the original spreadsheet.

Is there also a way to maintain the original data format in the linkage?

"Gord Dibben" wrote:

=IF(Sheet1!A1="","",Sheet1!A1) in a cell on Sheet2

keeps the cell blank until a value is entered in A1 of Sheet1


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 07:09:02 -0800, Bernie R. <Bernie
wrote:

My data transferred to the other spreadsheet with no problem, it lost the
format it had in the base spreadsheet such as dates, percentages, etc. The
problem is at the top of each of my columns it calculates totals and
percentages, and the total is counting all rows transferred whether they are
blank or not. I unclicked the "zero values" in the "Tools/Options" tab but
this didn't help. If I "clear contents" on the blanks cells the calculations
are correct but it also clears out the link to the other spreadsheet. One
other thing....there is a formula worksheet in the workbook that controls
calculations for some things (but not all). As far as I can tell this
worksheet isn't causing the "count the blanks" problem.

Data formats cannot be "linked".

Post the formula(s) you use for counting and where they are located.


Gord

On Wed, 30 Jan 2008 12:31:04 -0800, Bernie R <Bernie
wrote:

Appreciate your reply.

Have this in each row.

Problem is the counter that I have for each column is counting blanks. For
example, I have data for 28 people in 28 consecutive rows. The first 5 rows
contain Header/Label info, row 6 contains the total # of those who attended
training, row 7 calculates the percentage for those who attended training.
Strangely, this doesn't happen in every column but does for the majority.
If I "clear contents" on the blanks the total is correct, but then I loss the
link to the original spreadsheet.

Is there also a way to maintain the original data format in the linkage?

"Gord Dibben" wrote:

=IF(Sheet1!A1="","",Sheet1!A1) in a cell on Sheet2

keeps the cell blank until a value is entered in A1 of Sheet1


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 07:09:02 -0800, Bernie R. <Bernie
wrote:

My data transferred to the other spreadsheet with no problem, it lost the
format it had in the base spreadsheet such as dates, percentages, etc. The
problem is at the top of each of my columns it calculates totals and
percentages, and the total is counting all rows transferred whether they are
blank or not. I unclicked the "zero values" in the "Tools/Options" tab but
this didn't help. If I "clear contents" on the blanks cells the calculations
are correct but it also clears out the link to the other spreadsheet. One
other thing....there is a formula worksheet in the workbook that controls
calculations for some things (but not all). As far as I can tell this
worksheet isn't causing the "count the blanks" problem.

Appreciate your time and patience.

I reformat the cell containing dates and percentages to fix that problem.

I use counta for non-date cells and count for cells containing dates.


Is there an easier way to enter "=IF(Sheet1!A1="","",Sheet1!A1)" for each
row? My next worksheet is going to link 185 rows of like data from 27
spreadsheets, exactly like the one I'm doing now but this one contains 28
rows from 6 spreadsheets.




"Gord Dibben" wrote:

Data formats cannot be "linked".

Post the formula(s) you use for counting and where they are located.


Gord

On Wed, 30 Jan 2008 12:31:04 -0800, Bernie R <Bernie
wrote:

Appreciate your reply.

Have this in each row.

Problem is the counter that I have for each column is counting blanks. For
example, I have data for 28 people in 28 consecutive rows. The first 5 rows
contain Header/Label info, row 6 contains the total # of those who attended
training, row 7 calculates the percentage for those who attended training.
Strangely, this doesn't happen in every column but does for the majority.
If I "clear contents" on the blanks the total is correct, but then I loss the
link to the original spreadsheet.

Is there also a way to maintain the original data format in the linkage?

"Gord Dibben" wrote:

=IF(Sheet1!A1="","",Sheet1!A1) in a cell on Sheet2

keeps the cell blank until a value is entered in A1 of Sheet1


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 07:09:02 -0800, Bernie R. <Bernie
wrote:

My data transferred to the other spreadsheet with no problem, it lost the
format it had in the base spreadsheet such as dates, percentages, etc. The
problem is at the top of each of my columns it calculates totals and
percentages, and the total is counting all rows transferred whether they are
blank or not. I unclicked the "zero values" in the "Tools/Options" tab but
this didn't help. If I "clear contents" on the blanks cells the calculations
are correct but it also clears out the link to the other spreadsheet. One
other thing....there is a formula worksheet in the workbook that controls
calculations for some things (but not all). As far as I can tell this
worksheet isn't causing the "count the blanks" problem.