# Break 1 formula in 3 cells



## hsandeep (Thursday at 9:07 PM)

E3 contains a formula. This formula is constructed using ‘*tab name*’; *Column*; *Row #*.
*Tab name*: Will always be of *1* digit
*Column*: Will always be of *1* digit
*Row#*: Will always be of *3* digit

*G2* I need *Tab name
H2* I need *Column
I2* I need *Row#*

How to accomplish?
Thanks in advance
Book2.xlsxEFGHI207H131Pr1Cell FormulasRangeFormulaE2E2='7'!H131


----------



## Phuoc (Thursday at 9:11 PM)

Try this

=INDIRECT("'"&G2&"'!"&H2&I2)


----------



## hsandeep (Thursday at 9:54 PM)

Phuoc said:


> Try this
> 
> =INDIRECT("'"&G2&"'!"&H2&I2)


No..I need formula/(s) to be inserted in the 3 output cells G2; H2 & I2 which can populate the desired results. Input cell is E2


----------



## Sufiyan97 (Yesterday at 3:51 PM)

Try

Book11ABCDEF107H131234Sheet2Cell FormulasRangeFormulaA1A1='7'!H131C1C1=MID(FORMULATEXT(A1),3,SEARCH("'!",FORMULATEXT(A1))-3)D1D1=MID(REPLACE(FORMULATEXT(A1),1,SEARCH("!",FORMULATEXT(A1)),""),1,AGGREGATE(15,6,SEARCH({1,2,3,4,5,6,7,8,9,0},REPLACE(FORMULATEXT(A1),1,SEARCH("!",FORMULATEXT(A1)),"")),1)-1)E1E1=MID(FORMULATEXT(A1),SEARCH(D1,FORMULATEXT(A1))+LEN(D1),999)


----------



## hsandeep (Yesterday at 9:57 PM)

Sufiyan97 said:


> Try
> 
> Book11ABCDEF107H131234Sheet2Cell FormulasRangeFormulaA1A1='7'!H131C1C1=MID(FORMULATEXT(A1),3,SEARCH("'!",FORMULATEXT(A1))-3)D1D1=MID(REPLACE(FORMULATEXT(A1),1,SEARCH("!",FORMULATEXT(A1)),""),1,AGGREGATE(15,6,SEARCH({1,2,3,4,5,6,7,8,9,0},REPLACE(FORMULATEXT(A1),1,SEARCH("!",FORMULATEXT(A1)),"")),1)-1)E1E1=MID(FORMULATEXT(A1),SEARCH(D1,FORMULATEXT(A1))+LEN(D1),999)


Thanks Sufiyan for your efforts & trying to help me. Unfortunately my Excel version is 2010 & it does not supports function FORMULATEXT.
Another formula which will support my Excel's version is needed


----------



## Peter_SSs (Today at 3:50 AM)

hsandeep said:


> Unfortunately my Excel version is 2010 & it does not supports function FORMULATEXT.
> Another formula which will support my Excel's version is needed


Then I think that you will need  vba user-defined function for your version. Here is one.


```
Function FormulaPart(r As Range, lPart As Long) As String
  Dim s As String
  
  s = r.Formula
  Select Case lPart
    Case 1: FormulaPart = Mid(s, 3, 1)
    Case 2: FormulaPart = Mid(s, 6, 1)
    Case 3: FormulaPart = Right(s, 3)
  End Select
End Function
```

hsandeep.xlsmEFGHI12abcd7H131303A101Pr1Cell FormulasRangeFormulaG2:I3G2=FormulaPart($E2,COLUMNS($G:G))E2E2='7'!H131E3E3='3'!A101


----------



## hsandeep (Today at 4:58 AM)

Peter_SSs said:


> Then I think that you will need  vba user-defined function for your version. Here is one.
> 
> 
> ```
> ...


Peter Sir,
As always, classic...works perfectly well.  Many thanks Sir for your help.
2 query:: I have a Function vba code in Module 1.  So I inserted Module 2 & pasted your code in Module 2; the Worksheet tab name is "*Pr"* in which your code has to perform actions & sheet is *Sheet90* (there are other worksheets also in this workbook).
*Query 1: Does your code needs some 'polishing' so that it is confined to perform actions in Worksheet "Pr" only?
Query 2: Will the 'action' of Module 1 code remain unaltered, its actions are on Worksheet with tab name "zzz"*?


----------



## Peter_SSs (Today at 5:10 AM)

hsandeep said:


> Does your code needs some 'polishing' so that it is confined to perform actions in Worksheet "Pr" only?


If you don't want it on other worksheets, then the simple solution is don't put it on other worksheets.



hsandeep said:


> *Will the 'action' of Module 1 code remain unaltered, its actions are on Worksheet with tab name "zzz"*?


I don't really follow what you are getting at bu my function has no impact on any other function.


----------



## hsandeep (Today at 6:41 AM)

Peter_SSs said:


> If you don't want it on other worksheets, then the simple solution is don't put it on other worksheets.
> 
> 
> I don't really follow what you are getting at bu my function has no impact on any other function.


The Worksheet in which your code has to perform its actions is with tab named "*Pr*" & sheet is *Sheet90* (there are other Worksheets also in this Workbook).
Your code is yielding the desired results in the “*Pr*” Worksheet & runs perfectly well.

My 3 queries are:
*Your code needs to be pasted in Module (COMPULSORYILY TO BE PASTED IN MODULE) or CAN be pasted as a Worksheet Code?

Does your code needs some MORE 'polishing' so that it gets ‘confined’ to perform its actions in Worksheet "Pr" only?

With this addition of Module2 *(your code has been currently pasted in Module2),* will the 'actions' of Module1’s code change? *(The code of Module1 performs its actions on Worksheet with tab name "*zzz*"). In this query 3, I am asking that *WILL* your code, which has been pasted in Module2, ‘*DISTURB, IF AT ALL*’, the actions of Module1’s code?


----------



## Peter_SSs (Today at 7:00 AM)

hsandeep said:


> Your code needs to be pasted in Module (COMPULSORYILY TO BE PASTED IN MODULE) or CAN be pasted as a Worksheet Code?


I don't know exactly what you are asking.



hsandeep said:


> Does your code needs some MORE 'polishing' so that it gets ‘confined’ to perform its actions in Worksheet "Pr" only?


Didn't I already answer that?



hsandeep said:


> *With this addition of Module2 *(your code has been currently pasted in Module2),* will the 'actions' of Module1’s code change? *(The code of Module1 performs its actions on Worksheet with tab name "*zzz*"). In this query 3, I am asking that *WILL* your code, which has been pasted in Module2, ‘*DISTURB, IF AT ALL*’, the actions of Module1’s code?


Didn't I already answer that?


----------



## hsandeep (Thursday at 9:07 PM)

E3 contains a formula. This formula is constructed using ‘*tab name*’; *Column*; *Row #*.
*Tab name*: Will always be of *1* digit
*Column*: Will always be of *1* digit
*Row#*: Will always be of *3* digit

*G2* I need *Tab name
H2* I need *Column
I2* I need *Row#*

How to accomplish?
Thanks in advance
Book2.xlsxEFGHI207H131Pr1Cell FormulasRangeFormulaE2E2='7'!H131


----------



## hsandeep (Today at 8:11 AM)

Peter_SSs said:


> I don't know exactly what you are asking.
> 
> 
> Didn't I already answer that?
> ...


Your quote: *I don't know exactly what you are asking*.

My answer:
*WHERE TO PUT THE VBA CODE*?

Undoubtedly, I needed a user defined function for my Excel’s version. But I am new to user defined function & its code.
Instead of pasting it in the “*Pr*” Worksheet (*Sheet90*), I inserted a new Module, Module 2 (since Module 1 was already present there with vba code which performs actions on Worksheet “*zzz*”).
I am asking: Is my pasting of your code in the Module *CORRECT*?

OR

To make the code run *specifically* on 1 Worksheet *ONLY* “*Pr*”, I *CAN* paste it in the Worksheet “*Pr*” using Alt + F11?

My Quote: Does your code needs some MORE 'polishing' so that it gets ‘confined’ to perform its actions in Worksheet "Pr" only?
Your quote: *Didn't I already answer that*?
My answer: For me, NO. Some extra code lines like Worksheets(“Sheetname”).Activate should be added or not in your code OR similar to such code lines required to be added or not in your present code?


----------



## Peter_SSs (47 minutes ago)

hsandeep said:


> Is my pasting of your code in the Module *CORRECT*?


Yes



hsandeep said:


> My answer: For me, NO. Some extra code lines like Worksheets(“Sheetname”).Activate should be added or not in your code OR similar to such code lines required to be added or not in your present code?


No extra lines are required. My code is a *function*. It works in whatever cell you put the function in. If you only want the function in sheet "Pr" then only write the formula in cells on that worksheet.


----------



## hsandeep (19 minutes ago)

Peter_SSs said:


> Yes
> 
> 
> No extra lines are required. My code is a *function*. It works in whatever cell you put the function in. If you only want the function in sheet "Pr" then only write the formula in cells on that worksheet.


Thanks Sir,
You are absolutely clear & I have no queries left. Many thanks for your help rendered. 😀


----------



## Peter_SSs (7 minutes ago)

You're welcome. Glad to help.


----------

