• 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...

  • 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

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
 

Attachments

  • data row addition.xlsx
    23.7 KB · Views: 4
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.
 
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?
 
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.
 

Attachments

  • data row addition (PB).xlsx
    34.4 KB · Views: 7
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.


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