Separating Added Numbers from Multiple Tabs

[littlefish]

I have a 50-worksheet workbook from a client that includes specific data within a single cell on each worksheet that I would like to extract.

Cell B10 contains three numbers which are added together (ex. "=895+1023+9974".)

On a new worksheet, I would like to show three columns by fifty rows of the individual numbers in B10 from the original worksheets:

895 1023 9974

Any help is greatly appreciated.

Thanks,

Little Fish

 

[tyija1995]

Hey,

This will require a mixture of VBA and Excel but I think this is what you're after:::

Step 1: Create a "MasterSheet" tab and put it at the END (far right tab) of the workbook.
Step 2: Add this VBA code to a new module:

Sub TabNames()
    Dim i As Integer
    Dim numSheets As Integer
    Sheets("Mastersheet").Activate
    numSheets = ActiveWorkbook.Worksheets.Count
    For i = 1 To numSheets - 1
        Cells(i, 1).Value = Sheets(i).Name
    Next i
End Sub

Step 3: Run the code - you should see the tab names listed from A1:A50

Step 4: In cell B1 enter this formula and drag it down B,C & D columns for all 50 tabs:
=SUBSTITUTE(MID(SUBSTITUTE(FORMULATEXT(INDIRECT($A1&"!$B$10")),"+",REPT("+",LEN(FORMULATEXT(INDIRECT($A1&"!$B$10"))))),(COLUMN()-2)*LEN(FORMULATEXT(INDIRECT($A1&"!$B$10")))+1,LEN(FORMULATEXT(INDIRECT($A1&"!$B$10")))),"+","")

Step 5: Clean the B column as it will contain an equals sign, you can do this multiple ways, probably the easiest way is to enter in column E as
E1: =SUBSTITUTE(B1,"=","")
then drag down to E50, copy and hard paste in to the B column.

EDIT: Changed B1 formula as didn't update INDIRECT for all cases!

 

[tyija1995]

Another update: Just enter in B1 this formula and you won't need to worry about the equals sign;
=SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(FORMULATEXT(INDIRECT($A1&"!$B$10")),"+",REPT("+",LEN(FORMULATEXT(INDIRECT($A1&"!$B$10"))))),(COLUMN()-2)*LEN(FORMULATEXT(INDIRECT($A1&"!$B$10")))+1,LEN(FORMULATEXT(INDIRECT($A1&"!$B$10")))),"+",""),"=","")

 

[littlefish]

Perfect!

I implemented Step 5 to your formula, and then inserted the VALUE formula. This way the results will remain in columns B, C, & D.

Thanks again. Have a great day!

 

[tyija1995]

Thanks for the feedback! I always seem to send a post and then afterwards I am always finding tweaks that I could/should have made such as my 2nd post update! Even so like you mention, I could have nested my 2nd post in a VALUE function to finalise the solution, but at least we got the job done! have a great day too.

 

[sandy666]

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#70AD47]Column2.1[/td][td=bgcolor:#70AD47]Column2.2[/td][td=bgcolor:#70AD47]Column2.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

11892​

[/td][td=bgcolor:#DDEBF7]=895+1023+9974[/td][td=bgcolor:#E2EFDA]

895​

[/td][td=bgcolor:#E2EFDA]

1023​

[/td][td=bgcolor:#E2EFDA]

9974​

[/td][/tr]
[/table]

Column2: =FORMULATEXT(A2) //adjust reference suitably
then

[SIZE=1]// Table5
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Replace = Table.ReplaceValue(Source,"=","",Replacer.ReplaceText,{"Column2"}),
    SplitPlus = Table.SplitColumn(Replace, "Column2", Splitter.SplitTextByAnyDelimiter({"+"}, QuoteStyle.Csv)),
    Type = Table.TransformColumnTypes(SplitPlus,{{"Column2.1", Int64.Type}, {"Column2.2", Int64.Type}, {"Column2.3", Int64.Type}}),
    RC = Table.RemoveColumns(Type,{"Column1"})
in
    RC[/SIZE]

 

[littlefish]

Thanks for this option. I've never used PowerQuery, but it looks interesting. I'll check it out when I have some free time.

 

[sandy666]

sure,

you can do that without FORMULATEXT() but replace = to '= in range
the rest is the same as above (almost)

have a nice day