Does Excel convert Macro into formula?

Asked

Viewed 429 times

0

Without using the macro editor in VB it is possible to use a simple macro line in a formula?

I want to get a value of a cell always from the tab to the left of the active tab, as this could be done only via formula?

I found something more or less like this in a forum to get a value of an active cell, but error:

=Sheet1:[this.ActiveSheet.name] this.ActiveSheet!T42)

The ideal was to say to get from a spreadsheet to the left of the active a certain value of a cell.

Note: The company where I work blocks spreadsheets with macros.

How shall I proceed?

excel macro fórmula

  • Can’t use macros, and nothing from VBA? Or only Macros? For example, could create a function.

  • Friend, I was in doubt when exactly what you need... "get a cell value always from the tab to the left of the active tab" Can’t use a fixed reference? Tabs vary in position on your spreadsheet?

  • really not. When creating a new tab the tab reference is lost, that is, it does not correspond to the previous tab.Example: Tab 3 and the cell reference refers to tab 2!C4. If I copy it the reference is maintained and should be added. Ai creates Aba 4 with reference tab 2!C4 and should be tab 3!C4. understood?

2 answers

0

The only possible way to do this without using VBA is if the file follows a pattern in the type tab names texto + número sequencial.

Explain:
Using the excel standard itself, tabs have the following name:
first tab: plan1
second tab: plan2
third tab: plan3

In this case the following formula can be used, using the functions INDIRETO(), EXT.TEXTO(), DIREITA(), CÉL(), NÚM.CARACT() and LOCALIZAR():

Function:

=INDIRETO("plan"&EXT.TEXTO(DIREITA(CÉL("nome.arquivo");NÚM.CARACT(CÉL("nome.arquivo"))-LOCALIZAR("]";CÉL("nome.arquivo");1));LOCALIZAR("n";DIREITA(CÉL("nome.arquivo");NÚM.CARACT(CÉL("nome.arquivo"))-LOCALIZAR("]";CÉL("nome.arquivo");1));1)+1;NÚM.CARACT(DIREITA(CÉL("nome.arquivo");NÚM.CARACT(CÉL("nome.arquivo"))-LOCALIZAR("]";CÉL("nome.arquivo");1)))-LOCALIZAR("n";DIREITA(CÉL("nome.arquivo");NÚM.CARACT(CÉL("nome.arquivo"))-LOCALIZAR("]";CÉL("nome.arquivo");1));1)+1+1)-1&"!A1")

It is a fact that the formula is extensive, but it is the only way I could identify, I tested here in a spreadsheet with tabs (plan1, plan2 and plan3) and it worked perfectly.
inserir a descrição da imagem aqui

Comments: - For this to work the tabs must be sorted correctly, being plan1 followed by plan2 followed by plan3. - This solution can also work for other tab names that follow the pattern "text + sequential number", just change the formula.

  • This line of reasoning that I traced helped me a lot to adapt in what I need, without using macro.

0

This line of reasoning that I traced helped me to adapt in what I need, without using macro. I have tabs in the spreadsheet with month and year in the format mm aaaa, thus, to always bring a value systematically from the previous spreadsheet just ask the month and year before, staying so

=CONCATENATE(SE(MONTH(DATE(RIGHT(CÉL("name.file");4);LEFT(RIGHT(CÉL("name.file");7);2)-1;20)<10;CONCATENATE("0";MONTH(DATE(RIGHT("name.file");4);LEFT(RIGHT("name.file");7);2)-1;20)));MONTH(DATE(RIGHT("name.file");4);(RIGHT(CÉL("name.file");7);2)-1;20));" ";YEAR(DATE(RIGHT(CÉL("name.file");4);LEFT(RIGHT(CÉL("name.file");7);2)-1;20))).

Now just insert the desired cell into the Concat and go pro abraco! thanks.

Browser other questions tagged excel macro fórmula

You are not signed in. Login or sign up in order to post.