Refs to named range produce #NAME? error in 365 online

Hi,

I am working with Excel 365 both locally as well as online (via Sharepoint). I am creating macro-enabled workbooks, and everything works on my local machine, but trying to get sheet names fails on the online version of excel, and I don't understand why.

I have tried two different ways to get Sheet Names:

One: using the formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Two: using a dynamic named range composed using =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),""), and then grabbing the sheet name using the INDEX() function on this named range.

Both work locally, both fail online.

I presume there is some issue with the online version of excel recognising the GET.WORKBOOK call, but I'm not sure what is breaking in the first option.

Any solution/workaround to this that will allow me to get sheet names on the online version of excel would be much appreciated.

Thanks

Hi Jeff,

Thanks for answering so quickly. OK, so the second option is a no-goer. Indeed, you are right, the first formula produces a #VALUE! error, not a #NAME? error. The

Please Login or Register  to view this content.

formula in a spare cell also produces a #VALUE! error on the online sheet.

You know, I have just realised that the people I am making this resource for can open excel files locally as well, so this is probably a problem I don't need to solve!

Colin

Well, it gets weirder... As I've been playing around with this, suddenly the dynamic range ones are working - it seems they just take ages to update in the online system? I'm finding the behaviour of the online version a little difficult to fathom. I think I'll just opt for a very visible warning on the cover page instructing users to open the file locally!

Thanks very much for your help.