If cell is not empty, copy row...

  • Hi,


    I am pretty new to all this and unfamiliar with macro's and script, but I do have some very basic formula knowledge. However I cannot figure out the following. Could someone please be so kind to advise?


    I am trying to create a summary of data on another tab in Excel. Basically, would I would like to do is:


    If cell range F4 to F40 on my worksheet tab named INVOICES is not empty then copy the row of each non-empty F-cell to rows 100 to 136 on a worksheet tab named SUMMARY. All empty rows need not be displayed on SUMMARY


    Would that be possible with a formula? Which formula? And in which cell or cells on which worksheet tab do I need to enter it?


    Thanks!


    Mike

  • Hi Roy,


    Thanks for your message. Please find a sample sheet attached. In this case, my query would be:


    If cell range F4 to F40 on my worksheet tab named INVOICES is not empty then copy the row of each non-empty F-cell to rows 100 to 136 on a worksheet tab named SUMMARY. All empty rows need not be displayed on SUMMARY.


    Thanks!


    Regards,


    Mike

    Files

    • TEST.xlsx

      (65.44 kB, downloaded 58 times, last: )
  • Hi Roy,


    Columns A to G on the INVOICES tab may or may not contain data. If they do contain data (text or numbers), they need to be copied to the mentioned rows on the SUMMARY tab. There are no headers.


    Regards,


    Mike

  • Hi Roy,


    Sorry, I'll rephrase that as my previous comment was not very precise. The original query was more precise:


    If cell range F4 to F40 on my worksheet tab named INVOICES is not empty, then copy the row for each non-empty F-cell to rows 100 to 136 on a worksheet tab named SUMMARY. All empty rows need not be displayed on SUMMARY.


    Regards,


    Mike

  • Hi Roy,


    We have all been under a lot of stress lately, with the lockdown and all, so I'll let you off this time. But what have I done wrong to deserve your wrath? Why are you angry at me? It takes a little more time to be polite than just spewing out oneliners. Did you see how I keep addressing my messages to you by mentioning your name at the start of each message? And ending them with my regards and my name? I am sure you can do that too. Mind you, my question wasn't lifethreatening I hope. I am sure you help others voluntarily without a pistol held against your head. So, please, invest a little time in being polite.


    Okay, I'll press reset now. What exactly is the question that I didn't answer?


    Kind regards,


    Mike

  • Maybe try this formula solution


    In "SUMMARY" sheet cell A100, enter formula and copied across to F100 :


    =IFERROR(INDEX(INVOICES!A:A,AGGREGATE(15,6,ROW(INVOICES!$A$4:$A$40)/(INVOICES!$F$4:$F$40<>""),ROW(A1))),"")


    And,


    Select cell C100:F100 >> Custom Cell Format, enter : #,##0.00;;;


    Then,


    Select A100:F100, all copied down to row line no. 136


    >> Finish





    TEST (BY).xlsx

  • Hi bosco_yip,


    Thanks for your message and the formula. I am not getting his to work as Excel complains it's not a formula. But it doesn't matter. I'll just copy what I need manually for now.


    Thanks though.


    Regards,


    Mike