Forum Discussion
Data validation in Excel
It is the D and E collumns of the 12 first tabs which refer to 'Parametrage'N61:N68 and 'Parametrage'M61:M:68'
Thanks a lot !
Hi
I posted a reply around 10:30 but it does not seem to have come through.
Basically, what I had said was you would be better off using defined names to hold the DV lists.
I created three new ones in your file
Lookups =Paramétrage!$I$59:INDEX(Paramétrage!$K:$K,MATCH("zzzz",Paramétrage!$I:$I)) Saisie_horaire =Paramétrage!$B$60:$B$80 Tâches =Paramétrage!$I$60:INDEX(Paramétrage!$I:$I,MATCH("ZZZZ",Paramétrage!$I:$I)) I applied these on your sheet 01.19 in rows 6:30 in column C with DV =Tâches
I removed DV from columns D and E as their values are being returned by formulae, which I amended to
=IFERROR(VLOOKUP(C6,Lookups,2,FALSE),"")
=IFERROR(VLOOKUP(C6,Lookups,3,FALSE),"")
and columns G:J had the DV =Saisie_horaire
You would need to copy these DV's to the other areas of you sheet, and to the other sheets in the file.
I also suggested, that I would take a different approach to entering the data and not have separate sheets for each month and several sections to be able to hold all the days of the Month.
I would prefer to have a single Table holding all of the data set out as I have shown on sheet Suggestion.
Tee table would automatically have the DV and formulae applied as you create more rows.
THis can then be filtered to show your totals for each month or any other selection or you could produce a Pivot Table to summarise all results. I have shown an example.
Holding all the data in a single table makes it much easier to produce any summaries that you want.
The Summaries that you have in N117:S191 could easily be produced on a separate sheet, with a simple selection cell for the month, which would alter the result according to month selected.
Take a look at the attached file
Thank you, I will try to use the function 'Offset'.
For the rest, the problem is that my boss wants it in this format, I don't have a word to say bout it..
Thanks again
Auriane
That's the problem with bosses!!!
Rather than use Offset, use Index (as Sergei agreed, this is not volatile)
Copy the formulae in my file for the names ranges to your file and all should work OK.
I'm not sure why do you need both VLOOKUP and data validation list in D and E, VLOOKUP is enough.
Anyway, in D your data validation list refers on the same table, not on Parameters. In addition, better to use dynamic list, as variant with OFFSET as
=OFFSET(Paramétrage!I$60:$I$90,0,0,COUNTA(Paramétrage!$I$60:$I$90))
in C6 on 03.19. I also adjusted lists in D6 and E6 in the same sheet.
1. The Vlookup permits to find the correspondancy, and sometimes this correspondancy is multiple so the text that appears is 'to define' and there you use the list to define whih prescriptor/ country it is.
2. Effectively, the list does not correpond to the parameter page as define and that is my problem. I set it up so that it corresponds to the parameter page and out of nowhere it doesn't work anymore.
3. I do not get the Offset formula, could you developp?
Thanks a lot for your time and energy !
OFFSET() is available on all versions of Excel starting from 2007 (Windows) https://support.office.com/en-us/article/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66
However, since you have a lot of cells with data validation, from performance point of view better to use INDEX() for the dynamic list. For the first list enter the formula
=Paramétrage!$I$60:INDEX(Paramétrage!$I$60:$I$90,COUNTA(Paramétrage!$I$60:$I$90))
in Formulas->Name Manager and give it the name, e.g TacheList
After that you may use it in Data Validation list
Same for other two lists (I didn't change it, just take above formula, adjust the ranges and add with new names).
