Basic Excel 2000 Questions
Posted by Michael on November 28, 1999 10:50 PM
1. How do I keep cells from displaying formula results when information is not entered yet? Example: "sample number" entered in one cell is automatically entered in several other cells throughout workbook, but until "sample number" is entered there is a zero in the other locations. This also applies to column totals and pretty much anywhere I have a formula where data has not been entered yet. "Don't show zero values" is not an option as I need, at times, to enter zero's.
2. How do I display total number of items in a column or row? I don't want to SUM the column or row as that will add all the sample numbers, I simply want the total number of samples.
3. How do I keep a cell with a formula from being deleted? Example: like in question #1 where sample numbers are entered in various places, in those various places the cells are formula's that if someone selects and deletes the sample number, the formula is also deleted.
Thanks
Posted by Irek on November 29, 1999 9:20 AM
In "other locations" enter formula
=IF(sample cell="","",sample cell)
where: sample cell is cell address like A1 or the name of that cell
This also applies to column totals and pretty much anywhere I have a formula where data has not been entered yet. "Don't show zero values" is not an option as I need, at times, to enter zero's.
=COUNTA(range)
do this
select all cells, press CTRL+1, select PROTECTION tab, clear LOCK option,
press F5, select SPECIAL..., FORMULAS, OK
press CTRL+1, select PROTECTION tab, set LOCK option. Protect a spreadsheet with TOOLS, PROTECTION, PROTECT SHEET... do not type any password if you gonna forget it.
Irek
Posted by Michael on November 30, 1999 12:30 AM
Re: Thanks Irek ... another question though :-)
The solutions you provided worked great, thanks. I have another mind numbing problem though ... Sheet 1 (source) is where 34 sample numbers and locations are entered. That information is applied to various other sheets in various cells. Of the 34 samples, 4 need to be in a specific cell range. Example:
Sheet 1 B7:B41 (34 samples in no particular order)
Sheet 2 B7:B37 (30 of 34 samples in whatever order they are entered on Sheet 1). B42:B46 (4 specific samples from list on Sheet 1). Each sample has a location name unique to itself where as the number is a running total for the year (34 samples/month, 408 total/year). This is why I was thinking that the name could be used to select the 4 specific samples regardless of where they are entered in the cell range on Sheet 1. I tried this...
IF('Sample Identification'!B7:B41="Medical Center","",'Sample Identification'!B7:B41)
Formula didn't work. Even if this formula had worked it would have only solved part of the problem. For each sample there is analysis entered on Sheet 3 (same row where sample number is displayed from Sheet 1). I also need to have some, but not all analysis for the 4 specific samples "plugged in" to the row on Sheet 2 where the 4 specific sample names are to be displayed.
I have an Excel book for reference, but not knowing what I need to do makes it difficult to look up.
I hope this makes sense to you?
Thanks again
