Posts by Carim

Glad to hear it is working as expected

You can double-click on any ISO code to generate your reports ...

Hi,

For Future Readers ... the Age Formula expressed in Years, Months and Days :

=DATEDIF(A1,TODAY(),"y")&" y "&DATEDIF(A1,TODAY(),"ym")&" m "&DATEDIF(A1,TODAY(),"md")&" d "

Hope this will help

Thanks for your clarification.

Regarding the two questions, let me have your comments :

1. Can you have several persons belonging simultaneously to several categories ? e.g. Amy Cooper or Jane Smith

2. Do you need to have dynamically adjusted Validation Lists by day, which means that as soon as a Name is picked, it does disappear from the initial list .... and, by consequence, is no longer available for the following choice .... obviously within the very same day ...

Thanks for the feedback

Glad to hear you have fixed your problem

Hello,

Do you already have a macro to send your e-mails ???

If not, the best source (and explanation) has been designed by Ron de Bruin :

Mail from Excel and make/mail PDF files (Windows)

Hope this will help

Hi,

Indeed, there is a macro in your latest version 2 file ...

It allows to automatically adjust the Date selected by the User and copy it to the "Days Off" worksheet - cell E2 which, in turn does generate ALL your Validation Lists ...taking into account your database of Days Off ...

Regarding your latest remark, a couple of questions :

1. Can you several persons who do belong simultaneously to several categories ?

2. In order to dynamically adjust the Validation Lists by day, this would require some further enhancements to hide the Names already selected ... and by consequence reduce the potential following choices ...

It looks like 2 persons: Amy Cooper and Jane Smith do intervene in two categories ...

In addition, there are many different code colors ...

Do they bear a specific meaning ... or is it just for a visual differentiation ?

Good News ...!!!

First remark : Do not know what you are referring to ... when you are talking about the "Submit" button ?

Second remark : To look for an ID number located in the second workbook, two possible methods :

1. Application.WorksheetFunction.Match

2. Cells.Find

Method 1 is much faster since you already know in which column (in the second workbook) your searched ID number is located

Method 2 is required when you do not know where the ID number might be placed ...

Hope this clarifies

Thanks for your feedback

Glad you managed to fix your problem

Hi,

So basically, the very first step is to allow the user to select his File To Open ...

You can test following macro :

Sub Select_FileToOpen()
Dim FileToOpen As String
Dim wkb2 As Workbook
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*), *xls*")
    Set wkb2 = Workbooks.Open(FileToOpen)
End Sub

Quote from rory

It's a gift.

I am so jealous

You are welcome

rory

Well Done !!!

Congrats on your exceptional talent of "mind-reader"

Hi,

At first glance, you should review the basic building structure SUMIFS

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

In addition whole columns is often counter-productive use defined ranges...

Hi And Welcome to the Forum

This thread is more than 10 years old ... !!!

You should have created your own thread with your question ...

Anyways ... attached is your test file

Hi and Welcome to the Forum

The very first question to clarify is the following:

Are you working with all workbooks opened ?

or

Do you need workbook1 to open workbook2 ?

Quickly revisited your first two worksheets ... (quite honestly, the "Master schedule" is a total mystery to me ...)

Regarding your two inputs sheets :

1. NEVER use Merged Cells ... they are the seeds of all your future problems ...!!!

2. Selecting a Date in Column A will automatically adjust All the Data Validations Lists to select Names

All previous modifications have obviously stayed in place, and you can use Column A & B in the sheet "Days Off" to input your data

Hope this will help

Great ... so, in terms of streamlining, understand we can move on ... and tackle step 2 ...

Will get back to you as soon as feasible

Hi again,

To specifically answer your initial question ... attached is your test file.

Just Double-Click on any ISO code to run your macro

P.S. If I may add a side remark ...regarding the template you are using and more precisely :

the macro created by Nancy De Waele in July 2021 ... you need to know it was generated by the macro recorder, which means it not fully optimized ...

Thanks a lot for your Thanks AND for the Like 

Yes, the code can be used for all types of Excel files

Quote from freemindagain

A

Makes sense ... !!!