How can I enter data in a field that has a column formula?

Renee Dillard

I have a Contact list as a column type on sheet A and it is attached to my feeder sheet B. Sheet A also has a form attached to it and when a list option is selected, the contact list is populated on the sheet. However, if the list option is not selected, then the contact list does not populate.

I need to be able to fill in the contact list for manual entries as well as the automated ones. Is there a way to do this without having to have two separate spreadsheets? Let me know if more information is needed!

KPH

You can combine the two formula. Put the old one in to the new one in place of where I added the note in italicized text (“original formula to pull in name from property name”).

The IF function means if the first thing is true, evaluate the formula after the comma, if not, evaluate the last formula. The syntax for IF is

IF(thing to check, formula if thing is true, formula if thing is false)

So yours will be like this (you’ll need to change the column headings)

=IF([dropdown column name]@row=“general question or inquiry”, [manual name]@row, INDEX({Staff Name}, MATCH([Property Name]@row, {Property Name}, 0)))

If the first thing is true return the value in the column “manual name” . If false use the index formula that you already had.

KPH

You need the equals sign in here

[Purpose of your Inquiry]@row = “A general question or inquiry"

KPH

Hi

You cannot enter data into a field that has a column formula but you can amend the formula to check another column and return the value in the other column if the other column is not blank.

Your column formula would be something like this:

IF(ISBLANK([column that you enter manually]@row),original formula,[column that you enter manually]@row)

Hope this helps.

Renee Dillard

I am not sure i understand your suggestion.

maybe it will help if i fill in the gaps. STAFF NAME has a column formula attached to a feeder sheet that pulls the name of the staff member when PROPERTY NAME is selected. I added a column, with a drop down option of "general question or inquiry" so that PROPERTY NAME could be opted out of. However, if PROPERTY NAME is not provided, i still want to assign a STAFF NAME. is there a formula i could use to amend the row in STAFF NAME?

KPH

How will you assign a staff name when property name is not provided? If you create another column to enter it (called “manual name” for my example), then you could do something like this in the staff name column

=IF([dropdown column name]@row=“general question or inquiry”, [manual name]@row, original formula to pull in name from property name)

This says that if the dropdown option for general question is selected, the staff name is whatever is in the “manual name” column. If any other option is selected the staff name is found using the formula you already have (you need to paste that formula into the above in place of the italics). You will need to enter the correct column headings between the square brackets as well.

Renee Dillard

but there is already a column formula in the Staff Name column. Am I able to have two? or do i add to it? the current formula is:

=INDEX({Staff Name}, MATCH([Property Name]@row, {Property Name}, 0))

KPH

You can combine the two formula. Put the old one in to the new one in place of where I added the note in italicized text (“original formula to pull in name from property name”).

The IF function means if the first thing is true, evaluate the formula after the comma, if not, evaluate the last formula. The syntax for IF is

IF(thing to check, formula if thing is true, formula if thing is false)

So yours will be like this (you’ll need to change the column headings)

=IF([dropdown column name]@row=“general question or inquiry”, [manual name]@row, INDEX({Staff Name}, MATCH([Property Name]@row, {Property Name}, 0)))

If the first thing is true return the value in the column “manual name” . If false use the index formula that you already had.

Renee Dillard

Entering the correct column header names, my formula is

=IF([Purpose of your Inquiry]@row"A general question or inquiry", [Manual Staff Name Entry]@row, INDEX({Staff Name}, MATCH([Property Name]@row, {Property Name}, 0)) )

I am receiving an error that the syntax is still not right.

I entered this formula in the staff name column, combining the existing formula with the new.

KPH

You need the equals sign in here

[Purpose of your Inquiry]@row = “A general question or inquiry"

Renee Dillard

that was it, thank you!

KPH

great news! Glad I could help.