Forum Discussion
Referencing a cell using static column and generic row values
I need a formula that will generate a cell address by matching a static column value with a dynamic row value.
The Kind Of Solution I believe I Am Looking For (that doesn't work just yet, but you get the gist):
=IF(H+(ROW())="LOCATION 1","EMAIL ADDRESS 1","")
=IF(H(ROW())="LOCATION 1","EMAIL ADDRESS 1","")
=IF(H+ROW()="LOCATION 1","EMAIL ADDRESS 1","")
The formula needs to be completely generic (assuming a static column), as it is being applied to the cell from an outside source (Flow). If you are familiar with Smartsheet at all, this would be accomplished with mailto:H@row.
Thank you for your help.
Ben72 It sounds an awful lot like there might be a sheet in your file with a list of addresses and emails associated. If that is the case then a VLOOKUP might work.
Genericish:
=VLOOKUP("H" & ROW(), <Sheet with addresses>!<Range of address columns and rows (EG. A1:A55)>, col_index_num (the column the email address is in, EG. 2), FALSE (exact Match Only))
so as a made up example if on sheet2 column A there are Rows A1:A55 with addresses and B1:B55 with associated emails:
=VLOOKUP("H"&ROW(),Sheet2!A1:A55,2,FALSE)
4 Replies
Ben72
I am not 100% sure the ultimate goal, but perhaps: =IF(MATCH("LOCATION 1",H:H,0),"EMAIL ADDRESS 1","")Maverick494 , that got me closer.
What I will end up with, I hope, is a column of locations with the various email addresses fro those locations in the next column. The formula in the email address column will be an exhaustive list of the locations. The email for each location will have a different address as teh other locations, but always the same for that location. I need to have this formula abstracted from the sheet because it will be added through a Form and a Flow.
Of course, what the formula you suggested will do is check to see if LOCATION 1 appears anywhere in H and, if so, add Email Address 1 to the email column. I need to check if LOCATION 1 appears in column H, but only in the same row that the formula appears.
I hope that better explains what I am hoping for, and sparks the solution I am dreaming of!
Ben72 It sounds an awful lot like there might be a sheet in your file with a list of addresses and emails associated. If that is the case then a VLOOKUP might work.
Genericish:
=VLOOKUP("H" & ROW(), <Sheet with addresses>!<Range of address columns and rows (EG. A1:A55)>, col_index_num (the column the email address is in, EG. 2), FALSE (exact Match Only))
so as a made up example if on sheet2 column A there are Rows A1:A55 with addresses and B1:B55 with associated emails:
=VLOOKUP("H"&ROW(),Sheet2!A1:A55,2,FALSE)
