Results 1 to 6 of 6

Thread: if add to table

  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: if add to table

    how can i add data to a table that meets a certain criteria.

    Ex.

    XO, name, comment
    CP, name, comment
    RS, name, comment

    I want to add every row that has XO or RS in it to a table, maybe a pivot table

    Then recall this data on another page. The XOs and RSs will be pulled from several pages.

    TIA

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. You can't add data directly to a pivot table. You would have to add it to the underlying data table, and then update the pivot table (including changing the references - unless you planned ahead and used a dynamic named range for the underlying table and built the pivot using the dynamic naed range.).

    Can you give a little more information about what you are trying to do? (larger context) and can you provide a small sample?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Sep 2006
    Posts
    162
    I have 30 pages, each have 10 rows of information that spans about 10 columns.

    The important column will have the following options. ND, RS, RA, XO, CP

    Any of the 30 pages could have several rows with the column equal to ND or XO. and the others will have RA or CP. I want to pull all the rows that have ND or XO and list them on one page.

    So it would be like if Column x = ND or XO add to table y on page z.

    Another optoin would be to add a button on each page beside each row to add that line to another page.

  4. #4
    Join Date
    Feb 2006
    Posts
    113
    Hi,

    One way would be to set up a query. With SQL of the form
    Code:
    SELECT option, name, comment
    FROM tbl1
    WHERE option IN (RS, XO)
    UNION ALL
    SELECT option, name, comment
    FROM tbl2
    WHERE option IN (RS, XO)
    UNION ALL
    SELECT option, name, comment
    FROM tbl3
    WHERE option IN (RS, XO)
    etc, etc for the 30 source tables
    Or could do via VBA. Say using simple or advanced filtering, loop through each worksheet and copy desired results to summary page.

    Or via VBA by looping through worksheets and process using arrays. Copy data from each sheet to an array, loop through and store wanted results in an array that ultimately is posted to the desired results location.

    In all of these I guess you could do the filtering at the end too. Such as grab all data and at the last step filter for the "RS" & "XO", or whatever.

    regards,
    Fazza

  5. #5
    Join Date
    Sep 2006
    Posts
    162
    Sorry, been out of town.

    I hate to say I have no experience in VBA. Could you give me a basic format on how to do this.

  6. #6
    Join Date
    Feb 2006
    Posts
    113
    Without VBA, it might be simplest to use autofiltering. Manually go from worksheet to worksheet applying the filtering that you want and copying the successive filtered results to a destination worksheet. OK?

Posting Permissions

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