Validation Source: Change The List Source

Apr 1, 2009

Not sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?

View 5 Replies


ADVERTISEMENT

Too Much IF In Data Validation List Source

Apr 8, 2014

I try to find a solution (with or without macro) to assign different ranges named "Failure_C..." in a data validation source depending on the value of another data validation list (32 values called)

I started in the Data Validation List Source to fill in IF function but 32 inputs are not accepted and too much caracters in the source.

The formula is starting like that:

=IF(A57=$M$78,Failure_C01,IF(A57=$M$79,Failure_C02,IF(A57=$M$80,Failure_C03,
IF(A57=$M$81,Failure_C04,IF(A57=$M$82,Failure_C05,
IF(A57=$M$83,Failure_C06,IF(A57=$M$84,Failure_C07............

View 2 Replies View Related

Source Validation List In Another Workbook

Jun 25, 2007

Is it possible to have a list from a different workbook as the source list for a validation dropdown on a cell? If so, what is the syntax.

View 2 Replies View Related

List Validation Where Source Resides In Another Workbook

May 6, 2009

i just want to generate a list validation where source resides in another workbook?
i have used named range concept ?but not working can anyone help..

View 9 Replies View Related

VBA To Choose The Source Cells For A List With Data Validation

Mar 10, 2008

when you choose Data Validation for a specific cell, you can choose list. Once you have chosen list for "Allow", you can select the source cells for what will be in the drop-down menu. I want to be able to use VBA (or just use Excel) to modify those source cells.

For example:

There will be 3 lists with drop-down menus using Data Validation.

List 1:
Choose first option in list -> List 2 uses source A
Choose second option in list -> List 2 uses Source B
etc...

List 2:
Choose first option in list -> List 3 uses source C
Choose second option in list -> List 3 uses source D

View 11 Replies View Related

Auto-Update Validation List When New Entries Added To Source

Mar 18, 2008

how to make all cells displaying an item from the list source update with new information added to list source.

Example:
Cell "A1" on worksheet 2 displays the 3rd slot of information from list source on worsheet 5. I want cell "A1" on worksheet 2 to update when the list source information in the 3rd slot on worksheet 5 is changed.

Notes:
The list is set up by naming the columns on Worksheet 5 and is set as a validation list on the other worksheets. So on Worksheet 2 column "A" has the validation

VB:
AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".

List=(Coulumn A 's name on worksheet 5)

View 9 Replies View Related

Automatically Change Cells To New Value When Drop-Down List Source Changes

Aug 1, 2009

I am looking to have the Cells that I have applied a Dropdown List to update with the changes that I make to the list itself.

Example:

If I were to validate a list with a range of a1:a3 using "Bob" "Sarah" "Bill" and then apply that list to B:B and randomly select from the three names running the length of B:B, I would want that when I go back to A2 and change "Sarah" to "Linda" that every "Sarah" that I have selected using the drop down will update to say "Linda"

I hope that I am explaining myself clearly and in a simple context.

View 9 Replies View Related

Use Data Validation That Will Pull Data From A Source List

Jan 12, 2010

Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?

View 3 Replies View Related

Data Validation - Source Currently Evaluates To Error

Apr 28, 2014

I am currently creating a form to hold 2 drop down lists that change depending on the selection in the first one. I had never done this before and taught myself and got it to work minus one problem. As soon as I input the formula under the Data Validation the error: "The Source currently evaluates to an error. Do you want to continue?" pops up. Right now the formula works except for the last 6 options in my first drop down. When I select them nothing pops up in the 2nd drop down menu. I have double checked and triple checked that I have no spaces or anything in my LIST on worksheet 2 or any of the names are input correctly.

A7 is the first drop down - PROPOSED

B7 the second. - ITEM #

Lists and data to populate the lists are on worksheet 2 with the "List" at the bottom of worksheet 2...

View 1 Replies View Related

Range Name + String As Source For Data Validation DropBox

Jul 16, 2009

I have several cells with data validation's drop Box. Every data validation uses a range name as source (myColorList). Here goes the hard part: I need a new value inside the dropBox list, but The problem is that I can NOT modify the "myColorList" range.

-- Is there any way to build up the dropbox source using the range name and the new value? Something like

View 2 Replies View Related

Pivot Table Retains Old Source Data In Addition To New Source Data

Sep 7, 2006

I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).

Does anyone know how to purge the old data from the internal Pivot Table memory?

I hope this is enough information....let me know if you need more.

Thanks in advance for any help,

Jon

View 9 Replies View Related

Break The Link Or Change The Source

Jan 18, 2007

I've recently aquired a spreadsheet previously maintained by someone else. After my initial review the speradsheet has many links that need to be updated, which I do via Edit-Links, but there are also some links that no longer need to exist. I have reviewed all sheets and some links just no longer exist, however when I try to break the link or change the source nothing happens. I have had the original owner also check and confirm and they have the same issue. Finally I tried copying all the tabs into a new workbook, but the link shows up again.

View 3 Replies View Related

Change Chart Source Range

Jun 14, 2007

My spreadsheet has multiple charts, each referencing a dynamic named range as its source data (e.g. "California1", "California2", etc). Each named range contains multiple series. I would like to make it easy for a single chart to quickly adjust to display different named ranges (e.g. view the chart with "California1" data, then update the same chart to view "California2" data).

I'd like to do this without having to right-click on the chart, select Source Data, and then type in "California2" as the Data Range. My question: is there a way to make the chart's Source Data Range dynamic so that I can quickly select the named range that I want to display? For example, would it be possible to tie the Data Range to a separate cell so that if I typed "California2" into that cell the chart would know to use that named range as its new source data?

View 7 Replies View Related

Change Chart Source Data From Add-in

Aug 9, 2007

I have an excel addin that contains chart templates. Without going into too much detail a user will select an option which will update these templates then move the charts along with the source data to a new workbook. My question is this; How do I change the chart references programatically so that the chart data now points to the source data in the new workbook.

View 5 Replies View Related

Change Source Data Of PivotChart

Aug 30, 2007

When attempting to use the following

charts.add

activechart.setsourcedata thisworkbook.worksheets("PTPage1"). range("A1")

I receive the following error: Run-time Error '1004':

The source data of a pivotchart report cannot be changed. You can change the view of data in a pivotchart report by reorganizing its fields or items, or by changing its associated PivotTable report.

View 2 Replies View Related

Change Chart Link Source

Apr 18, 2008

I have a periodic task which involves moving some sheets from an existing workbook and presenting it in a new workbook. There are some diagram that I move as well from the worksheet. However, I also move the source data for the diagrams into the new workbook as well. Everytime I do this I need to change the source data to the new worksheet which is quite tiresome. Is there a smart hack to actually point the diagram source data to the new workbook?

View 2 Replies View Related

Link To External Source Workbook For Data Validation Lookup Values Returns Zeros

Mar 25, 2014

Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.

I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).

I've investigated the techniques in this link: [URL]

Questions:

1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?

2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?

View 5 Replies View Related

Pivot Summation And Change In Sum When Source Value Changed

Nov 26, 2013

Am currently working on an excel sheet that has values in multiple columns and rows. Simple example would be like

Assume two columns , with column One having a dropdown list to select the value ,

Pencil 2
Paper 3
Pencil 3
Eraser 3
Pencil 3

I want the data to be created in another sheet as

Pencil - 8
Paper - 3
Eraser - 3

I understand that this can be done through pivot table but the issue is when I change the quantity of the item in the source , say reduce pencil by 1 , the same should be reflected in the summation sheet as well automatically .

View 5 Replies View Related

Change PT Data Source Using Named Ranges?

Feb 9, 2012

I have a worksheet with many pivot tables and (eventually) 2 data sheets. I run a macro that creates the first pivot table and copies it 17 times. Then, using Call, I would like it to create the second data table, "Credits" (I'm working on that, but that's a seperate issue). Finally, I want one of the existing PT to change to the new data, "Credits".

I'm using an named range and that seems to be where I'm breaking.

Code:
Sub Remove_Credits()
'
Dim CSD As Worksheet

[Code].....

View 4 Replies View Related

Macro To Change Data Source Of Pivot

Mar 8, 2013

I have a Pivot table which is created by running a macro with formula calculated columns.

Q1. when i add another column in data sheet, and change the source manually, i get a error in calculated columns

Q2. I need to add the new column in data sheet to the row labels of the existing pivot sheet. with change formula calculated columns.

Q3. can we have any macro to this ?

View 3 Replies View Related

Change Source Data Of Graph With Combobox?

Apr 17, 2013

I have an 'in-cell dropdown' which looks up two values, Q1 and Q2 I have a range of data by month showing a specific number as below e.g

Month
Count
2012-01
120
2012-02
190
2012-03
200
2012-04
150
2013-05
175

I have a Line graph using this entire table as the source data. What I would like to do is only use part of the data based on what the dropdown cell specifies. For example, if it is set to "Q1", I would like just the 2012-01 and 2012-02 data to show in the graph. If its "Q2", I would like only 2013-03 and 2012-04 data to show in the graph.

View 4 Replies View Related

Change Source Path In PPT Link From Excel?

Dec 13, 2013

I have created a PPT which is linked with my excel data and charts. Now I have to copy it at different location (Both PPT and excel), But due to change in path; my PPT is not updating with latest data points and charts. I know, I can change link using edit link.

My PPT is having around 50+ link. some VBA code which can replace old path using the current path.

View 1 Replies View Related

Change Pivot Table Source File

Jun 12, 2007

I have a workbook with a number of pivot tables as well as the source data for each pivot table. Some of the source data sheets needed to be deleted so now if I try to refresh the corresponding pivot table I get "Connot open pivot table source file '[Std Mthly Rpt.xls]Report_2'". I recreated the source file again and would like to know if I can link the pivot table to this new source file or do I need to create a new pivot table??

View 7 Replies View Related

Code To Change Chart Source Data

Jul 16, 2007

I am in the middle of building a macro for some data to be automated. I have two sheets, one of which has an existing chart but the data has been removed so now there is no values on the chart. I then have a second sheet with data on it. The range is b4:c16. I want to point this data onto my chart on the other sheet.

View 2 Replies View Related

List Box Data Source

May 15, 2009

I have tried putting in the Row Source in the Properties of ListBox1, (A10:A13) but the range is in another worksheet called Codes. So when I run the Form the list box shows the range A10:A13 in the current sheet. I have named the data list SP but when I punch that in it will not accept it in the Row Source. How can I lock in the source data in the list box so it only shows the named range SP.

View 9 Replies View Related

Wildcard List Box Source

Mar 16, 2007

I Need Some Help i need to change the source data in the wildcard listbox, i need to be in same workbook but in another sheet range can be named like employee ...

View 8 Replies View Related

Match Data On Source And Destination Sheet And Change Value?

Jan 17, 2014

I have extracted data from multiple sheets and now want to mark this data as "Paid" and for the macro to go back and change the value in the original sheet on a row in column "AR" to "PAID.
All the columns have the same data type in.

The extracted data contains the original sheet name and each row has a unique ID No to it.

I know I probably need to use INDEX and MATCH and then Offset to post value.

Attached worksheet shows the data and columns.

View 14 Replies View Related

How To Change Source File Without Affecting VLookup Formulas

Jun 6, 2013

I have an excel file - central database - that holds a lot of raw data organized in tables. There are more than 15 files (dependent files) that access this data using VLOOKUP formulas.

I now need to add a few columns to the central database and these columns can't be after the columns already existing, i.e. they have to be on the left of some of the existing columns. Inserting those columns is going to mess up all the vlookup formulas in the dependent files.

Is there a way change the central database file without affecting the vlookup formulas in the dependent files?

View 2 Replies View Related

Excel 2010 :: Change Pivot Table Source With VBA?

Nov 25, 2013

I am using Excel 2010 and have a workbook with two sheets - "Risks & Issues" which contains the source data and "Risks - Summary" which contains a Pivot Table called "Dashboard"

I would like to create a command button called 'Refresh Data' which will be on the "Risks - Summary" sheet. When I add a new line to the "Risks & Issues" sheet, I would click the button in "Risks - Summary" and it will update the Pivot Table range.

So far I've tried using some examples found on this site, although with no experience in VBA macros, I'm not really sure what they do; all I know is that they cause an error.

Code:

Sub Refresh_Click()
Sheets("Risks - Summary").PivotTables("Dashboard").SourceData = Sheets("Risks & Issues").Range("A2").CurrentRegion.Address(True, True, xlR1C1, True)
End Sub

[Code].....

View 1 Replies View Related

Change Source File Of Linked Drawing Object

Apr 15, 2008

I've inserted a Solidworks drawing into a worksheet & wish to change the displayed image using VB. I suspect this could be done by changing it's source file, but can't get the code to work.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved