Want to write a new formula that is based upon an existing formula

I have a file (FILE1) that has last-name (Col A), first-name (Col B), address (Col C).

I have a second file (FILE2) that is linked to FILE1 only in Col A.


Problem: Working only with FILE2, can a formula be written for Col B (first-name) and Col C (address) that is based off of the formula in Col A?? Sort of like asking if you can take the formula in FILE2 cell A1 and "change the A into a B" to get the first-name to populate in cell B1 ... and "change the A into a C" to get the address to populate in cell C1.

Thanks for your help!
Frank

P.S. couldn't get the files to attach??? so here's the issue in simplicity

FILE1:
COL A COL B COL C

SMITH BUD 228 MORING ST
JONES ANN 575 WATERS AVE
MAYES MAY 989 PALM CT



FILE2:
COL A COL B COL C

='FILE1'!$A$1 ? ?
='FILE1'!$A$2 ? ?
='FILE1'!$A$3 ? ?

First, no it does not have to be an absolute. Second, I know I can write a link to FILE1 for Col B and Col C formulas ... BUT is there a way to use the formula in Col A ('FILE1'!$A$1) to write a formula for Col B that says something like: =A1+ADD 1 Column ... (silly but the idea of saying =A1 and then add some formula text is what I'm trying to get to here). Thx!

Thank you for that lead MrShorty! I appreciate the help!

Frank

Unfortunately, the OFFSET function requires the other file to be open in order for it to work. It worked as I was hoping but won't work for me here .... I have 1000+ files that would need to stay open at all times. Plan B .. ???!

Again, thank you MrShorty. I will research the vlookup link that you have provided as soon as I can. Thank you!

MrShorty, the VLOOKUP function worked. Files don't need to be open and it updated the sheet as needed. Thank you for your help! Great suggestions!!