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.

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.

"Giantrobot" wrote:

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.

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.

"Toppers" wrote:

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.

"Giantrobot" wrote:

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.

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.

"Giantrobot" wrote:

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.

"Toppers" wrote:

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.

"Giantrobot" wrote:

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.