Hi everyone
I hope you can help, please.
Every month, I create a spreadsheet which has the same naming convention e.g "Name 202101" for January, "Name 202102" for February and so on. The format and layout stays the same, the data is just updated.
I then have a master sheet which I want to keep by updating and appending the data that I look up from each of these sheets every month.
I have created the following as per my screenshot:
1. Column A contains the names I want to look up from the sheet that I open
2. You will see I created columns and basically created the reference for the sheet name for each month that I can use with an INDIRECT function.
3. I have created code to open the sheet based on the YYYYMM input I select in cell B1
The issue is, because the formula in column J has an INDIRECT function, I can't just copy it across to all months and have it update when I open the respective sheet, I get #REF errors. So what I was thinking of doing is open the respective sheet first as step 1 and then copy the formula from column J across to the right destination column as per the input in B1 which will then (if the right sheet is open) allow the data to update and pull from the sheet that is opened.
This might definitely not be the most eloquent solution but this is what I've constructed. The basis of it all is that I want to open a sheet based on a date that I select, have certain fields in column A being looked up in that sheet and pasted in the right column in the time series.
Hope it makes sense. I have attached a screenshot.
Thanks in advance!
I hope you can help, please.
Every month, I create a spreadsheet which has the same naming convention e.g "Name 202101" for January, "Name 202102" for February and so on. The format and layout stays the same, the data is just updated.
I then have a master sheet which I want to keep by updating and appending the data that I look up from each of these sheets every month.
I have created the following as per my screenshot:
1. Column A contains the names I want to look up from the sheet that I open
2. You will see I created columns and basically created the reference for the sheet name for each month that I can use with an INDIRECT function.
3. I have created code to open the sheet based on the YYYYMM input I select in cell B1
The issue is, because the formula in column J has an INDIRECT function, I can't just copy it across to all months and have it update when I open the respective sheet, I get #REF errors. So what I was thinking of doing is open the respective sheet first as step 1 and then copy the formula from column J across to the right destination column as per the input in B1 which will then (if the right sheet is open) allow the data to update and pull from the sheet that is opened.
This might definitely not be the most eloquent solution but this is what I've constructed. The basis of it all is that I want to open a sheet based on a date that I select, have certain fields in column A being looked up in that sheet and pasted in the right column in the time series.
Hope it makes sense. I have attached a screenshot.
Thanks in advance!
