Excel converts Macro into formula?

Question

Excel converts Macro into formula?

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Can I use a simple macro line in a formula without using the macro editor in VB?

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

I found something like this in a forum to get a value from an active cell, but from the error:

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

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

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

How do I proceed?

macro excel

asked by anonymous 04.07.2017 / 14:14

2 answers

GoogleMaps and Firebase in real time - Do not show the points RadioButton changes checked when scrolling Listview

score 0 · Answer 1

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

I explain:
Using the excel standard itself, the 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's a fact that the formula is extensive, but it's the only way I could identify it, I tested it here on a spreadsheet with the tabs (plan1, plan2, and plan3) and it worked perfectly.

Comments:-Forthistoworkthetabsmustbeorderedcorrectly,beingplan1followedbyplan2followedbyplan3.-Thissolutioncanalsoworkforothertabnamesthatfollowthe"text + sequence number" pattern, just change the formula.

score 0 · Answer 2

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

= CONCATENATE (IF (MONTH (RIGHT (CÉL ("filename"), 4), LEFT (CÉL ("filename"), 7), 2) -1; CONCATENATE ("0", MONTH (DATE (CÉL ("filename"), 4), LEFT (CÉL ("filename"), 7), 2) -1; 20 ))), MONTH (DATE (RIGHT (CEL ("filename"), 4), LEFT (RIGHT (CEL ("filename"), 7), 2) -1; (DATE (RIGHT (CÉL ("filename"), 4), LEFT (RIGHT (CÉL ("filename"), 7), 2) -1; 20))).

Now just insert the desired cell into the concat and leave for the hug! Thanks.