I'm trying to amend a chart via combo box. Cell C15 returns a dynamic named range, based on a vlookup.
My problem is in having the code pick up the value that C15 returns, rather than the formula in it.
Set rng = Sheets("Chart lookup").Range("C15")
ActiveChart.SetSourceData Source:=rng, _
PlotBy:=xlColumns
I'm trying to create a workbook for tracking costs and change orders for multiple subcontractors. My main question is about combo boxes. I would like to create a master list of subcontractors on The masterlists tab of the attached workbook. I would like to use this masterlist as the data source for a combo box I'm going to use to replace the subcontractor txtbox on the SubCoEntryFrm. I figured getting away from a user typing something in would benefit me if I wanted to use that subcontractor name field to analyze data.
View 5 Replies View RelatedI have got a table of results with wards along the columns and in the rows reading left to right weekly dates e.g. 5/10/08 12/10/08 19/10/08 etc.
How can I get a graph to change the data it displays from a combo box eg. A1 is selected in the combo box and it shows a line graph of the Data for A1 over the weeks? There are 50 wards
My userform has a combo box that upon entry event is populated via a sub procedure.
The sub procedure defines a column of data on the active spreadsheet as a range and manipulates the range to add unique values only in alphabetical order into a collection.
Which the collection is then used to Add.Items to the combo box.
The userform allows selection of an existing value from the combo box OR type a new value In (no formatting on value).
When combo box is exited and a further command button is pressed the value from the combo box is added to the spreadsheet.
However, I don't need to refresh the source values of the combo box upon EVERY entry as I currently do, ONLY when a NEW value has been typed in and added to the spreadsheet.
How can I (possible check index value ?) ensure when the user types in a new value, exits field and presses the command button is the ONLY time when the combo box source values need to be refreshed.
I have various charts others view and require the data range to change in 12 month intervals, ie Jan07 -Jan08, Feb07-Feb08. I currently move the ranges manually every month but find the it would be more interactive for others if a drop down combo box on the chart were used enabling the user to choose which intervals to view.
View 5 Replies View RelatedIs it possible to vary the size of data markers (eg spots) in a chart by their value?
View 2 Replies View RelatedI 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
I've have a piece of coding.
Sub CopyDown()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]=1,R[-1]C+1,R[-1]C)"
Range("A1").Select
ActiveCell.FormulaR1C1 = "20090000"
Range("A2:A200").Select
Selection.FillDown
Range("B2").Select
End Sub
This works fine, but I would like it to look in Column "M" but still insert in Column "A",
Also instead of Range("A2:A200") I would like it to copy down to the last row of data
as this will vary from sheet to sheet.
I want to make a combo box in excel that, if i select it the sheet inside the combo box will appear.
Example:
Inside of combox are: Sheet1
Sheet2
Sheet3
If i click combo box and i choose sheet3 the sheet3 will appear.
How can i do this? theirs a macro code to use?
If I have a cell with this info: [6126]BOB SMITH
What formula can I use to get BOB SMITH. The length of the name will vary. The number will change, but will always be 4 digits and will have the brackets. [XXXX]
You can always add numbers together. however, how can you vary columns based on a number?
for example, i can always do things like the following to change the number of rows selected in a column based on the variable numRows.
The first combo box is on a userform so that a subject can be selected
View 3 Replies View RelatedI'm trying to identify where a set of values 'agree' within a specified tolerance.
I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.
To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.
I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.
Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.
I am trying to use HLOOKUP to find a location in another sheet and display all the names in that specific location. Also can change location at will and will display the names for that location.
I have attached a spreadsheet that kinda has what I want. It does not have to be HLOOKUP but that is all I know, Hlookup and vlookup.
I'm trying to write code that will open files within a specific folder. However, the end of each filename is going to vary over time to the point that I'll never be able to predict the exact filename.
Is it possible to write something like:
Code:
Set wb = Workbooks.Open "C:\folderfile_filenumberxxxx.xlsx"
Where the "xxxx" signifies the variable element of the file name that could be any four characters? Failing that, is there a similar possible alternative?
i have data that will vary from item to item in the $ of rows associated
and the # of columns to be dealt with.
item1 Line 1
Line 2
Here i want to have a formula put and copied across X columns
item2 Line 1
Line 2 other data going across in the columns
Line 3
Line 4
Here i want to have a formula put and copied across X columns
item3 Line 1
item4 Line 1
get my meaning??
columns involved will be known before i get to this bit of code !!
as an example lets say it's 8 columns
using the record macro i get this bit of code
Range("E14:M14").Select
Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
This is fine but i don't know what row or the columns are before i run the macro so the Range
("e14:M14").select wont really work.
I've sent sometime going through MRexcell searching for tips but found nothing also googled for dynamically pasting across unknown columns
this is close to my idea in my mind conceptually
Range("C2:C" & LastRow)
but it doesn't make the columns dynamic eg column 8 being = H and then P = 8th column on and the last with data.
I created a macro on an Excel spreadsheet:
Sub Macro1()
Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57
End Sub
When the macro is run, it works with the exact amount of data that I've entered.
However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)
How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?
I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series
So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?
I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.
The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?
Code:
Sub IPT()
'
' IPT Macro
'
'
Range("E1").Select
[Code] .....
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
View 3 Replies View RelatedI have created a form that pops up when I open a new template. In that form I have inserted a combo box that I want to be able to select data from a separate spreadsheet that is on our server. What code do I need to write to link this data to my combo box. Below is what I have written.
Private Sub UserForm_Initialize()
'Add list entries to combobox. The value of each
'entry matches the corresponding ListIndex value
'in the combo box.
cbSiteAddress.AddItem ("P:AccountsBilling Schedule.xls'MASTER SCHEDULE'!A5:A103")
'Use drop-down list
cbSiteAddress.Style = fmStyleDropDownList
'Combo box values are ListIndex values
cbSiteAddress.BoundColumn = 0
'Set combo box to first entry
cbSiteAddress.ListIndex = 0
End Sub
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 View RelatedIs 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 RelatedI am trying to update a combo box when a user inputs new data. So I have a form that uses a list in a combo box. What I need is if that combo box receives new data then the combo box will show that new data the next time the form is opened.
View 6 Replies View RelatedI'm attempting to make a simple userform that inputs data onto an existing worksheet. I have the userform but would like to use a combo box to choose a "category" item of data, however I dont know what the categories are! I would like the combo box to, somehow, look at the spreadsheet and read off the already entered categories and offer those as choices.
A picture is worth a thousand words:
http://www.copestake.org/images/excel.png
Is there a simple way to fill the combo box (using the form initialize I assume) with the existing categories?
[Edited to link to image instead of displaying on board~admin]
I have a combo box where I select "Income" or "Expense". In relation to that, In the next column called "Category", I use the offset formula in "Validation". Depending on whether I choose Income or Expense, I will get different dropdown options for "Category". What I want to do is if I choose Expense, in the column named "Amount" beside category, I want to have it so that it places the numbers in brackets or simply a negative sign. Just as reference, I use the SUM function to total up the numbers at the bottom of the sheet.
I have attached a sample copy "Data Validation Test"
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies View RelatedI have a combobox on a form that uses match required. When the user selects this box and does not type in anything then decides to change something else on the form an "Invalid Property value" pops up. Is there a way to get around this. I have tried to add "" to the combobox list but it is still not working properly.
View 4 Replies View RelatedA row of say 20 cells, the first cell has 4 options in a combo (using data validation to make my combo box as it looks cleaner). Once the first cells value has been selected I want to populate the values of cells 2-20 based on which of the 4 options was selected in cell 1. Cells 2-20 will be a drop down of 7 options (so cell 1 selects the default selection in the other combo boxes). Also, for combo boxes 2-20 I want to change the cell background based on which option is selected.
How difficult will this be to program in Excel? Can someone help me out or point me in the right direction to find some good tutorials to help with this. (I do have some experience with Word VBA but I am by no means an expert with it, but I do program in other languages)
