putting a (.) period in blank cells when pasting data
 

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!

putting a (.) period in blank cells when pasting data
 

Being in worksheet functions i assume you want to do this outside of code,
copy and paste a "." into your print area (save for future if easier) then
copy your data, paste special and values with skip blanks checked.

If anyone has an elegant method I'd be interested to know it.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"nospam" <"karlb65(nospam)" wrote:

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!

putting a (.) period in blank cells when pasting data
 

Other methods:
- Edit Replace with "." in the replace box and find box empty.
- Edit Goto Special Blanks then type "." followed by ctrl+enter.

nospam wrote:

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!

putting a (.) period in blank cells when pasting data
 

Just put a period in blank cells by applying conditional formatting.

Select the range to which you want to apply this, and then go to
Format--Conditional formatting.

Enter the formula =ISBLANK(A1) in the dialog box, select the format button
and enter a period.

Click OK.

(The formula above assumes the range in question contains A1. If it does
not, replace A1 with an appropriate cell reference.)

Dave
--
Brevity is the soul of wit.


"nospam" <"karlb65(nospam)" wrote:

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!

putting a (.) period in blank cells when pasting data
 

Seems to me on the reference sheet you could trap for 0 and leave the cell
looking blank and no need for a dot at all.

=IF(Sheet2!A1="","",Sheet2!A1)


Gord Dibben MS Excel MVP

On Mon, 18 Dec 2006 08:01:51 -0500, nospam
wrote:

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!

putting a (.) period in blank cells when pasting data
 

This won't work if you want to see a difference between a zero and a
blank, but, if that is not an issue, you could simply format the final
destination on your company form so that zeros don't show up, then a
blank will be a blank; but, a zero would also be a blank which could be
a problem.
Good luck.
Ken


Gord Dibben wrote:
Seems to me on the reference sheet you could trap for 0 and leave the cell
looking blank and no need for a dot at all.

=IF(Sheet2!A1="","",Sheet2!A1)


Gord Dibben MS Excel MVP

On Mon, 18 Dec 2006 08:01:51 -0500, nospam
wrote:

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!

putting a (.) period in blank cells when pasting data
 

Maybe I can clarify further. The form I am going to print will either
have seat number or will have a blank where the seat number goes to be
handwritten in after printing. So, on my form to be printed the formula
is =Sheet1!E1 which references a column with code letters that represent
type of seating. Some do not have seating codes so on my form it returns
a 0 value. To remedy this the maker of the spreadsheet put periods in to
enable you to handwrite in the blank spaces and not have a fat 0 there.
I did a macro and used the find/replace to find empty cells and add
dots, and then another to use after printing to remove the dots for next
time, but there must be a better way.

So, in my example, Sheet10 has my cell where a code would go. In the
cell is =Sheet1!E1. Let's say Sheet1 E1 has a code of CC, it would show
up in my form to print. If, however, it has no code, it returns a 0. So,
then I have other info such as name, etc which is fine but a zero in the
blank where we would later handwrite a code. Same with the seat assignments.


wrote:
This won't work if you want to see a difference between a zero and a
blank, but, if that is not an issue, you could simply format the final
destination on your company form so that zeros don't show up, then a
blank will be a blank; but, a zero would also be a blank which could be
a problem.
Good luck.
Ken


Gord Dibben wrote:
Seems to me on the reference sheet you could trap for 0 and leave the cell
looking blank and no need for a dot at all.

=IF(Sheet2!A1="","",Sheet2!A1)


Gord Dibben MS Excel MVP

On Mon, 18 Dec 2006 08:01:51 -0500, nospam
wrote:

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!

putting a (.) period in blank cells when pasting data
 

I would say, this matches best with the prior formula of

=IF(Sheet1!E1="","",Sheet2!E1)
but just change to
=IF(Sheet1!E1="",".",Sheet2!E1)

In this way, if the cell E1 of Sheet 1 is blank, it will appear on Sheet 2
as "."
Otherwise, it will show with whatever chaacers are in the field.

If this isn't entering the period, may just need to adjust the formula to
look for " " instead.




"nospam" <"karlb65(nospam)" wrote:

Maybe I can clarify further. The form I am going to print will either
have seat number or will have a blank where the seat number goes to be
handwritten in after printing. So, on my form to be printed the formula
is =Sheet1!E1 which references a column with code letters that represent
type of seating. Some do not have seating codes so on my form it returns
a 0 value. To remedy this the maker of the spreadsheet put periods in to
enable you to handwrite in the blank spaces and not have a fat 0 there.
I did a macro and used the find/replace to find empty cells and add
dots, and then another to use after printing to remove the dots for next
time, but there must be a better way.

So, in my example, Sheet10 has my cell where a code would go. In the
cell is =Sheet1!E1. Let's say Sheet1 E1 has a code of CC, it would show
up in my form to print. If, however, it has no code, it returns a 0. So,
then I have other info such as name, etc which is fine but a zero in the
blank where we would later handwrite a code. Same with the seat assignments.


wrote:
This won't work if you want to see a difference between a zero and a
blank, but, if that is not an issue, you could simply format the final
destination on your company form so that zeros don't show up, then a
blank will be a blank; but, a zero would also be a blank which could be
a problem.
Good luck.
Ken


Gord Dibben wrote:
Seems to me on the reference sheet you could trap for 0 and leave the cell
looking blank and no need for a dot at all.

=IF(Sheet2!A1="","",Sheet2!A1)


Gord Dibben MS Excel MVP

On Mon, 18 Dec 2006 08:01:51 -0500, nospam
wrote:

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!

putting a (.) period in blank cells when pasting data
 

Why not use an underscore instead of a period and then when printed out
it will appear the same as your line?

Hope this helps.

Pete

Sean Timmons wrote:

I would say, this matches best with the prior formula of

=IF(Sheet1!E1="","",Sheet2!E1)
but just change to
=IF(Sheet1!E1="",".",Sheet2!E1)

In this way, if the cell E1 of Sheet 1 is blank, it will appear on Sheet 2
as "."
Otherwise, it will show with whatever chaacers are in the field.

If this isn't entering the period, may just need to adjust the formula to
look for " " instead.




"nospam" <"karlb65(nospam)" wrote:

Maybe I can clarify further. The form I am going to print will either
have seat number or will have a blank where the seat number goes to be
handwritten in after printing. So, on my form to be printed the formula
is =Sheet1!E1 which references a column with code letters that represent
type of seating. Some do not have seating codes so on my form it returns
a 0 value. To remedy this the maker of the spreadsheet put periods in to
enable you to handwrite in the blank spaces and not have a fat 0 there.
I did a macro and used the find/replace to find empty cells and add
dots, and then another to use after printing to remove the dots for next
time, but there must be a better way.

So, in my example, Sheet10 has my cell where a code would go. In the
cell is =Sheet1!E1. Let's say Sheet1 E1 has a code of CC, it would show
up in my form to print. If, however, it has no code, it returns a 0. So,
then I have other info such as name, etc which is fine but a zero in the
blank where we would later handwrite a code. Same with the seat assignments.


wrote:
This won't work if you want to see a difference between a zero and a
blank, but, if that is not an issue, you could simply format the final
destination on your company form so that zeros don't show up, then a
blank will be a blank; but, a zero would also be a blank which could be
a problem.
Good luck.
Ken


Gord Dibben wrote:
Seems to me on the reference sheet you could trap for 0 and leave the cell
looking blank and no need for a dot at all.

=IF(Sheet2!A1="","",Sheet2!A1)


Gord Dibben MS Excel MVP

On Mon, 18 Dec 2006 08:01:51 -0500, nospam
wrote:

Hi,

This might be an oddball question, but here goes. I have a spreadsheet
that I have to copy data from one sheet to another. Then any blank cells
I have to manually put a period (.) in so that when those cells are
referenced they don't show a 0 on a form we print. Is there any way when
I copy data from one sheet to another I can have excel put the period in
the blank cells automatically?

Here's the scenario: we have to enter certain data copied from our
website. So, we copy it from the site to a sheet in excel. Then we copy
certain columns from that over to another sheet. The data from sheet 2
is then referenced by another sheet which is formatted to be printed on
a company form. It contains some pre-filled info which is printed out
then filled out manually by us. Thus, I need a dot (which doesn't show
up too much on the blank lines, instead of a 0 (which would print on the
blank lines on our form if not for the period(.))

Thanks!