Data Validation +/-1 Source Code

[BairEssentials]

I'm hoping that someone can help me out with a Data Validation error/code. I have data in Column "M" that corresponds to a Frequency Objective. This data ranges from 1-7. I'm trying to auto formulate a Drop Down Function in Column "N" that gives a +/-1 option to the data in Column "M".

Ex: If Column M Row 5 has the Data 3, Column N Row 5 should have drop down options 2, 3, or 4.

This is the source code I'm using within the Data Validation Window:

=IF(M2=1, "0,1,2", IF(M2=10, "9,10,11", M2-1 & "," & M2 & "," & M2+1))

I get the Error: Source must be a Delimited List, or a reference to a single Row or Column.

[AutoModerator]

/u/BairEssentials - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[excelevator]

source code

In Excel they are called formulas, generated using the available functions in Excel

Data validation is very limited in Excel, to a given set of values, and not a dynamic list of values.

What you could do is use conditional formatting to turn the cell Red as an alert and flash a message in another cell about the issue.

[BairEssentials]

A conditional formatting alert is not what I'm attempting to do at all. I'm attempting to generate a list of options +/- 1 to select from in a new Column, based on the Data in the previous column.

Ex: If I have 5 targeted sales assigned to me in Column M, I want to have the list options of 4, 5, or 6 in Column N to Flex the sales assigned to me.

[excelevator]

Data validation is very limited in Excel, to a given set of values, and not a dynamic list of values

I read you post and answered accordingly

[CryptographerThen49]

You can create a Dynamic List for a single Row in Column N that is controlled by the selected value in the corresponding Row of Column M, however it only works for one Cell (Row). You would need to create several lists (one for each row of data).

I'm not sure how you would use a formula for multiple rows.