1

I've got a Data Validation in one worksheet, where its source is a full column in another worksheet in the same workbook, which gets it's info from an Access Database.

The table from Access has headers of course, so when I do the drop down list, it gives me the value in the header row as my first choice. Is there a way to use the whole column, but remove the header? The length of the used column will vary, otherwise, I'd just select a fixed range.

Improve this question
3
  • 1
    If you're using Excel365 then use FILTER (and or DROP if you have it) Commented Nov 4, 2022 at 20:03
  • DROP() is better ==> DROP(array,1) as @chrisneilsen sir said. Also with TAKE() ==> =TAKE(array,-ROWS(array)+1) Commented Nov 4, 2022 at 21:34
  • 1
    @MayukhBhattacharya Thanks, I successfully used the DROP method to work it out. Didn't know that one existed. You rock. Commented Nov 10, 2022 at 22:29

1 Answer 1

Reset to default
-1

Select the range from the second cell and below with as much spare cell as required even till down. Excel will show the values only.

Somebody minused. ?

Sample:

Screenshot

So the solution works. The requirement to select the hole column is due to XY problem and should not be considered.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It wasn't me (the person who posted it), but I'm guessing because it was more of a band-aid than a fix. But appreciate the thought and assistance. I did end up using the "=DROP(array,1)" method suggested above, and it worked perfectly. support.microsoft.com/en-us/office/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.