+ Reply to Thread
Results 1 to 7 of 7

Detect the presence of data in specific cell range and update table in another sheet

  1. #1
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    145

    Exclamation Detect the presence of data in specific cell range and update table in another sheet

    Hi, all

    I have created a workbook which can generate PPT from Excel content. The contents of Excel need to be defined in Definitions sheet (in the table) accordingly, following the slide index in the template.pptx. My concern is, there is this one sheet that will update its content every time the data in cell K2 and M2 (DTE sheet) detects the similarities of data in DTE_Raw sheet (the raw data) - almost like filter function.

    Now, is it possible to create a code in which it will automate the defining work of the table in DTE sheet? The details are as specified:

    1. With every 5 data in A2:A20, the table will copy the header in A1 and "insert copied cells" to the row below the 5th data. So, assuming we have several sets of table (max is 3 tables with max of 5 records in each) in DTE sheet now, next step will be defining the table(s) automatically to the Definitions sheet (currently user has to fill in the details by himself but my superior does not want that). Here are the details need to be defined:

    a. "Excel Page" = the sheet name ("DTE")
    b. "Excel Object" = cell ranges starting from header to the row of A which only has record i.e. an example is shown in DTE sheet
    c. "Excel Type" = "Range" for each new row
    d. PP page = increment of 1, starting from PP slide 17
    d. "PP Object" = "Table" for each new row
    e. "Top" = 1.5, "Left" = 0.5, "Width" = 9.0

    2. Determine the height of cells which have data (including header), i.e. A7:G12 or A13:G14 and the height will be updated to "Height" column in the Definitions sheet, according to their row - height will also have to be changed in terms of unit (divide by 72).

    Your help will be really appreciated as I am currently in deep confusion, especially with number 2. That is why the description is quite lengthy as I am trying to be as specific as I can. But do ask if there's any question. Thank you in advance!
    Attached Files Attached Files
    Last edited by ell_; 01-21-2018 at 08:25 PM.

  2. #2
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    145

    Re: Detect the presence of data in specific cell range and update table in another sheet

    Anyone willing to give a hand? I can perhaps configure further if your code can only do number 2. Hereby I attached my previous code for number 2 but it is not fully as I wanted:

    This code requires user to select the cell ranges first.

    Please Login or Register  to view this content.
    Would really appreciate a hand with this. I'm able to edit and understand code but still quite slow with creating complex code myself. Thanks in advance & have a nice day!
    Last edited by ell_; 01-18-2018 at 11:00 PM.

  3. #3
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    145

    Re: Detect the presence of data in specific cell range and update table in another sheet

    Hi, I'm still seeking for suggestion/answer. Anyone?

    Thanks in advance!

  4. #4
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    145

    Re: Detect the presence of data in specific cell range and update table in another sheet

    Hi, again

    I believe my post has been ignored as no one seemed to understand my issue. Hence, hereby I attached the template.pptx just in case you would like to try the PPT generator. As long as your Excel and template.pptx file are in the same folder, you can run the code.

    Hope you will have better understanding of my explanation up there.

    Have a nice day!

    PPTX link: https://drive.google.com/open?id=1vu...jWfZ2Qfd2pL-Q1
    Last edited by ell_; 01-21-2018 at 09:36 PM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,111

    Re: Detect the presence of data in specific cell range and update table in another sheet

    Probably no answers because you posted to the Excel Programming section - now moved to the Powerpoint Programming section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,957

    Re: Detect the presence of data in specific cell range and update table in another sheet

    Step 2 seems like it should be a simple .height property of the appropriate row/cell: https://msdn.microsoft.com/en-us/VBA...property-excel
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    145

    Re: Detect the presence of data in specific cell range and update table in another sheet

    Hi, MrShorty

    Thank you for the reply. After days of trying, I finally managed to do the code but not fully. There's still a mistake. Here's my current issue if you're interested: https://www.excelforum.com/excel-pro...ml#post4827033

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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