I have several ComboBoxes & ListBoxes on a Userform that populate by referring to Dynamic Named Range in the RowSource property. Also on the Userform is an area to edit/add/delete items in these named ranges. If I Add or Delete an item, the Named Range does not automatically resize--or at least the Menus don't "resize" accordingly--unless I unload the UserForm and load it back up. Is there a way to refresh these Comboboxes without reloading the UserForm?
I have a simple (working) UserForm for project data entry that I exported from one Workbook and imported into another. This UserForm contains a ComboBox that lists the local counties... this ComboBox has a RowSource that references a range on one of the Worksheets... the Worksheet name in the new Workbook is slightly different to that of the Workbook from which it was exported, so I went to edit the RowSource field in the ComboBox properties, but I get a "Could not set RowSource property. Invalid property value" error. Your thoughts on why I'm encountering this error message and why I'm unable to change the RowSource
I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?
My Userform with combobox entries for product checks against the rowsource and if not on the list, is added to the list. the list is a named range in a Table that is used in a data model.
No matter which way I try I am regularly getting a corruption in the table that is home to the named range which is the rowsource... after one or two entries from the userform.
The rowsource is not bound to the worksheet, it is populated at userform_initialize procedure. (There are 4 combobox drawing from same rouwsource hence the loop through to check each)
debug points to the line at which conbobox value is being entered into the rowsource, though not always - it sometimes just crashes the whole file and everything needs restarting, upon which the table is fine and entry is evident.
I have tried resize, addrow to bottom of table variations and all result in the same core problem of a table corruption that crashes the file.
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.
The code below is for a macro that allows the user to create a list of contacts. The column containing the names of these contacts are then referenced by a userform (code not present) by way of a Named Range. I want to update the named range whenever the user adds another contact so this new contact shows up in the user form.
I am currently referencing the range containing the names using R1C1 style, but I cannot get the variable aspect to work correctly.
I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.
The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.
I've got a userform with a combobox and a textbox. The combobox is populated from a dynamic range of product numbers and when a existing number is selected from the list, a respective product name appears in the textbox. These are then saved to a database sheet via command button.
My problem is following:
When a new product number is entered in the combobox that isn't on the list, I want the number & name of the new product to be saved not only on the database sheet but also on the source sheet so that the new product would be included in the combobox list when run again. Here's my code so far:
Private Sub CommandButton1_Click()
'here is the code that saves everything to the database sheet
Normally the combo box will reading the data range from top to bottom. Is there any way to allow the combo box to reading the data from left to right? when i cover, it only reading the first cell of data range.
My goal is to have changes done to one sheet reflect on to another to make things much more easy to do. Below is a picture of what I am dealing with. ( I covered some personal information)
pic of excel.jpg I want to be able to plug in something or delete it and have it reflect to all other sheets. Also I would like to be able to plug in some information and have it export itself based on a specific information. For example, I would want all Newark,NJ contacts to be place in the Newark list automatically.
I found that my listbox in userform cannot update automatically(instead it change to blank) when a new data is insert to the target cell which is rowsourced to the particular listbox. But when i close and reopen the userform, the data which i added just now appear in the listbox.I rowsource the listbox from its properties, the command are: 'sheet1'!A2:E5.
I've compiled data into a spreadsheet which indicates the number of homes sold over the course of 3 years (from 1/1/05 to now) along with their price per square foot, sale price, year built, bed/bath count, and other pertinent factors regarding each property.
I've extrapolated graphs from these in the past which indicated the trends in each category over time (i.e.: Average quarterly price per square foot over time, and so forth).
The data range I'm working with now, however, is from an area of my region so small, that the number of units sold over the past few years is sporadic. Whereas it's normally 100 to 1000 units that comprise my data, now it's only 15 units.
This has created gaps in my line graphs where the price per square foot will dramatically drop down to zero over the periods in time where no sale was recorded. So the line graph ends up looking like a zig-zaggy range of peaks reflecting the price per square foot of a sale, and valleys representing "$0.00" for periods of time where nothing has sold.
To have a Price per square foot of $0.00 is incorrect since in reality nothing was sold.
I would like to be able to eliminate the portions of the graph that have the "$0.00" so that the line graph can "jump" from one sale to the next when the quarterly data indicates 0 units sold.
I'd like to do this without having to eliminate those areas in my spreadsheets. Is there any "catch-all" tool that might do this or is that just crazy talk?
I am trying to collect data from a darts team and these values reflect the number of points won in a team game (ranging from 0 - 3). This will probably be simple for the experts here, so I'll let fly:
I have a table with six names across the top. In the rows below there will always be two cells that have number values ranging from 0 - 3 while the rest will be blank. Those two numbers however, will always be the same. I am trying to write a formula that will determine which columns have values and return a single letter value (first initial) for each name. Since all the names are different that is not an issue...
I am running the formula =VALUE(RIGHT(CELL("filename"),15)) to read the tab name in a workbook with 90 sheets. When a new account is opened, the operator inserts a new sheet from a template (which contains the above formula), and labels the tab to the account number. The formula then 'reads' in the account number and performs look-ups based on it.
The problem is that the sheets are only working when the sheet is active. They all seem to reflect the data in the active sheet or give me a #VALUE error. Where am I going wrong? How do I fix the problem?
I have 30 worksheets in a workbook in which the first sheet is called summary. The summary sheet has 29 formulas with all named ranges, so one named ranged per worksheet. The issue I have is when i clear old data and add new data every month, the named range changes on some worksheets not pointing to the correct range as previous month file even if I have $
Is there any way to use a Named Range in the "Source" field in a Data Validation? I have multiple sheets in a workbook that have the same layout, and I am using alot of different Data Validations. My problem is that whenever I need to change, or add a choice to one of these, I have to go to each sheet and change the Data Validations that are effected by that change. Is there a way to use a "Named Range" or something to put all of my acceptable choices, for that Data Validation, in one spot. And make any changes I had to that field.
I have created a macro (data on the left hand side) that allows me to copy data and paste it into an sql file. The problem i am having is that the data varies (e.g. data on the right) so this doesnt actually work. i was thinking of naming the values within the name ranges and then writing code to see if i can copy particular named range values but i dont know if this will work.
I have a cell for which I need the only options for data to be entered from a list in two different tabs from which I understand cant be done but is it possible to have two drop down options for the same cell?
The attached spreadsheet should explain this slightly better...
I have a range of data that is compiled from various cells that I have named as Range1. I then have another named range - Range2 which should be items selected from range1 and copied into range2. This is to enable other work to take place.
I have created an example userform with Range 1 shown but do not know the code to copy it over using a command button. Range 1 should not change. Also vice versa if possible i.e. remove the selected item from Range2 with another button.
I have a named range TOT_BUDGET. We use the current month number to access this range's index for current budgets.
My quandary is that I need to calculate the YTD budget for one of our dashboards. So, I need to get months 1, 2, and 3 and SUM them. Next month I'll need to get 4 months, etc...
I've looked at SUMIF, but it needs to match against some sort of criteria... I don't have any numeric month labels on the budgets page. I cannot figure out what range/array I would match the month number to.
Now, I know the easy way would be to put a month number at the head of each column, reference that array and do the SUMIF. Blech.
Ideally, I would love to just be able to say =SUM(INDEX(TOT_BUDGET, 1-3)). Is there an Excel function that will let me select an array slice?
In addition, we are getting the Total Sales number out of Great Plains via an SQL query. We summarize many accounts using a pivot table. We then access this table by using the GETPIVOTDATA function.
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
I'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.
Using the following code when updating just one criteria with several charts
VB: Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then [valMeasurePicked] = ActiveCell.Value [code].....
which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.