workbook and sheet name via formula

liverlarson

Hi, I don't know if you ever solved your problem. I hope you did, but in case you didn't, this was my jurney to solve it.

I have now a working sollution for both web and non web Excel spreadsheets, unfortunatly the Formula it's in Portuguese. Most probably you won't understand it, but I put it here anyway. Maybe from one you get the other.

The basic thing you need to understand is that, the same formula in Excel offline and in excel online gives different result's. For this porpous A1 = "=Sheet1!A1"

The "FORMULATEXT(A1)" formula offline result in ='Sheet1'!A1 and online result in =Sheet1!A1 (exactly), so if you try to look for "'" online, it will give an error, but not offline, so this is usefull to use IFERROR() function, wish solved both problems. 

So, in general, offline you look for "'" and online you look for "!" (both are arround of what you want to know), first I looked for the Left Text of those, and after I took the right text except 1 or 2 chars.

My Formula (in portuguese excel) is this one:

=SE.ERRO(DIREITA(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1);NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1))-2);DIREITA(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1);NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1))-1))

Trying to decompress it looks like this:

=SE.ERRO(   #first part of error will work offline but not online

        DIREITA(

              ESQUERDA(

                    FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1

               )   #end of left

        ;

               NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1))-2

        )   #end of right

;   midle of iferror (second part work online)

        DIREITA(

               ESQUERDA(

                     FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1

               )      #end of left

        ;

               NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1))-1

        )     #end of right

)      #end of iferror

Translation of formulas probably looks like this (not sure..)

all ; must change to ,

SE.ERRO = IFERROR

DIREITA = RIGHT

ESQUERDA = LEFT

FÓRMULA.TEXTO = FORMULATEXT

NÚM.CARAT = LENGHT 

PROCURAR = FIND

(the main diference are the number of arguments that each function has in both languages, so you need to account for that.)

Copilot gave me this translation result:

To translate the Portuguese Excel formula to English, you can use the following formula:

=IFERROR(RIGHT(LEFT(FORMULATEXT(A1),FIND("'",FORMULATEXT(A1),3)-1),LEN(LEFT(FORMULATEXT(A1),FIND("'",FORMULATEXT(A1),3)-1))-2),RIGHT(LEFT(FORMULATEXT(A1),FIND("!",FORMULATEXT(A1),3)-1),LEN(LEFT(FORMULATEXT(A1),FIND("!",FORMULATEXT(A1),3)-1))-1))

This formula performs the same operations as the original Portuguese formula. It extracts the relevant portion of the formula based on the positions of single quotes (') and exclamation marks (!) within the text contained in cell A1.

Please note that this is an approximation, and it may not work correctly if the formula structure is different or if there are other special characters involved.

Hope it works for you! and thanks for reading untill here 🙂