if then... do this query.

vipa2000

In the below - column Principal Check I have put:

=if([Principal survey drawing required?]2="Yes",[Principal drawing validated?]="Awaiting response","Not Required")

I am wanting the Principal drawing validated? column to have an entry, "Awaiting response" based on Yes in column 'Principal survey drawing required?' column. I am using the Principal check column as the check column.

I am getting unparseable. I am not sure this is the best way as will the default data allow the user to type over with the answer yes once complete in column Principal drawing validated? Thanks in advance.

Brian Campbell

Hello Vipa,

Try this I added a second if statement, so if the principal survey drawing is required it will run the if statement. However replace the bold section to determine what the criteria to be for Principal Drawing validated to be.

=if([Principal survey drawing required?]2="Yes",if([Principal drawing validated?]2= Put what this needs to equal here,"Awaiting response","Not Required"))

vipa2000

Hi Michael, the response for some reason was being placed in 1. I looked at your text and then used

=IF([Principal survey drawing required?]2 = "Yes", [Principal drawing validated?]2 = "Awaiting response", "Not Required")

if 'yes' I get an answer, but in column 1 again.

If I explain again, just in case I failed in my explanation.

If column A = Yes, then column 1 checks for 'Yes' and says 'Awaiting response' in column 2. Can a user type a value in column 2 and it not be overwritten by the 'Awaiting response' text?

If column A = 'No', column1 checks for 'No' and puts 'Not required'

When I was writing the above I have realised that I could get away with just automating the 'Not Required', because the operative should see a blank. I could even use conditional formatting to colour blank cells red?

=IF([Principal survey drawing required?]2 = "Yes", [Principal drawing validated?]2 = "Awaiting response", "Not Required")

Brian Campbell

Hello Vipa,

I think I understand what you are getting at and don't believe you need column 1 or I am missing something which if so I am sorry. I believe in column 2 you can just use this formula and it will get you either answer =IF([Principal survey drawing required?]2 = "Yes", "Awaiting response", "Not Required") This way if Principal Drawing Required is Yes it will say Awaiting Response otherwise it will say Not Required. In addition for the principal check, if you need it can just be if IF([Principal survey drawing required?]2 = "Yes", 1, 0) because this is a checkbox field right? Also you can't lock an individual cell based on it being a particular value.

Hope this helps

vipa2000

Hi Michael. When somebody appends data, if column 2 has been overwritten i.e saying complete, then the equation won't copy down. I was trying use column 1 as a hidden column to do the checks and inform the user. Regards Paul

Brian Campbell

Paul,

Typically an equation will only auto update underneath if the 2 columns above it have the same formula. Depending on how the data is put in you could just carry down this formula so it will already be there and can be overwritten and still calculate other rows. However if these fields are filled out via forms this will not work.