Referencing another worksheet
 

Hi,
I haven't used excel in a while and have run into a problem which seems
pretty simple.

I've got a simple formula on a certain sheet of the form "='Master-H'!AW105"
, to replicate data on another form.

It work but does two things I don't like. When there are blank values in the
original sheet, it return a '0'; and decimals get rounded. I am pretty sure
it has nothing to do with formatting because exporting the data to access
those two problems still exist.

What am I doing wrong?

Referencing another worksheet
 

You didn't mention what version of Excel you are using so it's hard to
give you a menu path to get where you need to be.

Basically, do a Format Cells on the range you are concerned with,
select Number format and set the number of decimal places you want
displayed.

Dealing with zeros is a bit harder. There is a workbook-wide setting
that will suppress the display of zeros, but that may be a bit extreme.

A cell formula like

=IF( 'Master-H'!AW105 = "", "",'Master-H'!AW105)

would do it if entered in your range of interest.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111553

Referencing another worksheet
 

=IF(='Master-H'!AW105="","",='Master-H'!AW105) to not display zeros.

What do you mean by "rounding decimals"?


Gord Dibben MS Excel MVP

On Tue, 30 Jun 2009 14:38:01 -0700, Access::Student
wrote:

Hi,
I haven't used excel in a while and have run into a problem which seems
pretty simple.

I've got a simple formula on a certain sheet of the form "='Master-H'!AW105"
, to replicate data on another form.

It work but does two things I don't like. When there are blank values in the
original sheet, it return a '0'; and decimals get rounded. I am pretty sure
it has nothing to do with formatting because exporting the data to access
those two problems still exist.

What am I doing wrong?

Referencing another worksheet
 

Sorry I'll try and explain myself better, I'm using 2007. And the part about
the decimals I figured out, I actually had my data type in access set to int
so it wasn't importing them correctly.

So the problem I'm having is that the other cells that should be blank(NULL)
have 0 in them. I'd already figured out how to remove them by formating (
0;-0;;@ ) so that they don't display in excel, but when I import the data
into access it imports the 0's instead of NULL, which is a problem for me. So
I need to do something outside of formatting and actually have NULL values in
those cells

"jamescox" wrote:


You didn't mention what version of Excel you are using so it's hard to
give you a menu path to get where you need to be.

Basically, do a Format Cells on the range you are concerned with,
select Number format and set the number of decimal places you want
displayed.

Dealing with zeros is a bit harder. There is a workbook-wide setting
that will suppress the display of zeros, but that may be a bit extreme.

A cell formula like

=IF( 'Master-H'!AW105 = "", "",'Master-H'!AW105)

would do it if entered in your range of interest.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111553

Referencing another worksheet
 

Sorry I read too quickly. I thought you mean to add the second formula to
formatting. It does work if it's put into the actual cell. thanks

"jamescox" wrote:


You didn't mention what version of Excel you are using so it's hard to
give you a menu path to get where you need to be.

Basically, do a Format Cells on the range you are concerned with,
select Number format and set the number of decimal places you want
displayed.

Dealing with zeros is a bit harder. There is a workbook-wide setting
that will suppress the display of zeros, but that may be a bit extreme.

A cell formula like

=IF( 'Master-H'!AW105 = "", "",'Master-H'!AW105)

would do it if entered in your range of interest.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111553

Referencing another worksheet
 

As an FYI, it never hurts to explain the real problem :Bgr

If the cell formula I suggested (which should place the null string ""
in the cell) doesn't get translated into a Null when Access sucks it it,
you'll need someone with more horsepower (and sense) than I to get you
there!


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111553