dynamic lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Giantrobot - 16 Jun 2006 23:20 GMT

I am setting up a model and in doing so I want to setup a dynamic lookup
command to streamline my work. My model consists of 3 separate workbooks.
Two of the workbooks serve as lookup tables (one for enrollment, and one for
residential development) to the the third workbook. Miguel helped me earlier
with a command for my residential lookup.

the command works fine and appears as:

{=INDEX('[res_development.xls]Saz
Summary'!$D$6:$D$711,MATCH(1,--('[res_development.xls]Saz
Summary'!$B$6:$B$711=1110823)*--('[res_development.xls]Saz
Summary'!$C$6:$C$711="SFD"),0))}

my enrollment command also works and looks like:

=LOOKUP("1110823",'[geo_stud_0607.xls]lookup
table'!$A$2:$A$361,'[geo_stud_0607.xls]lookup table'!$B$2:$B$361)

What I want to do is streamline the model and where it says "1110823" in my
formulas, I would instead like for it to reference cell I3. This way, when
I copy and paste my work sheets I can just change cell I3 and the sheet can
update with the proper information from the other workbooks. I attempted
this with my array formula and it worked for the first one, but once I copied
and pasted the page and attempted a new number it updated the corresponding
cells with "N/A". I also tried to use a VLOOKUP for my enrollment but it
also came back with an error.

the VLOOKUP appeared as so:

=VLOOKUP(I3,'[geo_stud_0607.xls]lookup table'!$A$2:$B$361,2,FALSE)

I can explain more thoroughly if need be. I'm a little new to these more
encompassing formulas and have been finding them a little tricky. I just
want to make my sheets more dynamic and whanted to know if this is possible.
Thank you all for your time.

Reply to this Message

Toppers - 16 Jun 2006 23:32 GMT

Try change I3 to $I$3 so that this a fixed (Absolute) reference i.e. doesn't
change if you copy/paste to other cells.

> I am setting up a model and in doing so I want to setup a dynamic lookup
> command to streamline my work. My model consists of 3 separate workbooks.
[quoted text clipped - 31 lines]
> want to make my sheets more dynamic and whanted to know if this is possible.
> Thank you all for your time.

Reply to this Message

Giantrobot - 16 Jun 2006 23:40 GMT

I've tried that. When trying to pull my enrollment it gives me the wrong
data, and when trying to pull my residential data it gives "N/A" still. I'm
not really cahnging cells either. All formulas are in the same place. I am
simply copying worksheets and want to change the data in cell I3 (reference
cell). Everything else stays put though.

> Try change I3 to $I$3 so that this a fixed (Absolute) reference i.e. doesn't
> change if you copy/paste to other cells.
[quoted text clipped - 34 lines]
> > want to make my sheets more dynamic and whanted to know if this is possible.
> > Thank you all for your time.

Reply to this Message

Toppers - 17 Jun 2006 00:11 GMT

If it worked BEFORE changing to I£, I find it difficult to see how it doesn't
work with I3. Is this the only difference? And did the new number exist ...
if not you will get an error.

> I've tried that. When trying to pull my enrollment it gives me the wrong
> data, and when trying to pull my residential data it gives "N/A" still. I'm
[quoted text clipped - 40 lines]
> > > want to make my sheets more dynamic and whanted to know if this is possible.
> > > Thank you all for your time.

Reply to this Message