Dynamically Adding Row Blocks Based On Cell Value

Apr 2, 2014

I've managed to get coding that allows me to insert single rows dynamically based on a cell value. Example is if I enter a value in cell C3 the coding inserts the same number of rows below.

I've also created a simple macro that inserts a multiple row block under the active cell, complete with formatting, text and some merged cells. Example is I run the macro and it will insert a block of 3 rows, complete with formatting, borders, text and merged cells.

My challenge is to bring these two elements together. So enter a number in a single cell which will then insert the same number of row blocks, complete with formatting etc. I've added a file to sample what it is I'm trying to achieve. I can add the coding I already have as well.

Dynamic list sample.xlsx‎

View 2 Replies


Adding Tables Dynamically

Jan 2, 2008

I am looking for a macro that will (most likely via button) create new table under an already existing one.

So for simple example, I have this ....

View 10 Replies View Related

Dynamically Alter Macro Based On Tab Name Or Cell Value

Feb 1, 2014

I have a workbook with several tabs. One tab is named for the year eg 2014. Annually, I open the workbook, rename to the new year and rename the year tab to the next year eg 2014 to 2015. All the dynamic range names update as expected. Sheet2(2014) becomes Sheet2(2015). However, a line of macro code does not change and errors out because the year does not change.

Is there a way to dynamically modify the '2014' sheet reference to reflect the renaming of the year tab eg to 2015 or to reference a helper cell named YEAR?

The cell referenced above R332C1 is dynamic.

I have tried writing the above line many ways based on internet research but no success. I suspect I can use a LEN function to actually modify the line of code but I'm not sure its even possible to alter a macro dynamically to meet my needs here.

View 6 Replies View Related

Assign Names Dynamically Based On Cell Content

Jul 10, 2009

I do have a data set from A1:C8 as follows:-
Brand Name(A1)Sales Value(B1)Margin(C1)Levi's100002000Arrow150002500Allen Solly120001300Hanes80001000John Miller250004500Disney280003200Ray Ban180002100

The above brands are categorized as follows:-
Brand NameProposed CategoryLevi'sConsignmentArrowBrandAllen SollyBrandHanesBrandJohn MillerPrivate LabelDisneyBrandRay BanConcessionaire

I would like to put formula from D1:D8 in such a way so that the Proposed categories are extracted automatically through formulae. This will help in getting the contribution calculated from different categories.
Brand Name(A1)Sales Value(B1)Margin(C1)Category(Desired Result)(D1)Levi's100002000ConsignmentArrow150002500BrandAllen Solly120001300BrandHanes80001000BrandJohn Miller250004500Private LabelDisney280003200BrandRay Ban180002100Concessionaire

View 9 Replies View Related

Dynamically Create Named Ranges Based On Cell

Dec 16, 2007

I am looking for a solution to dynamically create named ranges according to the contents of cells in a particular column. The following code works for 1 word names, but in many cases the title cell contains numerous words separated by spaces. Is there a way I can adapt this code so that it will name the ranges with the spaces removed? For example, where cell C2 contains the narrative 'Sales Ledger Control', I would want the range name 'SalesLedgerControl'.

Range("R2:Z2").Name = Range("C2")

The named ranges are referred to in numerous other worksheets, where selecting a particular narrative from a listbox creates a dependant drop-down in the adjacent cell (eg if Sales Ledger Control is selected, the dependant list contains names of customers). FYI, the data validation in the other worksheets ignores the spaces in the range names, ie: =INDIRECT(SUBSTITUTE(G2," ",""))

It would not take me long to name the ranges manually, but a macro is preferable because the narratives in the title cells will often change and the range names will obvioulsy also therefore change.

View 4 Replies View Related

Dynamically Adding Menu Items

Jan 22, 2010

I have created a menu called "Temp" and a sub menu called "Temp1". When the user click on Temp1, I need to dynamically add some menu items under Temp1. Is this possible in Excel?

I need to do this on the right click menu. for example, along with "Cut", paste" etc menu's I need to add a Menu Item, Temp and under this menu, I need to add a SubMenu, Temp1. When the user click on the Temp1, I need to populate the child items.

View 9 Replies View Related

Dynamically Fill Range Of Columns Based On Value Of Cell In Another Sheet

Feb 24, 2014

I wanted to enter value in sheet1 on A1. Columns in sheet2 from B1 until the value entered in sheet1.A1 should be automatically filled.


Sheet1.A1=5, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3, Sheet2.B4=4, Sheet2.B5=5.

if Sheet1.A1=3, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3.

if Sheet1.A1=7, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3, Sheet2.B4=4, Sheet2.B5=5, Sheet2.B5=6, Sheet2.B5=7


View 2 Replies View Related

Adding Events For Dynamically Created Controls

Oct 10, 2006

I have somecode that scan column "A", and add dynamically "TextBox" Control according to some condition.

How to add event on dynamically created controls?

Private Sub btnSubmit_Click()
Range(Selection, Selection.End(xlDown)).Select
For Each rng In Selection

View 9 Replies View Related

Dynamically Adding And Removing Controls On A Userform

Apr 5, 2007

Is it possible to dynamically add or remove controls on an Excel userform using VBA? Doing this before the userform is displayed would be great.

View 9 Replies View Related

Dynamically Adding And Removing Checkbox - Linked Cells

Sep 15, 2014

I have problems with dynamically adding and removing checkboxes. I have a form and there is a button to add more rows to the table. Every row includes a checkbox too.

So first i add a row, and then add a checkbox to a specified cell in that row.There is a button for removing rows as well (witch should removes checkboxes as well).

My problem is when i ad the checkbox the linked cell property only works for the first one.When i add the second row the linked cell of the firstly added checkbox changes to the one in the new row and the new checkbox has no linked cell. I am adding the stuffs as follows:

Private Sub addBtn_Click()
Dim y As Integer
y = findFunc("end") // Y define where To insert the New row
Cells(y, 11).EntireRow.Insert
Cells(8, 11).Copy

[Code] .....

Any way to add checkboxes dynamically.

View 2 Replies View Related

Sum A Cell Dynamically Based On Another Cell

Aug 2, 2006

I have a spreadsheet which I use a number in cell E4 which can be an integer for instance let's say E4=9.

I want to go down a column and get the sum dynamically for let's say when the cell in E4=3.

The range in column G starts at G27. I then want to return the dynamic value to cell E3.

What is the best way to accomplish this using vba code?

View 9 Replies View Related

Locking Specific Cell Blocks

May 7, 2014

When cross linking cells from one file to another, I sometimes lose the source link to rearranging cells on one of the other spreadsheets. Is there a way I can "lock" the source cell so that it isn't lost as a reference?

View 1 Replies View Related

Adding Color And A Value To A Cell Based On Contents In Another Cell

Apr 28, 2014

I have values in a number of cells. Next to them, if there was a value, regardless of what the value is, except blank or zero, i would like the cell to turn blue and if possible have a 1 in the cell.

I've used the conditional formatting, however, among the options, 'less than' is the closest to to getting the color right. The issue is that if the cell has nothing in it, it still colors the corresponding cell blue; because it is not less than the already blank cell. If it is blank, I don't want it to color the cell.

View 1 Replies View Related

Filter Dynamically Based On Selection List Value

Jan 15, 2012

I have to filter a data based upon the selection list value.


Assume that i have 4 values listed in my SELECTION LIST


If i select "US" in selection list ...Filter has to take this dynamically and filter the data..

View 2 Replies View Related

Dynamically Rank Items Based On Two Criteria’s

Jan 3, 2007

I need to have excel dynamically rank items based on two criteria’s (category and sales).

For Example:
I have a list of items with its category in column A, column B has the sales information. I need to rank the items by category and sales, the data is not sorted and I cannot use a macros/VB.

View 9 Replies View Related

Adding A Group Of Cells Based On Yes In Another Cell

Apr 2, 2014

I am looking for a formula in D7

In col E I have a range of numbers
In Col AA I have yes or no

I would like to add all of the col E amounts from line 18-72 where the answer in AA of the same line is no or blank

18 2.00 Yes
19 3.00 Yes
20 5.00 No
21 7.00

D7 = 12.00

View 2 Replies View Related

Adding Cells Based Off A Set Numeric Value From Another Cell

Dec 11, 2009

I have an excel 2008 sheet and want to add cells in an odd way. Here is the best way I can describe it.

I want a formula to add cells based off a number value in another cell.

So it would be something like

IF M3= 1 add C5 to SUM ; If not then go to next step
IF M3= 2 add F5 to SUM ; If not then go to next step
IF M3= 3 add I5 to SUM ; If not then go to next step

IF N3= 1 add C5 to SUM ; If not then go to next step
IF N3= 2 add F5 to SUM ; If not then go to next step
IF N3= 3 add I5 to SUM ; If not then go to next step....................

View 6 Replies View Related

Adding Worksheets Based On Cell Criteria

Aug 4, 2006

I would like one column (column A) to be an "indentifier" column. Column B is the customer name, column C is the customer city and state, column D is the customer phone number, and column E is the customer email.

By calling column A an "identifier" column, what I mean is that I would like to be able to go down the list and put an "x" next to each customer I would like to include in that particular workbook. I would then like to be able to run a macro which would take each of the customers with an "x" in the column A, create a copy of the template comfirmation call sheet, and fill in the identifying information from columns B through E into the copied template. Each worksheet would also need to be renamed the same as the customer name. It would need to perform this same procedure for each row that has an "x," making sure that all worksheets (confirmation call sheets) are grouped into one new workbook.

Sheet 1 is called "main"
Sheet 1 contains 5 columns
A = Blank
B = Customer Name
C = Customer City and State
D = Customer Phone
E = Customer email

Sheet 2 is called "template"

"x"'s are placed into "main" sheet, column A to identify which customer will be affected by macro...................

View 2 Replies View Related

Dynamically Scaling Pages Based On Page Breaks?

Sep 17, 2013

Is there anyway I can make excel scale a set amount of information between page breaks fit 1 page wide 1 page tall? using a macro.



I essentially have this data repeated multiple times depending on how many products I am looking at, I would like this repeated 3 times per page, which is where the page breaks are currently being placed. When i tried to set Width 1 page the automatic page breaks would only allow two of these per page at 100% scaling.

View 1 Replies View Related

Adding Formula To Macro Based On Moving Cell?

Jan 14, 2013

I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.

Sub Share_Sales3()
Dim Prompt As String
Dim Caption As String


View 3 Replies View Related

Adding Data By Row Based On Typing In Cell Contained In Row

Mar 10, 2009

I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more.

Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc.

Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.

View 9 Replies View Related

Adding Multiple Row Contents Based On The Content Of A Single Cell

May 30, 2007

Been trying to work this one out and have ended up banging my head against the wall.

Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability.

The team name is a selection from a list, the availability is 1-6 (different levels of availability)

I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up:

Name Team 1st 2nd 3rd 4th 5th 6th 7th etc.
Mr X ___1st___1______2__1
Mr Y ___2nd_______2________5__5__5
Mr Z ___1st___1____________3__3__3

Total 1st: 2 2 1 3 3 3
Total 2nd: 2 5 5 5

So basically the formula in the last row goes along the lines of:

Add each cell in the column IF the team name = 1st

View 9 Replies View Related

Excel 2007 :: Adding Feedback Message Based On Cell Value?

Jan 9, 2012

Using Excel from Office 2007, and WinXP Pro OS

I have a spreadsheet which contains 2 drop down pick lists in Field1 and Field2.

In Cell A2, I have this set in Data > Data Validation:

Allow: List
Source: =CatList

If the user selects a value from the CatList range, then the drop-down options in Field2 are updated accordingly.

In Cell B2, I have this set in Data > Data Validation:

Allow: List
Source: =INDIRECT(A2)

The Ranges are as follows:

Cat1 | Cat2 | Cat3
Cheese | Hat | Square
Trees | Cat | Circle
Bees | Sat | Triangle
Knees | Mat | Rectangle
Apologies | Bat | Octagon

I'd really like to be able to add a message into FIELD3, which is based on the value in Cat2

e.g. using pseudocode, something like:

If Field2 value = "Apologies" then put this message in Field3: "This requires approval from A"
elseif Field2 value = "Triangle" then put this message in Field3: "This requires approval from XYZ"
elseif Feidl2 value = "Bat" then put this message in Field3: "This does not require approval"

Basically this is for an access form, and I need to be able to add a feedback message to field3 based on what's in Field2.

I thought about using the INDIRECT option to add messages but I can't see how I'd do it. Maybe VB would do it, but I don't know it well enough.

View 3 Replies View Related

Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell

Apr 9, 2009

Basically a cell might have a number with 3 decimal places

For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.

Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.

I included an example of what i mean in this excel file which should clear up any confusion.

View 2 Replies View Related

Excel 2003 :: VBA - Adding Named Template Worksheets Based On Single Cell Value

Apr 24, 2012

I'm trying to make a macro in Excel 2003 to create x amount of named worksheets that are a copy of a different worksheet.

1. I have a main worksheet that will have a number manually entered into a cell (lets say A1);
2. I have a 'template' worksheet;
3. I'll assign the macro to a button on the main worksheet

If I enter '10' into cell A1 of the main worksheet, I'd like to click the button and have Excel create 10 copies of the template worksheet. These new worksheets should all share the same name with a number after them (ex: banana 1, banana 2..... banana 10).

View 2 Replies View Related

Excel 2007 :: Conditional Formatting And Adding Text To Cell Based On Multiple Cells

May 31, 2013

I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.

This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.

I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.

I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.

View 3 Replies View Related

Adding & Naming Sheets "X" No Of Sheets Dynamically

Oct 28, 2009

I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..

View 14 Replies View Related

Want To Reference Another Cell Dynamically

Jul 13, 2009

I basically am trying to just reference another cell (from a pivot table) from another sheet. ='SHEETNAME'!G5

But I Need the G to change... I basically need
='SHEETNAME'! + E29 + 5 to equal

='SHEETNAME'!G5 assuming G is in E29 but am not sure how to do this.

The other option is to use =GETPIVOTDATA("Sum of Amount_19",'SHEETNAME'!$A$3,"Label","Title") but I need "Sum of Amount_19" to change based on another cell, lets say F29.

View 3 Replies View Related

How To Define A Cell To Be A Range Dynamically

Feb 14, 2013

The way I have try to define lastcell is wrong. But I don't know how to do it.

Sub createUniqueRecordID_2()
' fill using CTRL + ENTER
Dim lastcell
Sheets("projects list").Select


View 1 Replies View Related

Formula To Run Dynamically When A User Changes Cell F27

Jul 25, 2006

I am trying to get the following routine to work. I need this formula to run dynamically when a user changes cell F27.

Sub PotentialUnitSales()
'Dim x As Range
Set Range("J27:J200") = "R[1]C[5]-(RC[5]-RC[3])"
'Range("J27").FormulaR1C1 = "R[1]C[5]-(RC[5]-RC[3])"
rng = Range([J27], Cells(Rows.Count, "J").End(xlUp))
End Sub

View 4 Replies View Related

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