Posts by Magdoulin

  • VBA event to 1) print now, 2) print username & 3) prevent deletion

    Hi Carim, Thank you for your prompt reply.


    I assume by attaching the sample sheet, I'm clarifying the aspect of the needs already, however, let me elaborate by answering your validating points:


    1. ' active row ' - second cell means Column B ... but your sheet will ONLY have one row ... so why have you prepared 427 rows ?
    Because each time the sheet will be re-opened, another row will be used and it will be the active row in this case.


    2. ' active row ' - seventh cell ... do you mean Column G ?
    Yes, I do


    3. prevent deletion of the last row ... why only the last row ? is there a difference between ' active row ' and ' last row ' ?
    I meant the active row, yes, I'm sorry.


    P.S. Your title says ' Print ' ... do you really mean Print ... or do you mean Input ...?
    I meant input, yes, I'm sorry.



    If you've a different solution suggestions than the presented, they're truly welcomed.


    Thank you.

  • VBA event to 1) print now, 2) print username & 3) prevent deletion

    Hi guys, I hope everything is going well with you.

    Look, I need your help please.

    Please check the attached.

    I need to create Save event. So, once the sheet is saved, the file acts as follows:

    • Type the date and time (NOW function) in the active row, second cell.
    • Type the username (Environ username, I guess) in the active row, seventh cell.
    • The last thing to auto-close the sheet once these commands are completed and the sheet is saved, however, and that’s the challenging thing that I’m not really sure if it’s doable, I want the sheet once it’s re-opened again, I need the lastly added row to be, editable yes, but not removable, like a footprint.


    Could someone help with the code writing? I’m familiar with the needed functions for the first two points, yet, I’m not that good to write the code, and for point# 3, I don’t know at all how to do it or what to use.

    Thank you guys.

  • Pivot Table Text Grouping by the First 3 Characters

    [INDENT]Hi guys, I need a help with the Pivot Table please, I need to group text fields automatically by the first 3 characters, how could I do that? Noting that I need to do this in the same workbook because I came across some solution but it needed to create new workbook for the Pivot Table. I do not want to proceed with manipulating the raw data as well, I need to do this through the Pivot Table itself please.



    Note: this post was published originally here but not sorted:
    https://www.mrexcel.com/forum/…g-first-3-characters.html[/INDENT]

  • Missed Conditional Formatting After Running Macro

    Hi guys, how is everything?
    Look, I need a quick consultancy how to sort this issue out.
    First, please visit this Google Drive link to view the Excel file subject to discussion:
    https://drive.google.com/open?…QVVgUbChttvc6E-NBM5lB02ob

    It’s big file, that’s why I needed to put it in Google Drive.
    Now, in two sheets in the file: 1) (Centre Name) Final Sheet & 2) Intraday, there is a hidden, G column is hidden and it has some conditional formatting.

    When I run the macro, first, the conditional formatting in G column changes the way it works.

    Second, when I copy the generated data range in (Centre Name) Final Sheet and paste it in Intraday sheet, it paste everything but G column, which is really strange.

    How could I fix these two issues please?

  • Applying Conditional Formatting (by 3 conditions)

    Wow! Oddly this worked!
    First of all, let me thank you of course


    The only difference I could tell that you didn't absolute the column:
    My formula was:
    =($A2<>"")*($b2="")*COUNTBLANK($b$2:$b$27)<>COUNT($C$2:$C$27)
    Your formula is:
    =($A2<>"")*(b2="")*COUNTBLANK($b$2:$b$27)<>COUNT($C$2:$C$27)

    but I don't get it, is there an explanation for that?

  • Applying Conditional Formatting (by 3 conditions)

    Alright, let us take this range only to simplify, $B$2:$B$27.


    Now, applying the below three conditions for example:
    =($A2<>"")
    =($b2="")
    =COUNTBLANK($b$2:$b$27)<>COUNT($C$2:$C$27)

    They are supposed to be all TRUE for cell B3 only, so the conditional formatting should work for this cell only.

    However, when I apply them in one condition to apply the formatting by this way:
    =($A2<>"")*($b2="")*COUNTBLANK($b$2:$b$27)<>COUNT($C$2:$C$27)
    The formatting is applied over the whole range, which is not supposed to be the case

    Why is that?

  • Applying Conditional Formatting (by 3 conditions)

    Hi guys, I seek your help please.
    Referring to the attached, for each range of B2:B26 & D2:D26, I’m trying to apply conditional formatting when there are three conditions meet:

    • If there’s blank cell in the range (B2:B26 for instance)
    • Each equivalent cell in range A2:A26 isn’t blank
    • If the whole range (B2:B26 for instance) isn’t blank


    I’ve reached the stage of conditionally formatting to meet the first two conditions over the two ranges by applying this formula: =($A2<>"")*($B2=""), for the range B2:B26 and =($A2<>"")*($D2=""), for the range D2:D26.

    But I’m not able to include the third condition whatsoever, any suggestion here please?

  • Custom Formatting Number

    How to custom format numbers to be displayed with the format x/50

    I’ve tried:
    ?? "/50"
    But it gave totally different output than the wanted

    I want the following: if I type for example 35, it’s formatted to be 35 /50

    How to achieve that please?

  • Conditional Formatting Issue

    Basically, it was much easier than that
    All what was needed to adjust the conditional formatting formula to be =(NUMBERVALUE(G3))>25569.0069444444
    No further modifications were needed
    And it works like magic
    Thank you for all of your assistance though

  • Conditional Formatting Issue




    I see, I could see the formulas differences yes, I'll give it a try for sure, but for the sake of learning, what is really the difference? Why my index.match formulas affect the conditional formatting here? I don't get it

  • Conditional Formatting Issue

    Quote from Carim

    Hello,


    In the message # 5 above ... your test file is fully operational ...:wink:


    Take the time to understand how the Format used in Column X is impacting on the formula used for Conditional Formatting ... :smile:



    Well, the modifications I can see you have applied that you changed the column X and AHT column format to be as time mm:ss, which is affordable, and you have unmerged the cells in the template sheet which is something we won't be able to do all the time for the fact that the report is generated that way from the system.


    And to be honest, I'm not really sure why the merged cells should affect the conditional formatting in another sheet


    I don't know where is the gap here because I have tried to change the formatting of the two columns to be time mm:ss keeping the merged cells as they are, yet, this didn't work

  • Conditional Formatting Issue

    Quote from Carim

    صباح الخير


    Hello Magdoulin,


    Is the correct format in Column X ( Sheet Category template ) combined with the new formula in cell D3 ( Sheet Category Final Report ) allowing the conditional formatting to operate 'normally' ...? :wink:



    I'm not really sure but I think so
    The thing is, I fully understand the complexity caused by the merged cells, but this's inevitable actually as the report is generated by this way from the system.

  • Conditional Formatting Issue

    I need the cells to be highlighted if the duration is longer than 0:10:00 minutes
    What happened that the conditional formatting doesn't work
    As you could see the first cell has 0:07:34 as value, however, it is highlighted as if it is longer than 0:10:00 with the conditional formatting I set
    Which is incorrect

  • Conditional Formatting Issue

    Hi Guys, how is everything, I am seeking your advice here please, why Conditional Formatting didn’t work for $D3:$D13 range? It was meant to highlight the time duration if it is greater than 0:10:00, taking into account that the range values come from formulas and that the cells are formatted as numbers for reporting purposes, any help please?

  • Sum Time Issue

    Well, I figured it out
    I believe I need to use this
    =SUMIF(B1:B50,"<>")-SUM(COUNT(B1:B50)*25569)


    I believe this sort the blanks issue
    Thank you so much
    You helped me a lot

  • Sum Time Issue

    great
    but there is a problem makes this formula doesn't work with me
    that the range B1:B40 will get blank cells occasionally
    like If I'll calculate the Sum for B1:B50 (10 empty cells there)
    It'll not generate the needed results
    I've tried sumif(b1:b50,<>"") but it doesn't work I don't know why