Forum Discussion
Referencing a cell using static column and generic row values
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)
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)Better if to show small sample file since not everything is clear. For example what H is in your formulas? - cell, column, some name? etc.
In general, if you'd like to have a clickable link on email address that's like
=HYPERLINK("mailto:" & IF(H11&" "&ROW()="LOCATION 11","a@b.c",""),"LOCATION 11")On the other hand if you use Flow you may return email address directly by it using HTML format for output.
