I have an Excel 2013 spreadsheet comprising twelve sheets named for the corresponding months starting with Apr (Apr, May, Jun. Jul, Aug etc). With the exception of Apr (the first sheet) each sheet needs to contain the following formula:
Formula:
=INDIRECT((TEXT($B$4-1,"mmm")&"!"&ADDRESS(MATCH("Target Item*",Jul!$E$5:$E$49,0)+4,5)))
It contains two references to data in the previous month (sheet).
The first reference finds the previous month's name using the formula segment
Formula:
(TEXT($B$4-1,"mmm"))
where $B$4 contains the date of the first of the current month. So far this has worked as expected.
I need to do the same thing for the second occurrence of the sheet name.
I have tried a straight substitution as follows:
Formula:
=INDIRECT((TEXT($B$4-1,"mmm")&"!"&ADDRESS(MATCH("Search String",(TEXT($B$4-1,"mmm"))!$E$5:$E$49,0)+4,5)))
which is rejected out of hand.
I have tried various forms of concatenation, wrapping various bits in double or single quotes and other variations, none of which work. They are either rejected completely or give #REF or #VALUE errors.
This formula, or one similar, is used several times on each of the twelve sheets and manually entering the sheet name is tedious beyond measure and a plentiful source of errors. Once it is finished I will be using the workbook as a template so there is a considerable saving in solving the problem.
Can anyone help please?
Bookmarks