1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

auto insert row, based on cell value

Discussion in 'Ask an Excel Question' started by JAlandhar 201718, Mar 14, 2019.

  1. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    73
    hello ninjas,

    I need some help again in my sheet, as I have date wise data now for all year, i need to print the data date wise like from date 01-04-2018 to 31-12-2018, can the data be automatically filtered date by date? or I have to print it day by day by manually adding date.

    Like I created a workbook in my sheet, which filters the data by only 1 date. what i need in result is like for example I added 04-04-2018 in that workbook, it automatically reflects the data of that date, and then I filtered it on next sheet by sub ledgers, then I need to have print of the result category wise, I need your help in that.

    I'm attaching the sheet below

    Attached Files:

  2. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    The solution shown was achieved using dynamic arrays in Office 365 insider, so it most likely will not be of direct use to you. The steps could be of value however.

    upload_2019-3-15_13-52-50.png

    I worked directly from your 'DATA' sheet and ignored 'FILTERED'. The strategy is to sort the input based upon SubLedger. To get 0 to come after 2, I defined 'subledgerOrder' to be
    = IF( subledger, subledger, 9 )

    To insert blank rows after SUBLEDGER-1 and SUBLEDGER-2 I introduced four records with fields comprising space characters at the beginning of the data. The 'artificial subledger' numbers of these dummy rows were 1.5 and 2.5 respectively. The dummy rows are hidden.

    Another option might be to use Power Query to do the same job. It would also be possible to use CSE formulas to perform the sort but that is not ideal when the data is growing dynamically.
  3. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    73
    Thank you so much for your kind help, can you please provide me the excel sheet you made in office 365 and can you please help me once again if you can help me in printing the same. I need the same content date wise, as the data entered was of 1 day in the sample sheet, i have the data in my database from 01-04-2018 till now. I need to print the data of all months date wise in same format. Is it possible?
  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    Here is the file. You will need to sign up to Office insider to have the new Dynamic Array functionality that I have used. It will be a few months before this appears in regular versions of Office 365.

    You could easily add a filter to run through day by day, maybe a day per sheet, but that is far from the automation that you really need. Power Query may make the process a bit more automated, including appending blank records to act as subledger separators.

    If you attempt to string the whole lot together as a single print job the challenge would be to insert page control.

    Attached Files:

    JAlandhar 201718 likes this.
  5. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    73

    Thank you, I'll look out in office 365 now

Share This Page