Forum Discussion

auriane78's avatar
auriane78
Copper Contributor
Jul 16, 2019

Data validation in Excel

Hi all,

 

I have a big problem. I am making some Excel shets for my work, and I am using some data validation lists. The problem is that I set up some lists and two days later, I did nothing, but the lists are refering to different cells than the ones I set up.

 

Do you have any idea how to fix this?

 

Thanks a lot,

Auriane

excel
Formulas and Functions

12 Replies

      • Roger Govier's avatar
        Roger Govier
        Brass Contributor
        Jul 16, 2019

        auriane78 

         

        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

Resources