Background
The U.S. government releases a daily report I use for work. Recently, the government changed how they release their report. Instead of releasing the report as a text file, they are now releasing it as pdf. Unfortunately, this has affected how I pull information from the report. Previously, I pulled the information from the site as it was saved as a text file. Now, I download the pdf and convert it to an excel file.
I have 1 workbook that contains saved copies of the report on individual worksheets, e.g. April 30, April 29, April 28, etc. Unfortunately, due to the conversion, the information on each page can vary by a row or two, e.g. Information_A on April 30 worksheet could be on row 30 and on April 29 it could be on row 33.
On the first page of the workbook, I have a summary page which pulls key information from the previous page, e.g. Summary would pull information from the April 30 worksheet.
Problem
The problem now comes from how I pull the information from the excel file. Since the information is not on the same row, I thought the Index/Match method would be best. The formula I made is below. The reference name, Information_A, is in column A and the value I need from Information_A is in column P.
=(INDEX('April 30'!P:P,MATCH("Information_A",'April 30'!A:A,0)))
Since my worksheets constantly change I thought I would be able to pull the last worksheet name and then input it into the formula like so,
=(INDEX('[previous_sheet]'P:P,MATCH("Information_A",'[previous_sheet]'!A:A,0)))
So I tried to figure out how to pull the previous sheet name. I tried a few different ways and ended up using ChrisB's solution here.
I had saved the formula he provided (below) as PrevSheet_2
=IF( MATCH(wsName,wsNamesArray,0)-1 =0, ERROR.TYPE(7), INDEX(wsNamesArray,MATCH(wsName,wsNamesArray,0)+1))
Where, wsNamesArray and wsNameare as follows,
wsNamesArray: =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) & T(NOW())
wsName: =MID(CELL("filename", INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,255) & T(NOW())
Which resulted in the 'final' formula of,
=(INDEX("'"&PrevSheet_2&"'!P:P",MATCH(Information_A","'"&PrevSheet_2&"'!A:A",0)))
which doesn't work. This formula gives me a #VALUE! error.
I tested the formula without using PrevSheet_2 and typed in the worksheet name, e.g. April 30, and got the correct value, so I know that part is working. I also test PrevSheet_2 in a cell and got the correct previous sheet as well, so I know the problem lies in the combination of the 2.
My question, then, is how to make this
=(INDEX("'"&PrevSheet_2&"'!P:P",MATCH(Information_A","'"&PrevSheet_2&"'!A:A",0)))
formula work.
I have a feeling PrevSheet_2 is in the wrong data type however I don't know how to fix it.
EDIT:
I have also tried using INDIRECT (below) and got a #REF! error.
=INDEX(INDIRECT("''"&PrevSheet_2 &"'!P:P"),MATCH("Information_A",INDIRECT("'"&PrevSheet_2&"'!A:A"),0))
