Best formula to use for replacing data in a cell from referenced sheet

kp1313

Currently using referenced sheets to keep track of clients that are contacted, and what is discussed on those calls. Agents use a form to fill out cells in rows based on a client assigned number. The assigned number is what is used as reference for INDEX and MATCH formulas on another sheet, which is working fine. However, when a new contact is made and the form is used to document under the client assigned number on the referenced sheet, the child sheet updates all data in the row, instead of just updating the cells. Is there a formula combination that will still index, but instead of matching, would replace any cells that were updated instead?

Genevieve P.

Hi @kp1313

An INDEX(MATCH will take find the first match and return that value (depending on what you've put at the end of the MATCH... 0 or 1). This means that if your form adds a row to the top of the sheet, the MATCH will likely find the first row in the sheet to use as the reference. See: MATCH Function

If this isn't what you want, it would be useful to see a screen capture of both of your sheets, identifying what alternate data you're looking to have brought through to your other sheet (but please block out anything sensitive).

Cheers,

Genevieve

kp1313

Thanks for your reply, Genevieve. How I have it set up is that agents track all interactions on a call log (see "CallLog") and the input from that call log is matched to the datasheet (see "DataSheet"). What I would like to happen is that anytime a new entry for the matched client ID from the call log, it updates cells rather than have it be blank because nothing was input for those columns on the call log. Does that make sense?

CallLog:

DataSheet:

Genevieve P.

Hi @kp1313

So you would like the formula to check the Call Log and return the most recent cell that contains data (versus bringing in the most recent cell, which could potentially be blank).

What I would do in this instance is use an INDEX(COLLECT instead of MATCH. This way you can use the Collect function like a filter, clarifying you need the matching row where the cell is Not Blank or <> ""

Try something like this:

=INDEX(COLLECT({Overview}, {PropNo}, [Value for Property]@row, {Overview}, <> ""), 1)

I would suggest using an IFERROR around your formulas as well. That way instead of the NOMATCH error you could have it be blank or "n/a".

=IFERROR(INDEX(COLLECT({Overview}, {PropNo}, [Value for Property]@row, {Overview}, <> ""), 1), "N/A")

Cheers,

Genevieve

kp1313

Wonderful. Thank you so much!!