If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel formula question
I am creating an audit tool that I want to be able to automatically populate
a report with recommendations. I have a Yes/No/NA cell in my checklist worksheet that when the NO option is selected it will automatically populate the corresponding recommendation in the report worksheet. The code "=IF(Checklist!C20="NO",Checklist!D20)" is used in the report worksheet to make that work. My problem is that when the YES or NA options are selected on the checklist worksheet, the applicable row in the report worksheet remains, but with the word "FALSE" in the columns that would normally contain the corresponding recommendation and the reference data. How do I configure the report worksheet to totally ignore the corresponding question on the checklist worksheet when the YES or NA options are selected? If I'm not making myself clear, I'll be happy to e-mail the file to you so that you can see exactly what I'm talking about. Many thanks to anyone who can help me. -- Bill |
#2
|
|||
|
|||
Excel formula question
Maybe...
=IF(Checklist!C20="NO",Checklist!D20,"") I added the ELSE portion to your =IF() function. Bill638 wrote: I am creating an audit tool that I want to be able to automatically populate a report with recommendations. I have a Yes/No/NA cell in my checklist worksheet that when the NO option is selected it will automatically populate the corresponding recommendation in the report worksheet. The code "=IF(Checklist!C20="NO",Checklist!D20)" is used in the report worksheet to make that work. My problem is that when the YES or NA options are selected on the checklist worksheet, the applicable row in the report worksheet remains, but with the word "FALSE" in the columns that would normally contain the corresponding recommendation and the reference data. How do I configure the report worksheet to totally ignore the corresponding question on the checklist worksheet when the YES or NA options are selected? If I'm not making myself clear, I'll be happy to e-mail the file to you so that you can see exactly what I'm talking about. Many thanks to anyone who can help me. -- Bill -- Dave Peterson |
#3
|
|||
|
|||
Excel formula question
Hi Dave,
When I added the "" to the formula I received an error message. -- Bill "Dave Peterson" wrote: Maybe... =IF(Checklist!C20="NO",Checklist!D20,"") I added the ELSE portion to your =IF() function. Bill638 wrote: I am creating an audit tool that I want to be able to automatically populate a report with recommendations. I have a Yes/No/NA cell in my checklist worksheet that when the NO option is selected it will automatically populate the corresponding recommendation in the report worksheet. The code "=IF(Checklist!C20="NO",Checklist!D20)" is used in the report worksheet to make that work. My problem is that when the YES or NA options are selected on the checklist worksheet, the applicable row in the report worksheet remains, but with the word "FALSE" in the columns that would normally contain the corresponding recommendation and the reference data. How do I configure the report worksheet to totally ignore the corresponding question on the checklist worksheet when the YES or NA options are selected? If I'm not making myself clear, I'll be happy to e-mail the file to you so that you can see exactly what I'm talking about. Many thanks to anyone who can help me. -- Bill -- Dave Peterson |
#4
|
|||
|
|||
Excel formula question
Dave,
My error :-)....I forgot the comma. So now the "FALSE" no longer is displayed, but the row still remains with four empty columns. So your recommendation got rid of the "FALSE" notation...but I'd really like it so that the report doesn't even contain the row. Is there a way to do that? Thanks again. -- Bill "Bill638" wrote: Hi Dave, When I added the "" to the formula I received an error message. -- Bill "Dave Peterson" wrote: Maybe... =IF(Checklist!C20="NO",Checklist!D20,"") I added the ELSE portion to your =IF() function. Bill638 wrote: I am creating an audit tool that I want to be able to automatically populate a report with recommendations. I have a Yes/No/NA cell in my checklist worksheet that when the NO option is selected it will automatically populate the corresponding recommendation in the report worksheet. The code "=IF(Checklist!C20="NO",Checklist!D20)" is used in the report worksheet to make that work. My problem is that when the YES or NA options are selected on the checklist worksheet, the applicable row in the report worksheet remains, but with the word "FALSE" in the columns that would normally contain the corresponding recommendation and the reference data. How do I configure the report worksheet to totally ignore the corresponding question on the checklist worksheet when the YES or NA options are selected? If I'm not making myself clear, I'll be happy to e-mail the file to you so that you can see exactly what I'm talking about. Many thanks to anyone who can help me. -- Bill -- Dave Peterson |
#5
|
|||
|
|||
Excel formula question
You may find that it's easier to keep the data on a single sheet, then use
Data|Filter|autofilter (xl2003 menus) to show/hide the data. But if you want, you may want to look at Chip Pearson's site for arbitrary lookups: http://www.cpearson.com/excel/TablesAndLookups.aspx (look for Arbitrary Lookups) Bill638 wrote: Dave, My error :-)....I forgot the comma. So now the "FALSE" no longer is displayed, but the row still remains with four empty columns. So your recommendation got rid of the "FALSE" notation...but I'd really like it so that the report doesn't even contain the row. Is there a way to do that? Thanks again. -- Bill "Bill638" wrote: Hi Dave, When I added the "" to the formula I received an error message. -- Bill "Dave Peterson" wrote: Maybe... =IF(Checklist!C20="NO",Checklist!D20,"") I added the ELSE portion to your =IF() function. Bill638 wrote: I am creating an audit tool that I want to be able to automatically populate a report with recommendations. I have a Yes/No/NA cell in my checklist worksheet that when the NO option is selected it will automatically populate the corresponding recommendation in the report worksheet. The code "=IF(Checklist!C20="NO",Checklist!D20)" is used in the report worksheet to make that work. My problem is that when the YES or NA options are selected on the checklist worksheet, the applicable row in the report worksheet remains, but with the word "FALSE" in the columns that would normally contain the corresponding recommendation and the reference data. How do I configure the report worksheet to totally ignore the corresponding question on the checklist worksheet when the YES or NA options are selected? If I'm not making myself clear, I'll be happy to e-mail the file to you so that you can see exactly what I'm talking about. Many thanks to anyone who can help me. -- Bill -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
Excel formula question
Thanks Dave. Greatly appreciate your assistance.
-- Bill "Dave Peterson" wrote: You may find that it's easier to keep the data on a single sheet, then use Data|Filter|autofilter (xl2003 menus) to show/hide the data. But if you want, you may want to look at Chip Pearson's site for arbitrary lookups: http://www.cpearson.com/excel/TablesAndLookups.aspx (look for Arbitrary Lookups) Bill638 wrote: Dave, My error :-)....I forgot the comma. So now the "FALSE" no longer is displayed, but the row still remains with four empty columns. So your recommendation got rid of the "FALSE" notation...but I'd really like it so that the report doesn't even contain the row. Is there a way to do that? Thanks again. -- Bill "Bill638" wrote: Hi Dave, When I added the "" to the formula I received an error message. -- Bill "Dave Peterson" wrote: Maybe... =IF(Checklist!C20="NO",Checklist!D20,"") I added the ELSE portion to your =IF() function. Bill638 wrote: I am creating an audit tool that I want to be able to automatically populate a report with recommendations. I have a Yes/No/NA cell in my checklist worksheet that when the NO option is selected it will automatically populate the corresponding recommendation in the report worksheet. The code "=IF(Checklist!C20="NO",Checklist!D20)" is used in the report worksheet to make that work. My problem is that when the YES or NA options are selected on the checklist worksheet, the applicable row in the report worksheet remains, but with the word "FALSE" in the columns that would normally contain the corresponding recommendation and the reference data. How do I configure the report worksheet to totally ignore the corresponding question on the checklist worksheet when the YES or NA options are selected? If I'm not making myself clear, I'll be happy to e-mail the file to you so that you can see exactly what I'm talking about. Many thanks to anyone who can help me. -- Bill -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|