Named Ranges Based Upon Indent Levels & Entries In Column
Sep 19, 2008
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
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.
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
I need a formula that will give me a persons name that is stored in Column B based on the highest value within 5 named ranges. Each named range is a selection of every other cell in individual columns i.e. named range junior1 is cells in column E, named range junior2 is cells within column H and so on.
I've attached the worksheet as it as present.Sample Running Scores 3.xlsx
Cell Y3 shows the highest individual score shot (not handicap score) from columns E,H,K,N,Q (or more accurately the named ranges Junior1, Junior2, Junior3, Junior4, Junior5 within these columns). Each of these columns represents a different round in each league, in this case a Junior National League. I now need Y4 to show me the name of the person that shot the highest individual score, in this case Person 21.
I know I need to use a combination of Index & Max in some way but I can't work out what or how.
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.
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.
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.
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.
Is there any way in Excel to create single-cell named ranges from a combination of the labels in the rows and column of a spreadsheet.
Here is an example:
CompanyA CompanyB CompanyC CompanyD
Sales 100 200
[code]....
I would like the first cell (containing the 100) to have a defined name of (something like) "SalesCompanyA", and the second cell to be "SalesCompanyB". And so on - e.g. the cell with 300 in it should be "CostCompanyA".
I can do it manually, but I have a huge spreadsheet & was hoping it could be automated. Just to be clear, it needs to be a single cell range. I know you can create a range from a selection - but this seems to create ranges of the entire row and/or column.
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?
Macro that could look at a row and take the values of two cells, combine them and then create a range name for a third cell in the row.
For example; for row 5420, in column C there is the word Florida, in column D there is the number 6235, and in column F there is a sentence or two. Is there a way to automatically create a named range for the cell of column F that would be named FLORIDA6235? And if so, can the macro do this for every row even if the word and/or the number changes.
I have roughly 28,000 rows and nine columns that I am working with. That's why I was wondering if there was a way to automate this. There are 10 states and I don't know how many different numbers attached to the states, however there are many state and number combinations that repeat, so there would be several rows with Florida in column C, 6235 in column D but a different description in column F.
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.
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.
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
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 have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
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.
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?
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.
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
I have a specific issue that I have not been able find in here or on the internet. I need to count entries in one column based on a date range using corresponding dates in another column.I found countif, countifs, dcount, etc but have not been able to get any code to work. I don't know if my excel spreadsheet attached correctly but I can explain it since it's a simple one. Column A is filled with report dates that generally increase from 10/1/2012 t0 9/30/2013. Column B is filled with the number of "AFIS searches." If an AFIS search was not conducted for a specific report then the cell is blank. I need to count the number of AFIS searches between the dates of 1/1/2013 and 3/31/2013.
Each item (row) in my sheet contains a logged date in column A, and a priority Level in Column G - which is via a data validation drop down (1 to 3).
What I would like to do is create a conditional format based on what the priority level is, and whether it has expired its allowed resolution time based on the logged date.
For example a Level 1 priority requires a 1 day fix, a level 2 requires a 5 day fix and a level 3 requires a 10 day fix.
If the priority level is a 1 and the logged date is over 1 day old i need the whole row to change colour. If the priority level is a 2 and the logged date is over 5 days old I need it to change colour, and if the Priority level is a 3 and the logged date is over 10 days old I need it to change colour. The colour can be the same each time, but I just need to know how to get it to trigger.
I think I have used an IF(AND( formula in the past, but cannot remember how to do it.
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
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
