Jul 16 2019 01:11 AM
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
Jul 16 2019 01:35 AM
Jul 16 2019 01:41 AM
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 !
Jul 16 2019 03:09 AM
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.
Jul 16 2019 03:17 AM
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 !
Jul 16 2019 03:54 AM
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).
Jul 16 2019 04:23 AM
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
Jul 16 2019 05:07 AM
Jul 16 2019 05:08 AM
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
Jul 16 2019 05:12 AM
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.
Jul 16 2019 05:27 AM
Jul 16 2019 05:43 AM
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
Jul 16 2019 06:12 AM