Automatically Update Drop Down List To Show Exact Amount Of Choices
Hi,
I'm trying to set up my lists in Name Manager to refer to all cells in a column that contain data. Here is what I've got so far:
=OFFSET(Values!$A$2, 0, 0, COUNTA(Values!$A:$A),1)
This works apart from the problem that it displays one empty value beneath the choices in the drop down list. What am I doing wrong?
There is so much good in the worst of us,
And so much bad in the best of us,
That it hardly behooves any of us
To talk about the rest of us.
Try
=OFFSET(Values!$A$2,0,0,COUNTA(Values!$A:$A)-1,1)
It looks like you have a value in A1 that you don't want in your list but it is being counted in the COUNTA(A:A)
hi swordswinger710, does A1 contain a header? your COUNTA might have included it & when u start from A2, it gave u an extra row
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hi
I usually,use this formula for Named LIst..
=OFFSET(Values!$A$2,,,MATCH("*",Values!$A$2:$A$5000,-1))
Is this, works for you?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
benishiryo, you were right, Cutter and Fotis1991's solutions fixed that though.Thank you all!
You're welcome. Thanks for the 'star tap'.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks