+ Reply to Thread
Results 1 to 43 of 43

Is this "summary of text" possible with a formula?

  1. #1
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Is this "summary of text" possible with a formula?

    I have a workbook with sheets 5 - 34 that have text on each sheet (cells F70 to F84), on each sheet.
    Some of these cells will have text, most will be blank.
    What I need is to have any and all of the the text in these cells (F70:F84) of each sheet, (in this case sheets 5 through sheet 34), show up on sheet 35, in one section (and without the blank lines). Starting on 35 - BX7 (pasted down).
    So sheet 35 would have a "summary" of all the text on sheets 5-34 (of the F70 : F84 lines), but with no blank lines.

    Note: My sheets are just labeled 5,6,7,8,etc,
    I am using excel 2007

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Is this "summary of text" possible with a formula?

    ________________________________
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    I do not understand this?
    This would be pasted somewhere?
    Please explain

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Is this "summary of text" possible with a formula?

    there was no file, i've made mine
    explain what?

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Is this "summary of text" possible with a formula?

    Re: Is this "summary of text" possible with a formula?
    Hi
    See my solution
    1st. Count number of itens in each Sheet in the range :: Column A
    A2: =COUNTA(INDIRECT("'"&ROW(A1)&"'!C3:C12"))
    2nd. Start line in B2
    3rd. Comulative line in B2:B5…
    4th. Columns C and E not for use
    5th. Column D has the Sheet name
    D2: =MATCH(ROW(),$B:$B,1)
    6th Column F has the item number on each sheet
    F2: =IF(D2=E1,F1+1,1)
    7th. Column G has the Row of each item in the corresponding sheet
    G2: =1/AGGREGATE(14,6,1/(--(INDIRECT("'"&D2&"'!$C$3:$C$12")<>"")*ROW($C$3:$C$12)),F2)
    8th. The itens of all sheets
    H2: =INDEX(INDIRECT("'"&D2&"'!$C$1:$C$12"),G2,1)
    See the file summary of text.xlsx

  6. #6
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    This is totally confusing. Maybe I am not being clear. This is all "text / sentences". (F70 - F84 on each sheet)
    I would need a formula that gives me the summary of "all sheets of text (F70-84), to "copy / show up" in one section on sheet 35.

  7. #7
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Hi there,

    You could accomplish this with a Subroutine.

    Hit Alt+F11 to open your VB editor.
    click insert at the top and select module.
    Paste the code below into the module and click Run (the little green sideways triangle that looks like a play icon)

    Please Login or Register  to view this content.
    Provided there is nothing in the cells below the data you want in column BX, this will work. If there is data below, it will shift up... Let me know if that is the case.
    Last edited by Raymundus; 02-03-2016 at 12:33 PM. Reason: Edit... I had 13 sheets instead of 35... missed one in the lrow range

  8. #8
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Is this "summary of text" possible with a formula?

    Quote Originally Posted by richard11153 View Post
    This is totally confusing. Maybe I am not being clear. This is all "text / sentences". (F70 - F84 on each sheet)
    I would need a formula that gives me the summary of "all sheets of text (F70-84), to "copy / show up" in one section on sheet 35.
    Hi
    You can substitute $C$3;$C$12 of my example with $F$70:$F$84
    So, for your range and name of sheets
    =COUNTA(INDIRECT("'"&ROW(A1)&"'!C3:C12"))
    must be
    =COUNTA(INDIRECT("'"&ROW(A4)&"'!$F$70:$F$84"))
    and copy down to row 37

    Regards

  9. #9
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Thanks Ray. I know a little about VBA.
    I did it, but when I press the "run" I get "Error 9, Subscript out of range"

  10. #10
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Jose, This all all new to me....
    So what formula exactly would I "paste down", on sheet 35 for the summary?

  11. #11
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Do your sheets have names? If Sheet35 has a name other than "Sheet35" the code will need to be modified. Also, I edited my original post... the line that says:
    lrow = Sheets("Sheet13").UsedRange.Rows.Count

    now reads Sheet35 instead of sheet13...

    Not sure if you tried it before I edited...

  12. #12
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Yup... tested it with the error... Subscript out of range...

    Change the 13 to 35 and it should work just fine

    If it does, I'll show you how to put a button on the spreadsheet and tie it to the Sub, so you don't have to open the VB editor anymore

    Let me know

  13. #13
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Yea, My sheet is just "35" (No sheet).
    I modified the VBA, but now I get a "mismatch" error.
    When I hit "debug" it highlights this line: [If rng1.Cells(i).Value = "" Then"]

  14. #14
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Ah... okay.... "35"

    Try this:

    Please Login or Register  to view this content.
    Or just replace everything that says sheet35 with 35

  15. #15
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    OK I cleared everything out of 35 row BX
    Ran the new code, but still get "Mismatched" as an error.
    DeBug highlights: [If rng1.Cells(i).Value = "" Then}

    Here is what 35 BX7 shows after code is run:
    =IF(ROWS(#REF!)>COUNTIF($C$5:$C$300,"d"),"",INDEX($D$5:$D$300,SMALL(INDEX(($C$5:$C$300="d")*(ROW($C$5:$C$300)-ROW($C$5)+1),),COUNTIF($C$5:$C$300,"<>"&"d")+ROWS(#REF!))))
    Last edited by richard11153; 02-03-2016 at 01:25 PM.

  16. #16
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Hmmmmm strange....

    Do me a favor and copy the code from your module, and paste back into the thread so I can look at it what's in your actual module.

    I have duplicated your workbook (structure at least) and the code works. I am in 2007 as well. I can't see what would be causing the error.

  17. #17
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Maybe it would be better if I just "copy / link" a row of the pages texts, (5-35) into one long row
    on page 35, then make a "sort" macro.
    Seems like that would work also.

  18. #18
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Ah... So, there are formulas in F70:F84 on the other sheets... A type mismatch can happen if the formulas result in an error because an error is not a value. And it seems we are copying and pasting formulas. When a formula is dropped on a new sheet, the cell references may not exist on the new sheet. Hence the #REF! notations you see in the formula above...

    This gets Really Messy... either all of the formulas need to have If(Iserror( functions and the references where the #REF! error shows up need to be expanded to include Sheet Names OR Someone with more skill than me needs to step in.

    It would really help if you could post a sample workbook (with names changed to protect the innocent).

  19. #19
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Here it is:
    [Sub copyto35()
    Dim WS As Worksheet
    Dim nrow As Long
    Dim rng1 As Range
    Dim lrow As Long
    nrow = 7
    lrow = Sheets("35").UsedRange.Rows.Count
    i = 1

    For Each WS In ActiveWorkbook.Worksheets
    WS.Range("F70", "F84").Copy Destination:=Range(Sheets("35").Cells(nrow, 76), Sheets("35").Cells(nrow + 14, 76))
    nrow = nrow + 15
    Next WS
    Set rng1 = Range(Sheets("35").Cells(7, 76), Sheets("35").Cells(lrow, 76))
    For Counter = 1 To rng1.Rows.Count
    If rng1.Cells(i).Value = "" Then
    rng1.Cells(i).Delete Shift:=xlUp
    Else
    i = i + 1
    End If
    Next


    End Sub]

  20. #20
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Yes, F70:84 (on each sheet) does have formulas where it is pulling that text from its sheet above based on a value (in the cell, just to the left) There is a lot of VBA , so I'm thinking it would just be easier to do a "link", then sort on pg 35.
    I could send you the workbook, and I'm sure rather than have the F70-84 section on each page, The page 35 summary most likely can be done directly from the source it pulls from on each sheet.
    It may be a bit of work though and I hate to burden someone with that.
    If your open to it, Id be glad to sent it to you.

  21. #21
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Send it over... I'll take a look. I'm not an expert... If I can't make it work, we can call on some other members of the forum... but I'm up for taking a shot at it.

  22. #22
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Is this "summary of text" possible with a formula?

    See the file where I made the adjustments.~

    summary of text(1).xlsx

  23. #23
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    I am total confused with what I'm looking at.
    Sorry, but I am very new to this.
    I'll send you a working workbook. See my notes in Red on pages 5 & 35
    OK?

  24. #24
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Ok Raymudus, Here is the workbook.
    See the comments on page 5 & 35 in RED
    Any help is appreciated
    Attached Files Attached Files

  25. #25
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Is this "summary of text" possible with a formula?

    See your file New Report sample.xlsm
    Is that you want?

  26. #26
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Okay... Got it... But I notice that Sheets 1,2 and T have different formats... do you want this to start at Sheet 4?

  27. #27
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Yes, start at 4. Thanks

  28. #28
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Here is your code...

    Please Login or Register  to view this content.
    Try it out and see what you think.

    If you like it, I wouldn't mind some reputation points (the little black star on the left side

    Copyto35.xlsm

  29. #29
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Wow, Nice job works great!! I have had many people try to help me with this and no luck.
    Can I make a Button, to make it start?, or can it be dynamic?

  30. #30
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Look at me being sloppy...

    Add the following below the line nrow2 = 26
    Please Login or Register  to view this content.
    And this right before End Sub:
    Please Login or Register  to view this content.
    This will keep you from seeing the flutter in the screen as the sub operates.

  31. #31
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    What ever page you want the button on, go to the top of the worksheet where it says insert.
    Select Shapes
    Select a rectangle... I like the one with the rounded edges.
    Place it where you want on the page.
    click on an empty cel to get out of the shape
    right click on the shape and go to Assign Macro
    When the window opens, Select Copyto35

    When you press the button it will run the macro

    You can format the button, add text etc.

    If you have difficulty, tell me where you want it and I'll add it for you

  32. #32
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    I guess I spoke to soon. When I tested changing the info on some pages, the summary on 35 is not changing.
    Maybe I screwed in up adding the lines
    Last edited by richard11153; 02-03-2016 at 03:49 PM.

  33. #33
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Can I send it back to you for a "Fix"?

  34. #34
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    The Button... No Prob

    Go to the sheet you want the button on.
    Go to the tab called Insert at the top
    Select Shapes
    Pick a Rectangle (I like the one with the rounded corners)
    Place the rectangle where you want it.
    Click an Empty cell (to get out of the shape)
    Right Click on the rectangle
    Goto Assign Macro
    When the window opens, Select Copyto35

    Now when you click the button, the macro will run.

    You can format the button color, size and add text if you like by right clicking on the rectangle and selecting Format Shape.

    Let me know if you get stuck. I can add it for you

  35. #35
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    oops... posted that twice...

    yeah... send it back and I'll take a look

  36. #36
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    Did you insert rows? Or just add data to the existing fields?

  37. #37
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    I did it all but not working, Ill upload the workbook for you see / correct.
    Give me one minute

  38. #38
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    I am have a hard time uploading the file for some reason.
    ARRRRG.....

  39. #39
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Got it. See new attachment.
    Last edited by richard11153; 02-03-2016 at 04:44 PM.

  40. #40
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    Got it. See attached
    Could you look at it for me?
    Attached Files Attached Files

  41. #41
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    FixedCopyto35.xlsm

    try that...

  42. #42
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Re: Is this "summary of text" possible with a formula?

    You are the best! And I appreciate all the on-going help today.
    Been working on this summary for a long time.
    Anyway to say in touch directly? (e-mail)

  43. #43
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Is this "summary of text" possible with a formula?

    You can send a private message via the Forum... And if that's got it, Please go to the top, where it says Thread Tools and mark it as solved...

    Glad it works for you...

    And again, happy to receive reputation via the little black star

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Summary of "some lines" of text on the last page of a workbook.
    By richard11153 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-31-2016, 08:02 PM
  2. [SOLVED] Need formula that finds the last ", " in a text string & substitutes it w/ the word "and"
    By danielneedssomehelp in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-23-2014, 04:06 PM
  3. Replies: 1
    Last Post: 08-15-2014, 06:00 AM
  4. "IF" statements for text and numbers for financial summary
    By amartino44 in forum Excel General
    Replies: 6
    Last Post: 10-07-2013, 02:50 PM
  5. Run Macros on all except "Summary" and "Reports" worksheets in a workbook
    By daralea in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-10-2013, 05:42 PM
  6. [SOLVED] Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary sheet
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2013, 07:25 PM
  7. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1