Keeping a constant cell reference through editing

[mduddridge]

I am sure the answer to this is simple, but I have searched trie
Excel's Help, the NG's [via Google] and the Forums here
excelforum.com and can't seem to find the right information.

I am currently using Excel to produce sheets which accompany th
documents our Auditors ship to storage, (we call these Transmittals).

Basically, I want a formula in one cell, (part of a number of row
repeated at the top of each page), to refer to a cell no matter what
may do to the worksheet as each Auditor's list is taken from a Maste
which I edit accordingly, (i.e. I delete any irrelevant rows).


Now for more explanation, which will probably be too long and/o
confusing to be of any use!

The Worksheet "Master" contains info for Auditors A & B. The formul
is in place and "Master" is copied twice. Each Worksheet is rename
for each Auditor. In "A" all of B's info is deleted & the cell stil
shows A for the auditor's name. In "B", A's info is removed and th
cell shows #REF!.

[Through a previous post] I currently, I have the formul
=IF(ISNA(VLOOKUP(E14,'[Transmittal Insert.xls]Audito
List'!A2:B11,2,0)),"Auditor's nam
missing...",(VLOOKUP(E14,'[Transmittal Insert.xls]Audito
List'!A2:B11,2,0))) in the cell that shows the Auditor's name, (E1
being the cell where the Username appears). I have tried changing th
references to E14 to $E$14, but I still get the #REF! result and, i
the formula, the E14/$E$14 references in the formula is replaced w
REF! :confused

 

[Frank Kabel]

Hi
not quite sure but try
=IF(ISNA(VLOOKUP(INDIRECT("E14"),'[Transmittal Insert.xls]Auditor
List'!A2:B11,2,0)),"Auditor's name
missing...",(VLOOKUP(INDIRECT("E14"),'[Transmittal Insert.xls]Auditor
List'!A2:B11,2,0)))

--
Regards
Frank Kabel
Frankfurt, Germany

I am sure the answer to this is simple, but I have searched tried
Excel's Help, the NG's [via Google] and the Forums here @
excelforum.com and can't seem to find the right information.

I am currently using Excel to produce sheets which accompany the
documents our Auditors ship to storage, (we call these Transmittals).

Basically, I want a formula in one cell, (part of a number of rows
repeated at the top of each page), to refer to a cell no matter what I
may do to the worksheet as each Auditor's list is taken from a Master
which I edit accordingly, (i.e. I delete any irrelevant rows).

confused:

Now for more explanation, which will probably be too long and/or
confusing to be of any use!

The Worksheet "Master" contains info for Auditors A & B. The formula
is in place and "Master" is copied twice. Each Worksheet is renamed
for each Auditor. In "A" all of B's info is deleted & the cell still
shows A for the auditor's name. In "B", A's info is removed and the
cell shows #REF!.

[Through a previous post] I currently, I have the formula
=IF(ISNA(VLOOKUP(E14,'[Transmittal Insert.xls]Auditor
List'!A2:B11,2,0)),"Auditor's name
missing...",(VLOOKUP(E14,'[Transmittal Insert.xls]Auditor
List'!A2:B11,2,0))) in the cell that shows the Auditor's name, (E14
being the cell where the Username appears). I have tried changing the
references to E14 to $E$14, but I still get the #REF! result and, in
the formula, the E14/$E$14 references in the formula is replaced w/
REF!

 

[mduddridge]

Thanks, again, Frank. Your solution works perfectly. :