Data validation in Excel

Copper Contributor

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

12 Replies

@auriane78 

 

Hi

Can you upload a copy of your file so we can see what you have?

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 !

@auriane78 

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.

@Sergei Baklan 

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 !

@auriane78 

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

image.png

After that you may use it in Data Validation list

image.png

Same for other two lists (I didn't change it, just take above formula, adjust the ranges and add with new names).

@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

@Sergei Baklan 

 

Thanks a lot, I will try this.

Best Auriane

@Roger Govier 

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

@auriane78 

 

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.

@Roger Govier 

 

Where exactly in my file? Can't find it ^^'

@auriane78 

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))

 

Basically the Match "ZZZZ" just finds the last cell in the column with any text it it, so it defines the row in the column to use for the end of the range.

 

Formulas tab > Name Manager > New >Enter name and then Paste he formula into the Refers_to box and click Close

Repeat for all three names.

 

The file I sent, already has that done and the validation has been altered on the 01.19 sheet in cells C6:J30 so you can test it there.

 

If you get stuck, feel free to email me direct at roger@technology4u.co.uk

@Roger Govier 

 

Thanks a lot :) i think it should be ok.

 

Best

Auriane