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.