+ Reply to Thread
Results 1 to 13 of 13

Auto copy text from multi sheets to a summary

  1. #1
    Registered User
    Join Date
    02-23-2021
    Location
    HK
    MS-Off Ver
    2013
    Posts
    69

    Auto copy text from multi sheets to a summary

    I would like to seek someone to help me solve the following problem.
    I would like to record all quotations in an excel workbook.
    I will createnew quotations in new sheets in the future. How the key points of quotations are automatically copied to a summary sheet (such as quotation no., date, cost)?
    Any formulas that can be recommended to me to complete the excel workbook?
    Enclosed photos for information
    Attached Images Attached Images

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Auto copy text from multi sheets to a summary

    An image attachment has limited value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.

    The paper clip icon does not work for attachments. To attach a file:

    1. Under the text box where you type your reply click the Go Advanced button.
    2. On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
    3. Click the Choose File button to select a file to attached.
    4. Click the Upload button to upload the file.
    5. Then click the Close This Window button. Your file is now attached to your post.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    02-23-2021
    Location
    HK
    MS-Off Ver
    2013
    Posts
    69

    Re: Auto copy text from multi sheets to a summary

    Thx reply

    I attached the excel file.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Auto copy text from multi sheets to a summary

    Assuming that you want to list the last quotation from each sheet:
    1. I suggest rearranging the quotations on each sheet to put them in record form as shown in columns E:H on sheets SH1 and SH2 of the attachment.
    2. The formula for the Summary sheet is: =IFERROR(INDEX(INDIRECT("'"&$B3&"'!"&C$1&"2:"&C$1&"5"),AGGREGATE(14,6,(ROW(E$2:E$5)-ROW(E$1))/(INDIRECT("'"&$B3&"'!"&C$1&"2:"&C$1&"5")<>""),1)),"")
    Note that the letters in row 1 may be hidden, for aesthetic purposes, by changing the font color of that row to white.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,326

    Re: Auto copy text from multi sheets to a summary

    Worksheet name : Summary
    Cell C3 array formula , Drag down and across
    HTML Code: 
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-23-2021
    Location
    HK
    MS-Off Ver
    2013
    Posts
    69

    Re: Auto copy text from multi sheets to a summary

    Thanks for your reply
    I am sorry for my late feedback because I tried to learn and study the formula in the sheet.

    I cannot fully understand and modify the formula to suit my actual case, it was failure finally. I found two problems of the draft summary shown in the attached file. Therefore, please teach me how to modify the formula and put it into the quote summary to fit the actual situation. Thanks
    Attached Files Attached Files

  7. #7
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,326

    Re: Auto copy text from multi sheets to a summary

    worksheet name : Quote Summary

    Cell G4 array formula , drag to H4 , and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cell I4 array formula , drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cell J4 array formula , drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by wk9128; 10-14-2021 at 04:55 AM.

  8. #8
    Registered User
    Join Date
    02-23-2021
    Location
    HK
    MS-Off Ver
    2013
    Posts
    69

    Re: Auto copy text from multi sheets to a summary

    Thanks for your reply

    I found problems when I input two or three pages of quotations in a new sheet. The quote summary cannot show the information. Would you mind helping to modify the formula?
    Attached Files Attached Files

  9. #9
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,326

    Re: Auto copy text from multi sheets to a summary

    worksheet name : Quote Summary

    Cell G4 array formula , drag to H4 , and copy down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cell I4 array formula , drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cell J4 array formula , drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by wk9128; 10-15-2021 at 05:30 AM.

  10. #10
    Registered User
    Join Date
    02-23-2021
    Location
    HK
    MS-Off Ver
    2013
    Posts
    69

    Re: Auto copy text from multi sheets to a summary

    Thanks for your reply

    After the modification of the formula, there are two minor problems found in the quote summary.
    1. Some descriptions cannot be shown on the cells.
    2. Cost amount did not be shown on the cell of the last page of the quotation.

    Would you mind helping me to solve the problems?
    Attached Images Attached Images
    Attached Files Attached Files

  11. #11
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,326

    Re: Auto copy text from multi sheets to a summary

    worksheet name : Quote Summary

    Cell I4 array formula , drag down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Why is there no way for the I10 result? Because the worksheet name: JBY, Cell A179 is the big five text type, just change the general text
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by wk9128; 10-18-2021 at 11:48 PM.

  12. #12
    Registered User
    Join Date
    02-23-2021
    Location
    HK
    MS-Off Ver
    2013
    Posts
    69

    Re: Auto copy text from multi sheets to a summary

    Thanks for your reply

    I found the following problems.
    1. Two quotations show an incorrect description in the cells.
    2. Two quotations cannot show the description in the cells.
    3. Total amount cannot show on the correct last page of quotations in the cells.

    May I have chances you can help me to solve the problems?
    Attached Images Attached Images
    Attached Files Attached Files

  13. #13
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,326

    Re: Auto copy text from multi sheets to a summary

    @Ben9108 Have you downloaded the attachment of POST#11? See if it is the result you want?

+ 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] Multi sheets multi times copy to new workbook during the month?
    By mazadb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2021, 05:26 PM
  2. Copy Multi Rows From Multi Sheets Based On Column Value And Create New Workbooks
    By Huskersippi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2017, 04:51 PM
  3. [SOLVED] Take values from multi sheet and copy to a summary page
    By egchristensen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2015, 04:02 PM
  4. Help! Creating summary sheets for a master multi-field budget
    By LP27 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2014, 09:42 AM
  5. [SOLVED] Summary sheet - copy cell contents from multiple sheets to one summary sheet
    By jsmity in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 10:32 AM
  6. Copy specific cell from multiple sheets to summary - excluding some sheets
    By kabammi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2012, 09:27 AM
  7. Replies: 2
    Last Post: 03-24-2011, 11:43 PM

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