using INDIRECT with VLOOKUP

[Msgjazz]

my current formula is
=VLOOKUP($A5,'[2017-07 - Caseload Listing Details.xlsx]Caseload Listing Detail'!$B$2:$G$34498,6,FALSE).

I would like for the "2017-07" to refer to any month that is entered in cell M2.

So if M2 changers to 2017-09 then the formular would read
=VLOOKUP($A5,'[2017-09 - Caseload Listing Details.xlsx]Caseload Listing Detail'!$B$2:$G$34498,6,FALSE).

Can't get the INDIRECT and CONCATENATE sequence right.

Thanks

 

[Greg Truby]

... I've also extended the range to 37000 which is more that the max in any given month....

If you're 100% certain that will be adequate, then you can just go with that. If possible, I would see if I could find out how many rows this data set has had for the past twelve to thirty-six months so I could forecast a reasonable ceiling for the next three to five years.

When naming it, would I just specify the columns B:G?

No. You would define the "refers to" to be the range in question, i.e. $B$2:$G$35000 or whatever it is. However, do note that this name would need to be defined in every copy of the Caseload Listing Details workbook that might be used with this formula. If that would be challenging the just oversize the address and call it good.

 

[Msgjazz]

Doesn't seem to be working.

 

[Msgjazz]

The MOREFUNC doesn't seem to work with Excel 2016 and 64bit PC. Get error messages each time I open a new book. I guess I go back to Plan B of manual update???

Thanks

 

[Greg Truby]

The MOREFUNC doesn't seem to work with Excel 2016 and 64bit PC.

OS wouldn't matter; but 32 v. 64-bit Excel itself does matter. If you look down a bit on the download site you'll see that the add-in does not work in 64-bit Excel.

I guess I go back to Plan B of manual update???

You can use VBA to make the process invisible to the user. But if you wish to reference an external data set, it would need to be (a) just one data set, i.e. you could create an Access table with every month's data and then query it or (b) you'll need to use VBA to either change the external reference that the formulas points to.

 

[Jonmo1]

You should also be able to use the Edit Links function on the Data Tab to change those references.

 

[Greg Truby]

You should also be able to use the Edit Links function on the Data Tab to change those references.

Gadzooks! It's been so long since I've used that button I'd utterly forgotten the darned thing even existed.

Edit - it looks like the VBA method is .ChangeLink for the workbook object. So it looks like one ought to be able to trap a Change event on the M2 and fix things that-a-way.

 

[Msgjazz]

https://mecktech-my.sharepoint.com/:f:/g/personal/jose_rivera_mecklenburgcountync_gov/EmEvbYthAAJOkbESGe7O0JwBt8_PX42T4xL0gJTQU1MPpA?e=Zldjn0Thank you all for your help. I've been out for a while. I have a different question. I'm trying to do a multiple IFS statement where it matches two cell in cell BF4

=IF(OR(E4=BF3,F4=BO3)*1,OR(E4=BF3,F4=BO4)*1,OR(E4=BF3,F4=BO5)*1,OR(E4=BF3,F4=BO6)*1,)
E F BF BO
3 ENF HELB
4 ENF HOSC 0 HOSC
5 HOTH
HRAD
HREV
HRFS

So If both E4 (ENF) matches BF3 (ENF) and F4 matches anything in BO3-BO11 (HELB, HOCS...) than I should get a 1, otherwise 0.

Thanks