Shared Workbook, limit a dropdown

[dave3009]

Hi All

Thanks for looking

I have been getting loads of help on this subject but I encountered a problem the minute I shared my workbook. The arrays cannot be updated using dropdowns, they work and treat in a single workbook.

So to get around this issue I created a new column which works but I get loads of blanks at the bottom of the box. I want to not have these, in my global settings I use.

=OFFSET(globalSettings!$V$1, 1, 0, MATCH("*", globalSettings!$V:$V))

I tried -COUNTBLANK and -COUNTIF(globalSettings!$V:$V, "") and a few others.

Looking forward to some solutions

Regards


Dave

 

[GlennUK]

How about:

=OFFSET(globalSettings!$V$1, 1, 0, COUNTA(globalSettings!$V:$V)-1,1)

?

 

[dave3009]

Hi GlennUK

That doesn't seem to work

Excel Workbook
	U	V
11	25/08/2011 1pm(V)	25/08/2011 1pm(V)
12	26/08/2011 8.30am(V)	26/08/2011 8.30am(V)
13	26/08/2011 1pm(V)	26/08/2011 1pm(V)
14	#NUM!
15	#NUM!
16	#NUM!
17	#NUM!
Globals

Excel 2007

Cell Formulas
Range	Formula
V11	=IFERROR(U11, "")

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook
Name	Refers To
Assessment	=Globals!$F:$F
Booked	=OFFSET(Globals!$J$1, 1, 0, COUNTA(Globals!$J:$J)-1, 1)
Workbook Defined Names

Everything I try leaves the offset dynamic range reaching for where the formulas end.

It worked perfectly until I shared the workbook then I got the error about not changing an array in a shared workbook, so I created the column V to make things work but I get a ton of blanks in my dropdown validations and I could do with it just looking neat.

Thanks


Dave

 

[dave3009]

Seems this works

=OFFSET(globalSettings!$V$1, 1, 0,SUMPRODUCT(--(LEN(globalSettings!$V:$V)>1))-1)

If anyone has any more efficient ways of doing this then I'd be glad to hear :D