Getting the "0" out of an auto populate cell.

B

Bill638

I'm building an audit tool that has a checklist in one worksheet and the
report in another worksheet. The checklist worksheet has questions and a
YES/NO/NA column. When the question is answered NO, it autopopulates the
corresponding recommendation in the report worksheet. I have a "reference"
column that contains links to best practices, etc. in both worksheets.

When I don't have a reference to cite in the checklist worksheet, and the
answer to the question is "NO" (which causes the automatic population of the
recommendation in the report worksheet), the reference column has a "0" in
it.

WHAT CAN I DO TO STOP THE "0" FROM APPEARING IN THE REFERENCE COLUMN IN THE
REPORT WORKSHEET?

Following are examples of the formulas I'm currently using for the two
columns (recommendation and reference).

Recommendation: =IF(Checklist!C201="NO",Checklist!D201,"")
Reference: =IF(Checklist!C201="NO",Checklist!E201,"")

Many thanks.
 
G

Glenn

Bill638 said:
I'm building an audit tool that has a checklist in one worksheet and the
report in another worksheet. The checklist worksheet has questions and a
YES/NO/NA column. When the question is answered NO, it autopopulates the
corresponding recommendation in the report worksheet. I have a "reference"
column that contains links to best practices, etc. in both worksheets.

When I don't have a reference to cite in the checklist worksheet, and the
answer to the question is "NO" (which causes the automatic population of the
recommendation in the report worksheet), the reference column has a "0" in
it.

WHAT CAN I DO TO STOP THE "0" FROM APPEARING IN THE REFERENCE COLUMN IN THE
REPORT WORKSHEET?

Following are examples of the formulas I'm currently using for the two
columns (recommendation and reference).

Recommendation: =IF(Checklist!C201="NO",Checklist!D201,"")
Reference: =IF(Checklist!C201="NO",Checklist!E201,"")

Many thanks.
Click to expand...


=IF(AND(Checklist!C201="NO",Checklist!D201<>""),Checklist!D201,"")
 
B

Bill638

Glenn,

Many thanks. That solved that problem. Now, a larger challenge (but then
again, maybe not). If the answer to the question on the checklist page is
YES, then there is no need for the auto-populated recommendation on the
report worksheet.

What can I do to have that question totally ignored on the report worksheet.
Now, if the answer is YES on the checklist worksheet, I get blank cells (an
empty row) in the report worksheet, which necessitates manually deleting all
such rows.

Again, many thanks for your quick response.

Bill
 
G

Glenn

Bill638 said:
Glenn,

Many thanks. That solved that problem. Now, a larger challenge (but then
again, maybe not). If the answer to the question on the checklist page is
YES, then there is no need for the auto-populated recommendation on the
report worksheet.

What can I do to have that question totally ignored on the report worksheet.
Now, if the answer is YES on the checklist worksheet, I get blank cells (an
empty row) in the report worksheet, which necessitates manually deleting all
such rows.

Again, many thanks for your quick response.

Bill
Click to expand...


I would probably use an AutoFilter to hide all blank rows.
 
B

Bill638

Thanks Glenn....now let me show my real ignorance. Just how would I go about
doing that? (I've gone to the Help in Excel, and opened the Advance on the
filter tool, but I'm real confused :-()

Thanks.
 
G

Glenn

Bill638 said:
Thanks Glenn....now let me show my real ignorance. Just how would I go about
doing that? (I've gone to the Help in Excel, and opened the Advance on the
filter tool, but I'm real confused :-()

Thanks.
Click to expand...


Well, without actually seeing your workbook, it would be hard to come up with
more explicit instructions. Maybe this will help:

http://www.contextures.com/xlautofilter01.html
 
B

Bill638

I hate to keep bothering you, but I'd be happy to send you a small section of
the workbook.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel formula question 5
Problem with SET & REF function 0
Sum of different value in different worksheet & cell number 2
Removing #N/A from an "If/Then" statement 4
Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column o 2
Format for a cell with a formula in it but I don't want the 0 to c 3
get a cell reference as a result of an If Function 1
row number of last non-blank cell 14

Top