Data validation - does not save =OFFSET(lista_rep!$C$5,0,0,COUNTA....

[JonasB]

Hi there :)

When I save the file ( worksheet ) it does not save with the function, why ?

Sheet 1 = data validation is where I fetch the named range cells from sheet 2. The function works but it does not save it. Next time I open the file the function has gone.

Sheet 2 = It is the named range called "lista_rep"


=OFFSET(lista_rep!$C$5,0,0,COUNTA(lista_rep!$C$5:$C$34),1)

Any tips?

Thank you / Danke / Gracias / Merci / Grazie :bow:

[JonasB]

:hi:

[p45cal]

I don't know, but you have a sheet called lista_rep AND a named range of the same name; I would give them different names.

[JonasB]

Hi there :)


I am at a sheet called "dados" and from it I am fetching a list of customers ( range name called lista_rep ) in a sheet called "Repres”.


Thank you for your help :)

[p45cal]

that formula:
=OFFSET(lista_rep!$C$5,0,0,COUNTA(lista_rep!$C$5:$C$34),1)
is expecting a sheet called lista_rep.

I'm guessing now: you have a named range lista_rep, a column of cells, but you only want to see in data validation as many cells as have data in them. For that the data validation formula might be:
=OFFSET(lista_rep,0,0,COUNTA(lista_rep))

Otherwise upload a workbook and explain what you're trying to do.

[JonasB]

Perfect :bow:

It worked.

Thank you

[JonasB]

Ooops :doh: Sorry...




The dropdown shows the customers list perfectly but there is one thing that it should do.
Validate the named range "list_rep".


=OFFSET(lista_rep,0,0,COUNTA(lista_rep))




I’ve tried the 1st function but Excel does not accept.


Excel show the message:
“excel you may not use references to other workbooks for data validation criteria"Thank you

[p45cal]

Not at all clear on what you're trying to do, so upload a file and explain a bit more.

[JonasB]

Very good Sir, allow me to express myself clearer :)


Sheet 1 called dados

This function brings the customers' list to the sheet below called dados, but it does not validate the list... it allows me to input anything.

=OFFSET(lista_rep,0,0,COUNTA(lista_rep))

27847

Sheet 2 called = Repres

The offset function should / have to allow only names from the list below. ( range name = list_repres )
=OFFSET(lista_rep,0,0,COUNTA(lista_rep))

27848

Thank you :)

[p45cal]

try:
27849

[JonasB]

Hi there :)

Yep, it worked. It validates the drop down list but if I press enter the cells will be blank.

You helped me a lot.

Thank you so much.