Dynamic Range For Data Validation Causing Save Error In Excel 2016 For Mac

Hi All I'm trying to link to a dynamic range based on the input in a different cell. I'm using indirects in data validation to call up the correct range. The code snippet below changes the data validation state of a cell and runs when the Excel-2003 worksheet is unprotected. However, when I protect the work sheet the macro doesn't run and raises a run-time error.

I think I understand what you are talking about but I will type an example from one of my sheets. This example is referencing 2 different sets of date ranges, as well as matching a lookup value in another range from a pivot table. Finally it sums up the dynamic range it finds.

(incorrect example) =SUM(IF($A1:$A1$E$4,INDIRECT('Sheet1!C'&MATCH($B$48,$B1:$PN1,0)&':PN'&MATCH($B$48,$B1:$PN1,0))))). Range A1:A1 is a date range. E4 is the date range we want the data from. B48 is the product I'm matching in the pivot table in Sheet1. I believe the problem you are having is caused by not using equal ranges for the two dynamic ranges being evaluated in the INDIRECT function.

So the ranges that are involved are the date range which above is $A1:$a1 (whole row) and $B1:$PN1(that lookup array from the pivot table. The problem with the above formula is that the range of dates must be the same length in #Columns as the array being used for the data ($B1:$PN1). So you can either change the date range to $A1:$PN1 or change the data range to $B1:$B1. It took me about eight hours to finally figure out this problem. I hope that answers your question clearly (this is my first post).

Greetings, I am using a named range in a data validation list to enter standard values in a cell. Occasionally there are values that need to be entered that are not already in the list, which of course kicks out the restricted entry message. Adding the new value to the named range is easy enough for me as the author, but not so easy for all of the users of the workbook.

Is there a 'wildcard' code or formula that will allow the user to enter in a new value to the cell if the ones offered in the drop down are not the ones that they are looking for? Hi there, I have a spreadsheet with some cells setup with a drop-down list containing Y, N or N/A This is being used on a TabletPC but if I make a mistake or need to change back to a blank field I have to invoke the soft keyboard, activate the cell and hit backspace then close the soft keyboard - quite a long-winded procedure just to change an incorrect choice! What I would like to do is add a blank to the list so if I have to revert back to a blank I can just use the stylus to choose a blank from the chooser list. How do I add the option of inputting a blank from the Data Validation List bearing in mind I am using the Data Validation Source box for entering my choices directly and not specifying a range of cells? I have tried adding ' and even a space to no avail. Although not a betting man I would wager there is a simple 'fix' for this but things are only simple if you know how in the first instance!;^) -- Thanks & regards, -pp.

I found this solution for 'drop down list with hyperlink' but it did not work. Perhaps a better solution is to use a workaround that relies on the HYPERLINK function to refer to whatever is selected in the drop-down list. For instance, if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1: =HYPERLINK(A1, 'Goto Link') The solution directly above provides exactly what I am looking for in the field where I write the formula, but it fails to hyperlink.

I have created a drop down list and linked each one of them to a specific worksheet. When I select them individually they link to appropriate worksheet. But when I select them in the drop down list I receive the following error when I select the Hyperlink in cell B1 as directed above. 'Cannot open the specified file' Any thoughts? Is it possible to assign values to names in a list, so that when you validate it as a drop-down list, you can select a name from the drop-down and it's corresponding value will be added to separate cell?