Quick question version, I want to include a variable in a vlookup function to direct it to a specific worksheet.
Slightly longer version
Currently I have a pretty big dynamic spreadsheet. To simplify, let's say I have a list of 30,000 userid in a "master" workbook. Each user has a col indicating a certain status (Y/N). This status changes frequently (and by different people), so it has a separate workbook. I then do a simple vlookup by userid in the "master" workbook. I have a "current" sheet in that workbook that has the current Y/N status. Quarterly, I need to lock this status, so I create new sheet title 2019Q1, 2019Q2, etc, where I copy/paste the current sheet (and protect it).
My issue is that frequently, I need to recreate an output from my "master" workbook using a particular quarter's data. My current process is to do a find/replace, and change "current" with "2019Q1" or 2020Q1", etc. across the whole workbook After a lengthy recalculation, I do my outputs. It works fine, but seems clunky and slow. Is there a better way... maybe using the indirect function? Basically, I want to plug a 20XXQY variable in, and have all the vlookups be directed to a specific worksheet.
thanks in advance.
Slightly longer version
Currently I have a pretty big dynamic spreadsheet. To simplify, let's say I have a list of 30,000 userid in a "master" workbook. Each user has a col indicating a certain status (Y/N). This status changes frequently (and by different people), so it has a separate workbook. I then do a simple vlookup by userid in the "master" workbook. I have a "current" sheet in that workbook that has the current Y/N status. Quarterly, I need to lock this status, so I create new sheet title 2019Q1, 2019Q2, etc, where I copy/paste the current sheet (and protect it).
My issue is that frequently, I need to recreate an output from my "master" workbook using a particular quarter's data. My current process is to do a find/replace, and change "current" with "2019Q1" or 2020Q1", etc. across the whole workbook After a lengthy recalculation, I do my outputs. It works fine, but seems clunky and slow. Is there a better way... maybe using the indirect function? Basically, I want to plug a 20XXQY variable in, and have all the vlookups be directed to a specific worksheet.
thanks in advance.