Accessing sheets prefixed with ^ sign using indirect function

[govindappa]

Hello,

I have an excel file consisting of about 50 worksheets which contain raw data. About 10 of them have names prefixed with a ^ sign. For example: ^Sheet3, ^Sheet4 etc. The remaining 40 sheets are named without any special prefixes.

In the summary worksheet, I have a column with the names of all the worksheets in column A and I use the indirect() function to access data from other sheets.

For example: to calculate the max value in the J column of the data sheets, I use this formula in the summary sheet:

Col A Col B
Sheet1 =MAX(INDIRECT(""&$A2&"!J:J"))
Sheet2 = MAX(INDIRECT(""&$A3&"!J:J"))
^Sheet3 = MAX(INDIRECT(""&$A4&"!J:J"))

This seems to work for Sheet1 and Sheet2 but not for ^Sheet3 ( it gives #REF! error).

If it were up to me, I would have not put the ^ sign in the first place, but as things stand, it is unavoidable.

Could someone please correct the formula so that ^ sign is taken care of?

Thanks in advance.

 

[Jonmo1]

Try

=MAX(INDIRECT("'"&$A4&"'!J:J"))

 

[govindappa]

It works. Thanks a lot!

 

[Jonmo1]

Glad to help, thanks for the feedback.