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

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.
--
Bill

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

Bill638 wrote:
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.


=IF(AND(Checklist!C201="NO",Checklist!D201<""),Ch ecklist!D201,"")

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

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
--
Bill


"Glenn" wrote:

Bill638 wrote:
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.


=IF(AND(Checklist!C201="NO",Checklist!D201<""),Ch ecklist!D201,"")

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

Bill638 wrote:
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


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

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

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.
--
Bill


"Glenn" wrote:

Bill638 wrote:
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


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

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

Bill638 wrote:
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.


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

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

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


"Bill638" wrote:

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.
--
Bill

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

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


If you're still having problems, post a section of your workbook on
www.savefile.com and I'll see if I can take a look at it.

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

Thanks Glenn,

I've uploaded it. The project URL is
http://www.savefile.com/projects/808763180 and the link to the file is
http://www.savefile.com/files/2093305. Note that the report worksheet also
has a cover page, overview, etc., that I don't want affected by the filter.

I greatly appreciate your assistance.
--
Bill


"Glenn" wrote:

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


If you're still having problems, post a section of your workbook on
www.savefile.com and I'll see if I can take a look at it.

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

Bill638 wrote:
Thanks Glenn,

I've uploaded it. The project URL is
http://www.savefile.com/projects/808763180 and the link to the file is
http://www.savefile.com/files/2093305. Note that the report worksheet also
has a cover page, overview, etc., that I don't want affected by the filter.

I greatly appreciate your assistance.

Just select "NonBlanks" from the first drop down list. It should be the last entry.

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

EXCELLENT GLENN!

That solved the problem.

Many thanks!!!!
--
Bill


"Glenn" wrote:

Bill638 wrote:
Thanks Glenn,

I've uploaded it. The project URL is
http://www.savefile.com/projects/808763180 and the link to the file is
http://www.savefile.com/files/2093305. Note that the report worksheet also
has a cover page, overview, etc., that I don't want affected by the filter.

I greatly appreciate your assistance.

Just select "NonBlanks" from the first drop down list. It should be the last entry.