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

G

Guest

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!
 
L

Lori

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

Gord Dibben

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
 
K

krcowen

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
 
G

Guest

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.
 
S

Sean Timmons

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.
 
P

Pete_UK

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
 
Top