Posts by myexcelstar

  • List starters and leavers on separate sheet

    Hi Roy,


    Autofilter won't work as I need this data as part of a report. I just did not include all the rest of the data here as it is not related to my problem. I need a physical listing of starters and leavers between other data, that I pull from other sheets into one summary sheet.

  • List starters and leavers on separate sheet

    Hi everyone,


    I would need your help again because I am stuck now. I have a list of employees with their start and end date. On a separate sheet, I want to list all leavers and starters for the next month, based on the company they work with. I have an INDEX formula (with SMALL), but this can only look for all starters and leavers, it cannot separate by company. How could I do that?

    So when I select "NORTH" in cell C1, I want to see only the starters/leavers from company NORTH. If I select "SOUTH" in C1, then of course only the ones from company South.


    I also tried the MATCH function with several criterias connected with *, but that did not work either. Anyone can help? File is attached.


    Thanks so much for your assistance.

  • Popup Message when date is approaching with condition

    Hi all,


    I have new list I am trying to create, in which I get alerted, if a date approaches to renew contracts. Here is what I need:

    - Upon opening the file, a popup window will appear and list the names (column A) and Dates (column E) of all those persons, whos contract will expire or who are leaving

    - This should happen 4 weeks before the expiry date in column E

    - It should also include dates that have past the deadline (eg. if the deadline is 30.7., the item should still appear, until the date is changed into the future)

    - It should include those lines, where the status (column D) is EOC and those whos status is OUT. If possible, list them sorted by EOC first and then those with OUT as status

    So it should list White and Black first (both EOC) and then Smith (OUT)


    I attached my file.


    Is this possible? I found codes that trigger the popup box, but they don't have the option of a condition.


    Thanks so much for your help!!!!Popup File.xlsx

  • Printsummary Sheet pulling information from other sheets based on value in one cell

    Hi Carim

    I posted a link to here in the other Forum because I don't want to be in breach of the Forum rules. My original message was:


    I am stuck with a Project and I would Need some advise. I have created a schedule that can be used to track attendance in the Computer. But it is too large to print (on letter size/A4 paper). So I would like to create a Print Summary sheet, which pulls the Information from a specific sheet.

    Example: I enter my schedules in sheet KW1 for my staff. This sheet is set to print ok on A3 Format. But as there are not enough A3 Printers, I Need it in A4 Format. For this, I have created the sheet 'Print'. On this sheet, I want to be able to enter the sheet Name (KW1-52) in cell B1, from which then, the 'Print' page pulls all the data from. It would eg. pull in row 7, the ID No, Name, Carryover items (RU, UR, GT, GS) and then the daily start and end times, exactly as they are on sheet KW1. And if I enter KW2 in B1, it would pull this Information from sheet KW2, etc. There should be no further calculations on the 'Print' sheet, it is purely to Format the data for A4 size printing.

    I don't think an IF function would work as there will be 52 Sheets to pull from…. is there any other Option to achieve this??

    Any help would be greatly appreciated. The file is attached.

    Thanks so much for your time!

  • Certain users can only access certain work sheets??

    Hi Mumps,
    what I realized now, when someone opens the file for the first time on a different Computer, it opens fully minimized, with all Sheets visible. I guess this is because of the security Questions to enable the Content and to activate the Content. Can this be avoided? I don't want anyone to see the Sheets that are not meant for them.


    Thanks so much

  • Certain users can only access certain work sheets??

    Hi Mumps,


    thanks, works very well. I made a modification and was wondering if you could tell me if what I did is correct. In the file you sent me, Sheets were just hidden, so any user would have still be able to unhide them. What I did, in your Code, I changed ws.Visible = xlSheetHidden to ws.Visible = xlSheetVeryHidden.
    When I do this, the Option of unhiding does not even come up for the users (except the Admin). Is that the Right way of doing it?


    Thanks so much

  • Certain users can only access certain work sheets??

    Hi Mumps,


    thank you soooo much. This works perfectly!!!! Wonderful!!!!! Just two Questions:
    - When someone opens the file first, it opens in protective mode, asking to activate the Content before Opening the file. What it means is, the file opens up in Dep6 tab first with the instructions to "activate Content". Once I do that, then the UserForm Pops up. This means, the Person Opening the file will be able to read what is written on Dep6 sheet. Can this be changed so it opens in an empty sheet?? It happens only the first time when the program does not know that the file is safe.
    - Can the Password field be set so it Displays ****** upon entering the Password?


    Thanks again, you are a star!!

  • Certain users can only access certain work sheets??

    Hi Mumps,
    Thanks for your Reply. Sorry I did not post a file. Here it is. The summary sheet pulls only Information from the Dep1-10 Sheets to Display the totals for the entire Company. On the Dep1-10 Sheets, the HODs will track the attendance of their staff, and the totals in columns NG, NH and NI will be taken over to the summary sheet. On the Users sheet, I want to be able to Input the user names and Passwords.
    I want the different users only to be able to Access their work sheet (eg. User 1 -> Dep1 sheet). So when they open the Excel file, a User Form will open up for them to enter their user Name and Password and then only their Dep sheet will open. They cannot Access the others. The Admin of Course should be able to see and open all Dep Sheets. There are no other files, all are combined into this one file (we used to have Dep1, Dep2, Dep3,..... as separate files, but that doesn't allow me to pull the totals from them without Opening each and every one of them).


    I hope this explains it better. Thanks again for taking the time to help me in this Project.

  • Certain users can only access certain work sheets??

    Hi all,


    I have been Looking all over, but I can't find an answer to this challenge. We currently have different files for different Departments, so pulling a summary report is not possible without Opening each one of These files to update my summary page. Here my Question: I would like to combine alls these single files into one, is there a way to create a workbook with several work Sheets (A, B, C....) and then to protect them in a way, that only User A can see & Access Sheet A, User B can only see & Access Sheet B, User C can only see & Access Sheet C. Important is that User A can fully Access Sheet A but has no Access to the other Sheets, not even to look at the data on them.
    Of Course Administrator X should have Access to all Sheets. Also, could then a User have Access to several Sheets, eg. User D has Access to sheet D and E?


    Is that actually possible or am I expecting too much from Excel??

  • Vacation entitlement calculation

    Hi all,
    I have been trying to complete a Task on the calculation of vacation entitlement, but now I am stuck and I was hoping someone here would know how to complete this. Here the Situation:
    I have a list of employees and their start Dates. In the first year of their employment (the remainder of that calendar year until 31.12.) they earn 10 days of vacation (pro rated of Course, based on amount of days worked in that time). In the second year (full calendar year after their start date from 1.1. - 31.12.) they earn 15 days and in the third (and more) year (calendar year 1.1. - 31.12) they earn 20 days. This means, eg. if someone starts on November 1st, for the first 2 months of their employment, they earn vacation based on 10 days entitlement (10/12*2). Then from the 1st January until the end of that next year, they will earn 15 days and the years after that, they will earn 20 days per calendar year.
    So what Formula is there to tell Excel, to Count the remainder of the start year on 10 days, the next calendar year on 15 and all future calendar years on 20 days??
    What I Need is, a Formula in column D, that is showing me what their full year entitlement is as of today and in column E, what the total entitlement is since start date. Columns F, G, H, I and J should Show me their entitlement for each year (pro rated if it is in the first year or the current year)


    I hope this is not too confusing, but not sure how to explain it better.


    Thanks so much for any help you can give me!!

  • Populate Leave Calendar

    Dear Ali


    thanks so much for the link. Not sure if this can work as it is working with conditional formatting rather than "x". But maybe I can use the formulas in a way that it can work for me too. Anyway, thanks so much for taking the time to assist. Truly appreciate it.