Data Validation question regarding OFFSET

[Bonzopookie]

Hello,

I hope this is easy to understand:

I am trying to prevent a user from entering data into specific worksheet cells, by setting up criteria in a drop-down list and then using Data Validation Custom OFFSET formula.

My Drop-down List contains the following info:

DRINK
FOOD
ACTIVITY
WEIGH-IN

I am successfully able to create the OFFSET, using the following formula:

=OFFSET($A$16,0,-3)="WEIGH-IN"

I can also do this in another range of cells for ACTIVITY.

The problem I am having, is that I want to create another OFFSET, in a different column, that can prevent data entry if a user selects either DRINK or FOOD from the drop-down list. Can this be done with an OFFSET formula?

Thank you for any help. I can provide the source, if needed. I am somewhat of a newbie to this site, so hopefully I am following the respected guidelines.

[mikeTRON]

It sounds like you are trying to create dynamic dependent drop downs with data validation. Which is possible but a bit of a pain to setup and manage if it changes frequently, ESPECIALLY if you have a lot of combinations possible.

https://www.google.com/search?source...7j0l5.6135j0j7

[HWScott]

I made this cheat sheet for myself to help when creating Dependent Dropdowns. Saves me a ton of time.

[Bonzopookie]

Thank you so much! I will take a look at this cheat sheet right now, and I will definitely respond after I can sort it all out. FYI- I am using this sheet as a personal fitness tracker. Between this and my budget, I am trying to teach myself how to use Excel. I like to customize things to my liking, instead of using apps.

[Bonzopookie]

Thank you so much for your feedback. I have exhausted reading about the Dependent Drop-Downs. I agree that they can be tough to manage, but this sheet is really only being used by myself, so my hope is to build it, and let it (hopefully) work from year to year.

Also, I downloaded and looked over the sample Excel sheet you provided. Again, it was helpful in teaching me how dependent drop downs work, however my table is set up differently (the columns of data are not side-by-side).


I am enclosing my workbook. The columns with "x" in the cells, represent the cells that I would like to prevent data entry from occurring in.

As you will see, I was able to get it to work for the "ACTIVITY CHART", however I cannot get the "NUTRITION CHART" to work, because I want the user to choose between either "FOOD" or "DRINK" from the pull-down menu. Is this possible to do in the Data Validation Menu?


UPDATE: I cannot upload my attachment file. I am looking into this, because I have read the instructions for uploading attachments. The attachment link is not working, and the settings tab shows that my excel file upload is "in progress", after quite awhile. Something might be wrong with my browser settings. I contacted site admin and will hopefully be able to upload it soon. If anybody has suggestions for this problem, i welcome them too.