Named Ranges Based Upon Indent Levels & Entries In Column
I had an idea that I could use the level of indent in the first column to provide the name for the range. The easiest way to explain is with the example spreadsheet I have attached
Cells C5:C10 show how the naming convention should look like, basically Indent Level 0 returns a prefix (Sheet_Name_Prefix) and its corresponding row entry in Column A. Level 1 should return the last Level 0 name and its corresponding row entry in Column A.
I had a go at the code and it works for Level 0 but I can't get it to put to Level 1.
Sub Test()
'Dimension Variables
Dim RowTitleEntries As Integer
Dim NameLoop As Integer
Dim IndentLevel As Integer
'Set the value of RowTitleEntries
RowTitleEntries = 6
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Subtotal Two Levels Based On Color Change Or Indent
I need advice regarding the best way to subtotal data that has been exported from SQL Reporting Services to Excel. Formulas are not exported with the worksheet and need to be re-entered. The data is contained in three levels, but second level is not always present (see attached). The Level 1 data has a "pale blue" background and desciptions start in column A. Level 2 data has a "Gray-25%" background and starts in Column B. Detail level data has no fill color and descriptions for it start in Column C. The detailed data should be subtotaled in the Level 2 line directly above it (if available). The Level 2 data should be subtotaled in the Level 1 row directly above it. If no Level 2 row exists beneath a Level 1 row then all of the detail rows below should be subtotaled in the Level 1 row. I have looked at options for keying on the color or the column contents for triggering when to calculate the subtotals, but nothing that I have tried works. The attached sample file illustrates how the data looks after being exported from Excel. The number of detail rows may change at any time as new projects are added.
View Replies!
View Related
Conditional Formatting Based On 5 Named Ranges
i edited this thread becuase I realized why I was getting the wrong results, however, I am completely clueless on how I need to accomplish the task below. I need to apply formatting based on five named ranges on another sheet. if the values in cell F1:F1000 appears in the named range "datestore" and the corresponding value in range h2:h1000 appears any of the named ranges "date1" through "date5" I would like it to fill the cell yellow.
View Replies!
View Related
Dynamic Named Ranges Based On Cell
I have just started diving into the vast world of dynamic ranges and it's a bit overwelming. I have a dB of sorts that a listbox in a userform fills off of. The dB will be driven by column A and B. Column B are names that are displayed in the listbox. Column A is the class that these names belong to (3 classes). I need to develop dynamically named ranges of Column B based off of Column A. In other words, I need a dynamically named range for all of class1, class2, and class3 in column A to display column B in the listbox on the userform. I have attached an example to clarify the example.
View Replies!
View Related
Dynamically Create Named Ranges Based On Cell
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 Replies!
View Related
Max Of The Values In This Column In Two Different Named Ranges
I have two named ranges, let's call them A and B, each referring to a different single row in a worksheet. I have another row in which I would like to compute the maximum of the values found in A and B in that column. =MAX(A,B) gives the maximum value of all the values found over the entire ranges A and B, rather than just those in the current column. What formula should I use to find the maximum of the values found in range A and range B in the current column?
View Replies!
View Related
Macro To Create Named Ranges Based On A Header Row.
I need a macro that can set up some named ranges using the text in the header row and the sheet name. The header row will always be in row one, but the number of columns will be dynamic. The amount of rows in the range will need to be dynamic also. To clarify, every used cell stating in row 2 in a column will be the named range with the sheetname+text in row one of the column the actual name.
View Replies!
View Related
Move Ranges To Corresponding Ranges Based On Column Having Data
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View Replies!
View Related
Indent Based On Corresponding Cell Value
I am trying to do some automatic formatting of a list of data to create an indented BOM structure in a similar way to DDiddy's post "Indent Data Based On Adjacent Symbol" (Indent Data Based On Adjacent Symbol.) But want to set the IndentLevel for Column B by values in Column A. I.e. a value of 1 in Cell A1 indents Cell B1 by 1. Username shg posted some code that he suggests should do this, but I failed to get it to work. Can anyone explain shg's code or suggest an alternative that would work, preferably automatically upon entering a value in Column A? I'm only just starting to learn Excel macro's, so please forgive me if this post is in any way unclear.
View Replies!
View Related
Indent Data Based On Adjacent Symbol
I am trying to do some automatic formating of a list of data. I can accomplish 90% of what I would like to do via a simple keystroke record macro. What I cannot do is shift the contents of Column B based on the contents of Column A. Column B will have to be shifted to the right 1, 2, 3 . . . cells. Below is an example. Column A symbols as it relates to number of cells to shift Column B Symbol......Amount of shift -[-]..........0 --( ).........1 --[-].........1 ---( )........2 ---[-]........2 ----( ).......3 I have included a sample .xls showing the Original Data and the Desired Output
View Replies!
View Related
Function To Sum A Column's Entries Based On Another Column
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View Replies!
View Related
Autofill Column B Based On Column A Entries
I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A? The 4 items that will populate in Column B will always be the same. I have attached an example to better explain what I am trying to describe.
View Replies!
View Related
Format Column Based On Entries In Another
In the same worksheet, I would like to format column "U" based on the alpha character in column "T" and also format column "AA" based on the alpha character in column "Z". There are three alpha characters that will be used in columns "T" and "Z" ( upper case and lower case), "A" with the format of $ #,000 & "H" with the format of $ 0.00 & "P" with the format of percent %. I want to format column "U" & "AA" prior to entering a value. Column "T" & "Z" are a pull down menus containing only the values listed above (a,A,h,H,p,P) I had asked and received an answer regarding the above pertaining to columns "A" and "B" and was given an answer that works, however, today my boss changed the configuration of the spreadsheet and for the life of me, I could not get the code to work using the new parameters.
View Replies!
View Related
Filtering For Unique Entries Based On One Column
This may be a basic question but I haven't been able to find the solution: I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example 0001 a 0001 b 0002 c 0002 d 0003 e 0003 f I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example: 0001 a 0002 c 0003 f I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
View Replies!
View Related
Collapsible Rows & Column With Different Levels
I have been using a spreadsheet which will allow you to hide columns and rows with a button associated with that row or column. I need to adapt this spreadsheet for another purpose but dont know how to recreate this feature. I have attached a screan capture to assist with my description
View Replies!
View Related
Define Range Based On Named Row And Column
I would like to name a range based on a row number and a column number. I have found the row and column number using this code, but I can't seem to get the rest right. Sub LastRowCol() Dim intLastRow As Integer Dim intLastCol As Integer Dim ws As Worksheet For Each ws In Worksheets intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row intLastCol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
View Replies!
View Related
Formatting Unknown Ranges Based On Contents Of A Column
i can't seem to figure this one out. I attached a file showing how i am trying to format data (the second tab). The big issue is the counting to format. (the formatting itself is obviously easy.) The number of columns is not always standard so i need to count for it. the number of rows in each "room number" is also not always standard, which means i also need to count for those. If anyone has had experience trying to do something like this i would appreciate the help. i know it is possible because i had one that someone had given me years ago, unfortunately i lost it in a hard drive failure...also years ago. if it isn't obvious what i am trying to do, let me know and i will try to be clearer.
View Replies!
View Related
Updating Values In Variable Ranges Based On Repeating Patterns In A Column
I have a list of two columns. Here’s an example. The left most column provides the row number. _ A B 1 1 0 2 2 1 3 2 1 4 3 2 5 4 2.5 6 5 4 7 1 0 8 1 0 9 2 2 Whenever there are two repeating numbers in column one, I want to reduce certain numbers in column 2 by a certain amount. The amount is determined by half the difference between the number in column B corresponding to the second repeated number and the number in column B corresponding to the row after the second repeating number. The range of numbers that are to be reduced begins with the row after the second repeating number and ends with the last row before number one appears in column A. The values in column A are integers, always starting with one. For example, 1, 2, 3, 4, 1, 2, 3, 1, 2, 3, 4, 5. For the above example, after finding the repeating number two’s at A2 and A3, it would reduce B4 through B6 by half the difference between B3 and B4 (1/2). The values for B4 through B6 would be updated in column B. The same for the next repeated numbers, which is one at A7 and A8. Half the difference between B8 and B9 is 1. Here’s what the updated list would look like: 1 1 0 2 2 1 3 2 1 4 3 1.5 5 4 2 6 5 3.5 7 1 0 8 1 0 9 2 1
View Replies!
View Related
Restrict Ranges To X Entries
I am looking for a macro that would accept only one entry per selected ranges. It would be something like if there is one entry in range("b4:e4"), a message box would appear advising the user that only one entry is permitted in that range. It would give the option to delete the entry and to rekey the entry for that canditate. I hope I am clear, if not let me know 1 entry in range("b4:e4"), per canditate 1 entry in range("f4:h4"), per canditate 1 entry in range("i4:l4") per canditate 1 entry in range ("m4:o4") per canditate 1 entry in range ("p4:r4") per canditate 1 entry in range ("s4:v4") per canditate
View Replies!
View Related
Validate Cell Entries To Named Range List
I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line): Private Sub Worksheet_Change(ByVal Target As Range) Dim rngFind As Range 'Set Target = Cells(ActiveCell.Row, ActiveCell.Column) Set rngFind = Range("rngVal"). Find(Target.Value) 'If data in column C changes, do the following If Target.Column = 3 Then 'If the value is found on the validation list, do nothing. If rngFind Is Nothing Then Else MsgBox "You must enter one of the following in this cell:" With Application .EnableEvents = False .Undo .EnableEvents = True End With End If Else End If End Sub
View Replies!
View Related
Using Named Ranges.
How do I go about using named ranges instead of cell names(ie A4:A9) Private Sub UserForm_Activate() Dim lngRow As Long Dim intIndex As Integer UserForm1.ComboBox1.Clear 'Clear combobox lngRow = 2 Do While Sheet1.Range("a" & lngRow).Value <> "" For intIndex = 0 To UserForm1.ComboBox1.ListCount - 1 If UserForm1.ComboBox1.List(intIndex) = Sheet1.Range("a" & lngRow).Value Then Goto NextRow End If Next intIndex UserForm1.ComboBox1.AddItem Sheet1.Range("a" & lngRow).Value NextRow: lngRow = lngRow + 1 Loop End Sub I wanted to change "a" to the defined range "search"?
View Replies!
View Related
Named Ranges From VBA
I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10. I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>. Sample .......
View Replies!
View Related
Delete Named Ranges
I have a number of crazy defined names in my spreadsheet (1,746 to be exact). Not sure where they all came from, but I need them gone gone gone. Some of them start with squares at the front of the name and most of them refer to =#ref! or similar. I've seen a number of vba solutions to this, but none of them seem to work for me - I get "400" errors or run time errors. For reference, I'm hitting Alt F11, pasting the whole sub in, saving, then running.
View Replies!
View Related
Referring To Named Ranges
I'm creating a button so as to input a chart where the user inputs the name of the range that they want the chart to hold. The named ranges are dynamic thus I want the graph to also be dynamic but when added the source is simply the range of the name rather than the name itself. Is there anyway I can enter in a VB variable into quotations marks so that it simply writes that.
View Replies!
View Related
Vlookup With Named Ranges
I am having problems getting the code below to follow my intentions. I have 2 files. I have a temporary file that holds all the data that I need. I have a destination file that needs to have data copied into it. In the temp file, I have data for several dates and product types. What I intend to do is to do a vlookup in VBA to look for the date and the product type in the temp file and copy the appropriate data to the destination file. I have numerous named ranges both in my temp file and my destination file. For the code below, I wanted to make a loop to find the date in the temp file that is listed in the destination file. Once this is done, I wanted to find the product type in the temp file that is listed in the destination file. If both conditions exist, then copy a certain range from the temp file to the destination file. My intention is evident in the code, but I don’t think that I am putting in the correct “code format”.
View Replies!
View Related
Graph Using Named Ranges
Is it possible to create a chart based on value from a drop down? The drop down has two values which are both named ranges. There for I want to be able to choose from the drop down whether to display data per team or per agent.
View Replies!
View Related
Named Ranges In Workbook
I have got a named range, which gives me a list of all the students. I would like to put that data returned in named range in excell workbook. For example: named range called "Students" is getting populated from A1:A10 with values: Student1 Student2 Student3 Student4 Student5 Student6 Student7 Student8 Student9 Student10 I would like to put this data in other worksheet. and instead of putting all the students, it just puts only Student7 Student8 Student9 Student10 I entered in named range name ("students") in all cells I wanted to put data in.
View Replies!
View Related
Referencing With Named Ranges
Ok I have been trying to figure this out for way to long. Say I have a list of values in A1:A4. I have named this range MyRange. If I wanted to refer to this range somewhere else in the workbook and enter =MyRange it will only work provided the "=MyRange" is in the same rows as the original data. For example if I enter MyRange in cells C1 through C4 it would mirror the data in column A. But if I enter MyRange in C5:C8 I would get a #Value error. Does anyone have any ideas to make this more of an absolute reference. Basically anywhere I enter MyRange into a cell I want to see A1. If i drag that formula down I want to see A2, A3, A4, etc....
View Replies!
View Related
Duplicating Named Ranges
I'm creating a KPI spreadsheet which utilizes named ranges to allow for Dynamic charting. I've created the first data input sheet for one of the 10 areas being KPIed. The sheet has 60 named ranges in it. The goal is to duplicate the existing sheet (Area 1A) 10 times and adjust the named ranges and formulas within the named ranges according to the sheet names. Is there a way to accomplish this without having to manually recreate or edit every named range for each new sheet?
View Replies!
View Related
Union Named Ranges
(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:
View Replies!
View Related
Named Ranges In A Chart - .
I have a spreadsheet with 300+ questions in columns (covering 2 worksheets - rows 1 to 5), and the answers to the questions are placed in the rows beneath - column A & B lists who the answers came from. The idea here is that on a seperate sheet a user can enter a question number (e.g: 1.2OP007) and a graph will update to show the different values given for each person who answered that question. So, in this seperate sheet I have the question in cell $A$1. In cell $B$1 I have this formula:.........................
View Replies!
View Related
Mirroring Named Ranges
The worksheet contains 20 different datasets which are divided into separate 30 x 10 grids, all of which help comprise a larger 300 x 20 grid. I have defined each smaller grid/dataset as its own named range. Originally the entire dataset started in A1, but I am shifting it dataset down by 40 lines to create space to work at the top of my workbook. I am wanting to create a data validation list in A1, then have cells A3:J32 populate with the named range I select in A1. However I also want this populated dataset at the top of the sheet to be a mirror -- not a copy. Therefore once I have the data in my range available A3:J32, anything I manipulate would be mirrored in the original range in the lower portion of the workbook.
View Replies!
View Related
Import Named Ranges
I am importing an external Sheet into my Excel workbook but it only imports the data not the named ranges. I need the named ranges to be imported and overwrite any existing range of same name. Example: In existing workbook Sheet I have a named range, ABC which is A1:E10 - in the Sheet I am importing the ABC range is A1:E12. So when I have done import I want named range ABC in my workbook to become A1:E12 (as per external Sheet). This is the code that import data (but not named ranges)
View Replies!
View Related
Copy Named Ranges
I have a workbook with about 25 named ranges that I would like to use in a a different workbook. Is there some way to copy them, or a way to transfer them through code?
View Replies!
View Related
Selecting Two Named Ranges
I have two named ranges, LV1 and LV2 and wish to select them for printing. If the ranges were fixed I would use - Range("A2:H4,A8:H10").Select - how do I do the equivalent using the named ranges in vba?
View Replies!
View Related
VLookup, Named Ranges And IF
=VLOOKUP('ITEM ADD-CHG Form'!Z19,Brand_V,2,FALSE) Another wrench!. If the value in cell Z19 is not found in the named range "Brand_V", the formula returns #N/A. I need to replace #N/A with the verbiage DEF while leaving the other results intact.
View Replies!
View Related
Print Named Ranges ..
I want to create a very simple macro to print some ranges that I have defined in my workbook. The ranges are as follows: Note: Each of these ranges is in a different worksheet named Cons_Summ, Esky_Summ, Indy_Summ, and Gfld_Summ respectively. To be printed in portrait view: - Cons_Summ_IS - Esky_Summ_IS - Indy_Summ_IS - Gfld_Summ_IS To be printed in landscape view: - Cons_Summ_OH - Esky_Summ_OH - Indy_Summ_OH - Gfld_Summ_OH I just want to create a macro so I can run it and it will print all of the ranges listed above with the proper orientation. Nothing special, each range must fit on 1 page x 1 page.
View Replies!
View Related
Sum Of X Named Ranges
the code to add two independent named ranges cell by cell in vba. Both of the named ranges have the same structure and the sum would be posted to a third area of the same structure cell wise.
View Replies!
View Related
Resize Named Ranges
I have to refer to a named range in a macro, but in the beginning I don't know what size it will be. So, in the beginning myRange will always consist of a single cell, which later on will be the 1st cell of the correctly sized range. While the macro runs, the correct size of the range is calculated, and at that point the range has to be resized, say to 'n' rows by 'm' columns, with the original cell as 1st cell.
View Replies!
View Related
Loop Defining Named Ranges
I'm trying to define some ranges with a loop but being a newbie at VBA I'm finding it difficult to solve the error in the code. First, as the data area is uncertain (variation in the number of rows and columns) i look to the end of the entire data area. Then i want to define intervals of 250 rows with a 30 row difference between each of them. Here is my
View Replies!
View Related
|
TRACKER
