How to make blank cells show blank when referenced in another workbook

[bigpapa]

I have created a spreadsheet with 8 different columns where data is entered and have all those cells referenced to specific cells in worksheet 2. I never know how many documents need to be entered so i formatted 2000 cells. The only problem is when i import it into access it puts zeros in thousands of cells sometimes making u have to delete them. I have the zeros turned off in excel so they don't show but i need the cells in worksheet 2 to show blank if they are blank in worksheet 1 so they will import into access that way

 

[jeffreybrown]

Hello & Welcome to the Board,

Couldn't you update the formula to say if the value is nothing then return nothing ""

 

[brettdj]

For a quick non-VBA way to ignore blanks

- copy the data from worksheet 2 into worksheet 1 (your sheet you import to access)
- past special the values in worksheet 1 over themselves
- Select all the Cells, Press F5 ….. Special …. Constants (which selects all non-blank cells)
- In the Formula Bar type a formula that refers to the top left cell in worksheet 2, ie if this was A1
='Worksheet 2'!A1
- And use "Ctrl & Enter" together to force this relative calculation into all the non-blank cells.

See "Quickly creating a summary differential sheet or linked output sheet - ignoring any blank cells", http://www.experts-exchange.com/A_4003.html

hth
Dave

 

[bigpapa]

According to the link you gave both sheets are suppose to be identical but they aren't. The 1st sheet has some fields the imagers only use and the reason i reference worksheet 2 is b/c it only contains the columns that are in the access database and can be appended w/o any error. All i am doing is this "=Sheet1!B3" which references the right 1st box of the same column in sheet one then just pull the cell all the way down to 2000 to copy the reference all the way down. i need to know what to add to that to make blank cells return blank.

 

[jeffreybrown]

Maybe try....

=IF(Sheet1!B3,Sheet1!B3,"")

 

[bigpapa]

that returns a blank like i need but it also puts a #VALUE! in the cells that have data instead of copying what was in the cell to the reference cell

 

[jeffreybrown]

that returns a blank like i need but it also puts a #VALUE! in the cells that have data instead of copying what was in the cell to the reference cell

I'm kind of flying blind here. Can you post a small sample of what is going on and the formulas you are using.

See my sig block for the HTML maker.

 

[GaryWMn]

I often use the LEN() function to determine if a blank should be displayed or not....

=IF(LEN(Sheet1!B3)<>0,Sheet1!B3,"")

You might need to include some extra features (dynamic named range) to help Access grab the correct range of cells.

Will this work for your purposes?

Gary

 

[brettdj]

According to the link you gave both sheets are suppose to be identical but they aren't.

Correct. Given your layout my suggestion is not appropriate for your problem

Cheers

Dave

 

[bigpapa]

Sorry guys i have been busy and had to set this aside. Basically i need any cell in worksheet A to show up blank if it is referenced in worksheet B. I only made to row 2000 reference but alot of the time there are only like 30 rows used so it shows 1970 rows with zero's when i import worksheet 2 into access. So i believe if i can make anything that is blank in worksheet 1 show up as blank in worksheet 2 then people won't have to delete 1970 documents sometimes and only the data present will import into Access.