Edit cells with column formula

Jef Snyders

Is there a way to edit the cells where a column formula is used?

I am trying to link information from 1 database to another via a INDEX MATCH formula, so we don't have to re-write info 2 times.

However, the databases does not contain the same projects (we link info based on project number).

See below - Project name is filled out based on exact match with Project Number (on a different sheet). However, a lot of PRJ numbers are not matching so we would like to edit the cell for Project Name where it says '#NO MATCH'. At this point, I am not able to edit cells where a column formula is used. How can I edit them?

Marlana K.

@Jef Snyders That is a tough one! I have experienced the same thing and have had to set the formula to a cell formula allowing me to remove the formula anytime there is a #NO MATCH. Unless there is someplace else that houses the information then you could use IFERROR/INDEX/MATCH/INDEX/MATCH.

I track all of my "Smartsheet Solutioning" Request that I am working on, my project plan! We use INTAKE TO ARCHIVE process for both New Solution Request and Change Request for Legacy Solutions.

Below I am looking at our INTAKE for Change Request to a Smartsheet Solution matching the Request ID if it cannot find it (IFERROR) then it will INDEX/MATCH using the ARCHIVE CHANGE REQUEST sheet.

=IFERROR(INDEX({INTAKE: CHANGE REQUEST SS Existing Solution Name}, MATCH([Request ID]@row, {INTAKE: CHANGE REQUEST SS RequestID}, 0), 1), INDEX({ARCHIVE: CHANGE REQUEST SS Existing Solution Name}, MATCH([Request ID]@row, {ARCHIVE: CHANGE REQUEST SS Request ID}, 0), 1))

Hope this helps!

Marlana K.