Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a value on a different worksheet | Excel Discussion (Misc queries) | |||
referencing the name of a worksheet | Excel Discussion (Misc queries) | |||
3-D Worksheet referencing | Excel Discussion (Misc queries) | |||
CountIF() in Worksheet B while referencing cells in Worksheet A | Excel Worksheet Functions | |||
CountIF() in Worksheet B while referencing cells in Worksheet A | Excel Worksheet Functions |