Experts:
I need some assistance with creating a dynamic lookup (I'm using "lookup" in a generic sense... doesn't have to be, e.g., a VLookup formula).
Here's what I have in my XLS:
- Data entry tab ("Step 0")... users enter n number of values in column A
- Lookup tab (Step 1")... I want the values in "Step 0!A2:A" to be reflected in the 2nd tab
Current challenge:
- Right now, I'm using a formula "=IF('Step 0 -- Data Entry'!A2<>"",'Step 0 -- Data Entry'!A2,"")" on the 2nd tab.
- However, if a row on the 1st tab is deleted, it results in the "#REF!" error. I want to avoid this error, so that users can make any changes (additions, deletion, modifications) w/o resulting in formula issues.
- Also, I'd really would prefer to NOT having to copying down the formula (on 2nd tab) 25 or 50 rows given the users may enter n number of unknown values on 1st tab. Instead, I'd rather have some dynamic process on the 2nd tab.
Please see attached XLS for details. I'm totally open to changing the "lookup" process on the 2nd tab. Merely want to achieve a method where I won't run into potential reference errors. How can this be achieved?
Thank you,
EEH
Bookmarks