+ Reply to Thread
Results 1 to 6 of 6

can this be automated without VBA?

  1. #1
    Registered User
    Join Date
    06-14-2012
    Location
    Italy
    MS-Off Ver
    Excel 16.0
    Posts
    42

    Question can this be automated without VBA?

    Hi, I have this formula in a "Recap" sheet's B2 cell:

    Please Login or Register  to view this content.
    I can't share a sample with ease, too many sensitive data. But, as you can see/imagine, every month the workbook will receive a new YYYYMM sheet, and above formula will be updated with a new SUMIF('YYYYMM'!$C$2:$C$40;A2;'YYYYMM'!$J$2:$J$40)+ block. So, apart from the name of the sheets it takes info from (YYYYMM), the rest of the formula is static.

    I was just wondering if there is a way of "upgrading" the formula automatically, without VBA, e.g. "compacting" it.
    Thank you!

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,529

    Re: can this be automated without VBA?

    If you could I would put the formula on each data sheet
    SUMIF($C$2:$C$40;A2;$J$2:$J$40) at needs to be referencing the cell on the sheet with the results on it

    then if the same cell is used in each data sheet you can then sum that cell between the sheets


    https://www.howtoexcel.org/tips-and-...in-a-workbook/

    you may wish to add a blank sheet so you do not need to keep adjusting the formula if you but the new data sheets before this

  3. #3
    Registered User
    Join Date
    06-14-2012
    Location
    Italy
    MS-Off Ver
    Excel 16.0
    Posts
    42

    Re: can this be automated without VBA?

    Hi! It would be more work to put the partial formula in each sheet, which atm has just data, but thanks for the hint

  4. #4
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,970

    Re: can this be automated without VBA?


  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,529

    Re: can this be automated without VBA?

    But the formula could just be copied from an existing data sheet, which is quicker than manually editing the sheet to add the new sumif for the new sheet, so arguably it is less work!

    I do like Pepe's suggested solution to keep everything on one sheet although you need to make sure you type the names of the tabs accurately

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,838

    Re: can this be automated without VBA?

    K1= new sheet name

    =SUMIF(INDIRECT("'"&$K$1&"'!$C$2:$C$40");A2;INDIRECT("'"&$K$1&"'!$J$2:$J$40"))
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ 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. Automated Entries
    By ajisfree in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2017, 03:46 AM
  2. Automated url look up in who.is?
    By Flabbergaster in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2014, 01:58 PM
  3. Automated table
    By sije1204 in forum Excel General
    Replies: 0
    Last Post: 01-22-2014, 09:34 AM
  4. How to automated this?
    By Nasri.3900 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2013, 05:15 AM
  5. Automated Vba Macro
    By Winon in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 10-03-2011, 05:15 AM
  6. Automated Ticketing for IT
    By albert28 in forum Excel General
    Replies: 4
    Last Post: 06-24-2010, 04:25 AM
  7. [SOLVED] Automated Popups
    By Brad.R.Sutton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2006, 11:00 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