Free Microsoft Excel 2013 Quick Reference

RTD and data validation

I have a spreadsheet that uses the RTD formula to retrieve dividend info on a list
of stocks. I also have a static column that contains what I expect to be accurate dividend info. Basically, I want to compare the RTD results against my static data. If there is a discrepancy, I want to be notified by a message box and manually change the the static data so the two match. Do I have to pause the RTD somehow so that I can have time to react to the messagebox (before the message box appears again)? Thanks.

Post your answer or comment

comments powered by Disqus
I'm trying to accomplish the same as in this thread HTML Code:


Re: Lock/Unlock Cells Based On Validation Lists
VBA is subject to users choosing to enable macros - which they can elect not to do and thus defeat the code.

First - there is no validation list in cell F11 (or other cells) and the actual cell containing the value is F10 - you have the cells merged (bad idea - causes problems).
However, you can accomplish this without the use of VBA by using Conditional Formatting and Data Validation in conjunction with a helper cell.

Using column-Z as the help column, you could enter this formula: =F10="On Leave"
Which will return TRUE or FALSE; TRUE = F10 contains "On Leave"; FALSE = F10 does not contain it

Set cells J10:P11 to use Conditional Formatting, formula option, using this formula; =$Z10=TRUE
Select a gray fill color
Cells J10:P11 will be colored gray if Z10 = TRUE due to F10 containing "On Leave"

Next . . .
Apply Data Validation to cells J10:P11, using the Custom option and use this formula: =$Z10=FALSE
Set the Error Alert style to Stop and create a custom message if desired.
When Z10 = False it means cells F10 contains "Active" so users can make entries. If Z10 = False then cell F10 contains "On Leave" and Data Validation will prevent users from making entries I applied this to my spreadsheet, but somehow when the cells are supposed to be cleared of the conditional formatting, only the top cell clears and the rest stays black (color I used).

I did all of it just the same.



then for range =$A$3:$E$24
=G1=FALSE then it colors everything black. then I clicked the box Stop if True. when the cell a1 has data, it only clears a3 and the rest stays black

Is it possible to have both Vlookup and data validation names linked to an external file? I've figured out how to reference the Vlookup but not the names issue...



Hi All

I'm trying to put a form control combo boxes and data validation in various cells of a spreadsheet. Unfortunately, users with Macs cab see the combo boxes and data validation cells, they can select the drop down arrows and see the selections, but when they want to click on any of the selections it will not transfer it to the cell. Is there any configuration settings I have to do in the excel program for macs



Ok so I'm using VLOOKUP and data validation to create a list, howeveri was wondering, would it be possible to have a scroll list, that when you choose a item opens a new list, for example you click the drop down list and choose ranging items, which now triggers another drop down list, then when you choose melee items from the first drop down list, the second drop down list has different items in it.???

if anymore description is needed inform me..

thnx- Smithswood

Here is the code I use in the worksheet object. You double click to insert a line below the selected line.

    Dim LR As Integer 
    If Target.Column > 1 Then Exit Sub 
    Cancel = True 
    If MsgBox("Do you want to insert a new row below this row?", vbYesNo) = vbYes Then 
        With Target 
            Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 14)).Select 
            Selection.Offset(1).Insert shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow 
            Exit Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
That part works... however in column's E, J and M and N I have the following that won't copy and fill down accordingly...

E - Formula
J - Conditional Format
M - Formula
N - Data Validation

The formula's are as follows

E - =IF(ISBLANK(D10),(""),ROUNDDOWN((MINUTE(V10)-MINUTE(T10))/60,1))
M - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))

The conditional format in J are:

If the cell value = "REP" then Orange text applies to =$J$10
If the cell value = "REC" then Red text applies to = $J$10

The Data validation in N is based off of cell M (a formula)

The data validation is "allow list" and source is "=INDIRECT($M$10)"

How do I copy these to the new lnserted line and then fill down to the end of my document, which will be no further than Row 150?

When I insert a line, the formula for instance in M goes like this

Line Above - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))
Inserted Line - (blank nothing there)
Line Below - =IF(ISBLANK(R11),(""),(CONCATENATE(R11,"",W11)))

I need it to fill this way.. for this column

Line Above - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))
Inserted Line - =IF(ISBLANK(R11),(""),(CONCATENATE(R11,"",W11)))
Line Below - =IF(ISBLANK(R12),(""),(CONCATENATE(R12,"",W12)))

I hope I explained it well enough... basically I want to insert a line, and it copy and fill down the data accordingily...

thanks in advance!!

Hello All,

Ok, I am just getting into the macro aspect of excel, so please be patient with me. I basically am trying to create a macro that creates a new row of data under existing data in a table (not really a table but just a range as of now). Here are my two main criteria:

1) I have some cells that have data validation, some cells with formulas, and some cells with just text (really symbols) that I want copied down from the row above into the newly created row. I need all cells (other than the rows with symbols) to show as blank, however upon creating the new row.
2) I want the row to always be created after the last row of data.

I have tried using the macro recorder to try doing this as well as researched everywhere online but I am not experienced enough in VBA to get the syntax down quite right for what I am trying to do.

I have attached the workbook for your reference. To put it more clearly:

- I want to create a Macro that adds a row after the last row of data in the table. Currently this would be after row 22, however, after adding another row, I would want it added after row 23 and so forth and so on.
- I would like it to copy the formulas and data validation in cells C22, D22, E22, and I22, but not I don't want any selections made for the cells with data validations (D22:E22). As long as those cells are clear, cell C22 should be empty based on the vlookup formula in that cell.
- I would like it to copy the contents from cells F22 & H22 as is in the new row.

Thank you so much for any help you can provide!!

I have a Data Validation box that needs to produce a drop-down list of options depending on the value of another (dynamic) field. I could do this using nested Ifs, but......the dynamic field can now have one of 12 values (i.e. more than the limit of nested IFs).

My first thoughts were to use VLOOKUP, i.e. using the name in the dynamic field as the lookup, and then return the name of a Named Range as the result (then Data Validation would get it's list from the Named Range)....but I can't seem to get it to work. My 'code' in the Data Validation Source is as follows (I have selected the List option):


Where I2 is field containing the dynamic value,
$E$55:$F$65 contains a column with all the possible dynamic field names plus a matching column of Named Ranges (in the second column).

When I enter the code above, I get "The source list must be a delimited list, or a reference to a single row or column.

Any clues? Any other ways of doing this?

Dear Smartest Excelers In The World,

Is there a way to have Data Validation List and Data Validation Custom formula in the same formula?

Here is the complete problem description:

1) I made a list of 20 first-names of people on Sheet3, one first-name per cell, no formulas or anything on Sheet3, it's all typed letter by letter.

2) I named those cells on Sheet3 NAMES so that I can use Data Validation on it afterwards

3) I made 15 data validation cells on sheet1, each one is set to allow: LIST and the source for each one is NAMES

4) I'm happy because in each of those data validation cells on Sheet1 I have a dropdown list allowing me to choose amongst any of the firstnames on sheet3

5) I'm unhappy because I can choose one firstname in one data validation cell in sheet1 AND THE SAME ONE in another data validation cell in sheet1
(And this is for a timetable so I don't want 2 firstnames to be able to have the same timetable)

6) I found a method for disallowing duplicates amongst all my data validation cells: (hypothesis: let's say my 15 Data Validation cells on sheet1 are in A1:A15)
I select A1 then I select A1:A15, I go to DATA VALIDATION, I allow CUSTOM, and in SOURCE I put: =COUNTIF($A$1:$A$15,A1)=1

7) Now I cannot put the SAME firstname, in 2 different cells amongst my15 data validating cells in sheet1 BUT I lost my drop down list!!!

8) I want both at the SAME TIME in each of my 15 data validation cells on sheet1!!! (A dropdown data validation list AND disallow duplicates amongst those 15 data validation cells on sheet1)

Any ideas?

I am trying to do a Data Validation and drop down box for a series of cells. However I don't want to validate against the whole cell just a part (left, mid, right).

I wanted to find a way to do something like =LEFT(D5:D9,2) in a formula in the data validation. However LEFT, MID, RIGHT doe not support an arrays and data validation does not like anything close to that.

Next I tried doing a named range and doing a LEFT in front of the named range. I use named ranges for other data validation issues when crossing sheets etc.

This did not work in Excel 2003, but strangely enough did work in Open Office 2.4.

I know that I could hide some rows that contain the LEFT, MID, RIGHT info and just do the data validation on those, but that solution was not as clean as I really liked.

I was trying to find a good way to do this with just formulas as I may share the sheet and Macros put up the security warnings.

Any ideas or thoughts would be appreciated.




I have a globally defined named range called "Year" that refers to

In a worksheet "Model (1)" I have a B1 cell on which the data validation is set to "Pick from list", and that list is defined as

So far so good. I believe that this would not have worked had the name "Year" been localised to "Rates!Year". A named range referred to in data validation has to be either local to the worksheet containing the validated cell, or else a global name. It cannot be localised to another sheet.

Also in worksheet "Model (1)" I have another cell C1 which contains a formula

I wish to create a duplicate of worksheet "Model (1)", which will be called "Model (2)". In the worksheet "Model (2)" I wish to be able to select an entry for cell B1 by Pick from list, with reference to the list contained in the range Year. And I should like the corresponding cell C1 in "Model (2)" to evaluate to =Match('Model (2)'!B1,Year,0).

At some later date I may wish to add additonal entries to Year, without having to update all of the "Model (x)" worksheets.

As it stands, the whole thing falls down, because when I create a duplicate of "Model (1)", the cell 'Model (2)'!C1 evaluates to #N/A while the Pick from list validation in cell 'Model (2)'!B1 is disabled, because the named range referred to in the validation evaluates to an error.

The cause of the problem seems to be that when I duplicate Model (1), Excel also creates a duplicate of the name "Year" which it localises to "Model (2)", and both the validation and formulae choose to refer to the localised name in preference to the global one

I could solve this by VBA, so that whenever I create a duplicate worksheet I delete the corresponding localised name, and correct the formula and validation as necessary in cells B1 and C1 of the new worksheet, all within the VBA routine. But I would rather have a solution that does not involve VBA (there are currently no VBA modules in this workbook and I would rather it stayed that way).

This is surely a wheel that has already been invented, so I should be grateful for some pointers.


Is it possible to create a Dynamic Data Validation list based on a formula that looks up information based on another value?


1 Smith
2 Jones

1 Smith Dan
1 Smith Jane
1 Smith Bob
2 Jones Larry
2 Jones Frank

User looks at the data validation cell list and sees the 3 Smiths or the 2 Jones depending on the lookup value.

So when I paste a new list into my spreadsheet I have a specific cell with the Data Validation looking at a formula that will populate based on the lookup.

I would like to avoid use VBA if possible.



I have just been upgraded to Excel 2007 and whilst there are some very useful features I am somewhat dismayed that there are old features that are no longer supported.

Specifically, I want to create a data validation statement that allows the contents of a list and dynamically adjusts itself to the length of the list. In the good old days I created a Names Range, defining the range by =OFFSET($AA$1,0,0,COUNTA($AA:$AA),1) for example and then referenced that name in the data validation statement.

Looking at some of the blurb on the Excel pages it appears that Excel 2007 no longer supports named ranges. My question, therefore is twofold:

1 How can I achieve the same in Excel 2007, and

2 Can I save a workbook created in Excel 2007 and preserve this functionality for earlier versions?

Many thanks


On the spreadsheet that I am working on i have some Data Validation in cell B2. This is the cell that is active when you open my Read Only spreadsheet. The Data validation messgae appears when the sheet is open but only for a split second and then it disappears. In order to get the Validation message back you need to click on the box. Can anyone tell me why this would happen. I even tried putting in something like this:
Private Sub Workbook_Open()
Application.ScreenUpdating = True
Application.ScreenUpdating = True

End Sub
The Message still will not remain on screen.

Any suggestions?


I have 7 cells in a column. In each cell in the range, I want the only
choice for entry to be TRUE. (If the statement isn't TRUE they will leave
blank.) I also want to specify Data Validation Criteria and generate an
error message so that if a user enters TRUE in more than 1 of the cells in
the range, an error message will be generated telling the user they can only
have True in only 1 of the 7 cells in the range. Can this be done and if so,

How can I enter a format for a number that will appear when input as
follows, and also have a Data Validation that ensures there is 8 numbers
typed in

0 1 2 3 4 5 6 7 (not there is a single space between characters)

I have tried format as Custom # # # # # # # # with a Data Validation of 8 -
but this doesn't work where the first number is Zero, I assume Excel doesn't
recognise the Zero and thinks that there is only 7 numbers enter

I need the spacing between each, thanks

Hi All,
I've written a macro for a rather large spreadsheet (about 150 rows and
20,000 columns) that applies data validation and conditional formatting to
look for data entry that doesn't meet certain sets of conditions. What I want
to know is if there is a way to find cells where the conditional formatting
has returned true, thus highlighting the cell, without having to scroll
through the wntire sheet. Likewise I would like to know if there is a faster
way to find cells that have data that is invalid. I know that you can find
cells that contain conitional formatting and data validation, but in my case
all of the cells contain these. Any ideas?

All on one worksheet, I have a bunch of data validated cells in multiple
columns and rows (ranging from B24 to K68) which pull from a master name
list in L2:L50. An exact match is not required, allowing me to type new
names in the data validated cells if the name I want isn't already on the

What I'd like to do is put a formula in my unused list cells that reference
the range of data validated cells [and the names above itself in the list],
and if it finds a name that /is/ in the data validated cells but /isn't/ in
the list, show that value in the cell- essentially, so if I (or another
user) types in a new name, it also is automatically added to the main list
and is thereafter available in any of the other data validated cells/lists
without having to re-type it.

My brain hurts just thinking about it- so before I spend a lot of time, has
anyone else set something like this up, and if so, can you share what
approach you found most workable?


The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my

Hi All,

good evening - monday tomorrow! yeyyyy! i'll get some peace and quiet after a hectic w'end with wife ordering me around and children requiring lots of attention!!

Please find attached a sample pivot table.

I have four field headings in Report Filter, Region (B1), Site Name (B2), Month and Number of Nights. At the moment when I select a region and then a site name - I can still see ALL site names, I would like that when I apply a filter on Region that i can only see the sites associated with that region when i then click on B2. Much like a data validation list.

My actual data set is some 400,000 rows..........

Ideas on a postcard!! thanks guys!


I have a problem with a spreadsheet (attached). The problem (for me) is quite complex and any suggestions would be very welcome. My spreadsheet has three sheets - Costings, Materials, and Validations. In the Costings sheet I have three columns - A, B, C. I am trying to create a complicated dependent list where Column A determines the list in column B, and Column B determine the list in Column C. I have got as far as getting column A to determine the choices in column B using the INDIRECT function with the data ranges being named as the data validation cells for column A:


However column C is more difficult as different values in A and B must dictate the list in C. For example if the value in A contains "Offshore Plate" AND "Carbon Steel" the validation list in column C must be the range listed under "OffshorePlate" in the validations sheet. Again this works using the INDIRECT function by combining the values of columns A and B and removing the spaces.


However if column A is "SAW Tubular" then Column C will be pointing towards two different validation ranges depending on what is selected in column B. What I want the sheet to do is select the range "Alloysawt", if column B contains anything but "carbon steel" or "carbon steel EU stock". If column B does contain "carbon steel" then I want column C to display the data validation range "Sawt".

Also if column A contains "Structural Hollow Section" I want column C to look up the value in B and then display the data range "Struchs" if any of the lookup values match the contents of the cell in column B. I guess this is another case where 2 conditions need to be met in order to display the correct validation list in column C.

Is this possible? if so could someone please help! I am struggling with this!


I have created a generic form which uses drop-down boxes (data
validation option List) to select one field which automatically updates
other fields using a vlookup function linked to a named range on
another sheet.

Lots of people use this form and each time enter their name and the
date into the form as well as selecting an item from the drop down

i have written a macro to clean the sheet up ready for a new user, as I
am not able to make it read only.

i have no problem clearing the name field (cell)
The problem I have is I want the drop-down box field to set back to a
default value in the list, rather than stay in th eformat it was saved.

i am struggling to find code to manage data validation and drop down
boxes in this way

any ideas?


I have 7 cells in a column. In each cell in the range, I want the only
choice for entry to be TRUE. (If the statement isn't TRUE they will leave
blank.) I also want to specify Data Validation Criteria and generate an
error message so that if a user enters TRUE in more than 1 of the cells in
the range, an error message will be generated telling the user they can only
have True in only 1 of the 7 cells in the range. Can this be done and if so,

I'm making an inventory management spreadsheet for a local band and I am trying to "idiot-proof" the spreadsheet. I want to know if there is a way to have a drop-down list for tee shirt sizes (data validation style) show up ONLY if "tee shirt" is selected. I have a similar issue for the "styles" of tee shirt but I figure the answer will be the same for both problems.

I attached my project file and there are #VALUE errors where I'm having the problems.

Just to make sure I have my issue written in plain English:

When "tee shirt" is selected for purchase, I want options for "style" and "size" to be available as a drop-down list. If any other option is selected, I want the cell to be blank. I can't figure out how to do that or if it's even possible.


I added "$" in the lookup so I only get an error when the cell says "tee shirt" but that doesn't really help.

Hi All,

I have some issues while working on Worksheet change event for range of cells.
I have one sheet in which user have to update required fields and
click on send email command button which sends mail.
I have assigned sending email macro to command button And other data is stored from range A4:H7(this range is not constant.May
vary in other sheets).
Now there are three last columns and Send email command button for which i need help
Column F-- Status Column-- Data validation is done so only allows values Active or Inactive
Coolumn G- Effective Inactive Date--This column is blank. This value should be entered when column F values is selected as
Column H - Review -- Data validation is done so only allows values Yes or No

Now I require following things working:
1. Column F:Status Column --> a. If user selects Inactive value then message box should pop up asking user to enter susbsequent Effective Inactive Date value.

b.Also want subsequent Effective Date value made mandatory for user if status is Inactive.

c. If field in this column is left blank then Command button(here SendEmail) button should not work and one message box should pop up upon clicking command button asking user to update require fields

2. Column H: Status -->
a. If field in this column is selected as No or left blank then Command button(here SendEmail) button should not work and one message box should pop up upon clicking command button asking user to update require fields

Please help me out as this issue is really freaking me out.

I have been beating my head against the wall all weekend on how to set this up and cannot for the life of me get it. Of course I am a newbie to all this and would appreciate all the help.

"Workout" Sheet I have a drop down list for Col B and Data Validation for Col C. I want a Vlookup set up for Col G dependent on what is brought up under Column C.

Please help!!!

No luck finding an answer? You could always try Google.