Return Blank from Cell Calculation

[Spreadsheet Geek]

I am attempting to link two large spreadsheets using formulas for each cell.
I want text, numbers, dates etc. to populate, but if a cell is blank, I want
a blank cell left in the cell.

I have always used =if([Wksht1]Input.xls!A1="","",[Wksht1]Input.xls!A1).
This seemed to work fine in Excel 2003. In Excel 2007 it works fine in the
sheet that the formula resides, but calculations based on the first sheet
return errors. The error is occurring because the calculation is not seeing
blanks, but "".

As an example: A second sheet has the formula in a cell
=(Input!$A1+(Input!$B1*Input!$C1/1000))

When I Evaluate the formula, I get =(""+(""*""/1000)), which returns #VALUE!.

I really do not want to go into the second 80 Meg (in Excel 2007), 400 Meg
(in Excel 2003) spreadsheet and add conditionals to all the formulas (40
Pages, at least 100 columns per page, 700 rows of data), to ignore the ""
entries, so the math works throughout the spreadsheet.

Is there a setting, or another way (without Macro's) that I can get a blank
cell out of a formula?

Thanks

 

[Pete_UK]

If the problems are to do with calculations carried out on strings (as
in your example), then you could let the original formulae return a
zero, but apply conditional formatting to the cells such that if the
cell content is zero then change the foreground colour to the
background colour (usually white) so that you can't see it. This will
then allow calculations, but those cells containing zero will appear
as blanks.

Hope this helps.

Pete

 

[Spreadsheet Geek]

Unfortunatly, some of the input cells in the spreadsheet are used to
overwrite results of calculations. When the cells are blank, no calculations
are overwritten, but if they are zero, they overwrite many of the
calculations with the zero values, which would make the spreadsheet useless.

Thanks for trying!

If the problems are to do with calculations carried out on strings (as
in your example), then you could let the original formulae return a
zero, but apply conditional formatting to the cells such that if the
cell content is zero then change the foreground colour to the
background colour (usually white) so that you can't see it. This will
then allow calculations, but those cells containing zero will appear
as blanks.

Hope this helps.

Pete

I am attempting to link two large spreadsheets using formulas for each cell.
I want text, numbers, dates etc. to populate, but if a cell is blank, I want
a blank cell left in the cell.

I have always used =if([Wksht1]Input.xls!A1="","",[Wksht1]Input.xls!A1).
This seemed to work fine in Excel 2003. In Excel 2007 it works fine in the
sheet that the formula resides, but calculations based on the first sheet
return errors. The error is occurring because the calculation is not seeing
blanks, but "".

As an example: A second sheet has the formula in a cell
=(Input!$A1+(Input!$B1*Input!$C1/1000))

When I Evaluate the formula, I get =(""+(""*""/1000)), which returns #VALUE!.

I really do not want to go into the second 80 Meg (in Excel 2007), 400 Meg
(in Excel 2003) spreadsheet and add conditionals to all the formulas (40
Pages, at least 100 columns per page, 700 rows of data), to ignore the ""
entries, so the math works throughout the spreadsheet.

Is there a setting, or another way (without Macro's) that I can get a blank
cell out of a formula?

Thanks