change cell value with SUBSTITUTE formula

Carica

Hello!

Trying to change the column (submitted by) values from user name to the actual persons name. I have:

IF([submitted by]@row="{users log in}", SUBSTITUTE ({first and last name}))

I'm getting no return value.

thank you

Paul Newcome

In that case, you would use an INDEX/MATCH.

=INDEX({Reference Sheet Name Column}, MATCH([Column2]@row, {Reference Sheet Match Column}, 0))

Paul Newcome

There is no way to access "current user" via formula.

Carica

thanks. I have a sheet that matches user name to actual name that i want to cross-reference, or link in the sheet where the name change or substitution is need.

Carica

i hope that my last replies clarifies.
im in the sheet, and i want to change the user value to the user full first,last name based on the existing sheet that has these values matched. My formula is above, and its not working. Please advise. Thanks!

Paul Newcome

Are you able to provide some screenshots for context? It sounds like you may need an INDEX/MATCH instead.

Carica

here: in column 3 i would like to have the first, last name. Column 4 and column 5 are a separate sheet, listing user names and their matching full name.

IMG_0830.png

Uploaded Jan 28, 2025266.33 KB

Paul Newcome

In that case, you would use an INDEX/MATCH.

=INDEX({Reference Sheet Name Column}, MATCH([Column2]@row, {Reference Sheet Match Column}, 0))

Carica

thats the correct formula. Thank you!

Paul Newcome

Happy to help. 👍️

Carica

hi @Paul Newcome, all of a sudden this formula is giving #no match return, for all values. Do you known what may be the cause? Thanks

Paul Newcome

It means that [Column2] is no longer found in the {Reference Sheet Match Column}.

Has anything changed with the source data?

Carica

ok, thanks! Nothing has changed. I copy pasted the sheet and the initial formula referenced, and it worked.
thank you!

Carica

@Paul Newcome on another note, what do you think about the drop down menu column on the form with 1000 selections. How to make the field on the form user friendly? Or searchable?

Thanks