| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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,"") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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,"") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I auto transform sets of data? (change "female" to "f") | Excel Discussion (Misc queries) | |||
| text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Auto Scale X axis doesn't work with "" values in cell | Charts and Charting in Excel | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Linear Mode
