problem with pasting & #value

[geofffra]

Hi,

I hope some one can help.
I have created a spreadsheet to extract data and sort it into individual worksheets for the work, units and time individuals have done daily. This data is then copied to the bosses non modifiable spreadsheet.
My problem is:
I am trying to copy& paste data from my SS with formulas to to the other. The cells pasted into only have formatting of numbers and time) The pasted blank cells with "" criteria return a #value in the calculations column of the bosses. Can anyone help with a means of removing what I assume are non priting characters being pasted from my sheet. This is all currently done with macros, but do I need to take a different track.

Thanks

Geoff

[Bob Phillips]

Take a look at Dave McRitchie's TrimAll function at
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--

HTH

RP
(remove nothere from the email address if mailing direct)


"geofffra" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I hope some one can help.
> I have created a spreadsheet to extract data and sort it into
> individual worksheets for the work, units and time individuals have
> done daily. This data is then copied to the bosses non modifiable
> spreadsheet.
> My problem is:
> I am trying to copy& paste data from my SS with formulas to to the
> other. The cells pasted into only have formatting of numbers and time)
> The pasted blank cells with "" criteria return a #value in the
> calculations column of the bosses. Can anyone help with a means of
> removing what I assume are non priting characters being pasted from my
> sheet. This is all currently done with macros, but do I need to take a
> different track.
>
> Thanks
>
> Geoff
>
>
> --
> geofffra
> ------------------------------------------------------------------------
> geofffra's Profile:
http://www.excelforum.com/member.php...o&userid=29171
> View this thread: http://www.excelforum.com/showthread...hreadid=489013
>

[geofffra]

Thanks Bob,

Looks like some reading ahead. So far a quick glance at the site does not give the answer, but I will have a much closer look.

As everything works well with zero values in the cells(boss hates the zeros), my thoughts are that I some how need to search through all the cells and delete ALL zero values before cut @ paste

[Bob Phillips]

Why not just change the boss' spreadsheet to suppress zeros,
Tools>Options>View and uncheck zero values.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"geofffra" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Bob,
>
> Looks like some reading ahead. So far a quick glance at the site does
> not give the answer, but I will have a much closer look.
>
> As everything works well with zero values in the cells(boss hates the
> zeros), my thoughts are that I some how need to search through all the
> cells and delete ALL zero values before cut @ paste
>
>
> --
> geofffra
> ------------------------------------------------------------------------
> geofffra's Profile:
http://www.excelforum.com/member.php...o&userid=29171
> View this thread: http://www.excelforum.com/showthread...hreadid=489013
>

[geofffra]

Thanks Bob,

I was unaware of that feature.
Now to see if boss is happy with that solution as i think they do more with it at head office and payroll.


Geoff.

[David McRitchie]

Or if you want to be more selective is what cells you want to blank out the zeros
(zeros are useful, they mean someone entered something, unless it is a formula)
you can use cell formatting with the third operand empty

Format cell in normal way then modify the 3 third operand to empty

General becomes General;-General;;@
or General;-General;;

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
becomes
_(* #,##0.00_);_(* (#,##0.00);;_(@_)
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"geofffra" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks Bob,
>
> I was unaware of that feature.
> Now to see if boss is happy with that solution as i think they do more
> with it at head office and payroll.
>
>
> Geoff.
>
>
> --
> geofffra
> ------------------------------------------------------------------------
> geofffra's Profile: http://www.excelforum.com/member.php...o&userid=29171
> View this thread: http://www.excelforum.com/showthread...hreadid=489013
>