Excel 2010 :: Put Named Range In Column Of Cells?
Jun 21, 2014using excel 2010
I created named range selection called "Contractors".....how do I put the drop down lists in a column of cells now?
ADVERTISEMENT
Excel 2010 :: VLookup With Named Range Not Updating?
Oct 5, 2011There is a formula
=vlookup(a1,Named_Range,2,false)
if I change a value in the range named "Named_Range" this vlookup does not update.
This formula works but does not allow for any updates. Meaning the vlookup returns the original value even after a cell has been changed. "Named_Range" is on a different sheet but in the same workbook.
Auto calculate is on. I have recalculated the cell manually. I have Office 2010
Excel 2010 :: Date Picker To Default To Named Range
May 6, 2012I am new to VBA. Using Excel 2010. I have a date picker on a userform named LtPayCalFm1. I have created a named range on a sheet within the workbook called LQD which is a date. I want the date picker to show the date LQD when the form opens. The code I have is
Code:
Private Sub LtPayCalFm1_Initialize()
Dim LQD As Range
DTPicker1.Value = LQD
End Sub
I get Run time error '380': Invalid property value How do I correct this?
Excel 2010 :: Load Values From Named Range Into Array?
Jan 15, 2014Code:
For Each clsName In Array("Africa", "Europe", "Asia")
If I have the above array values in the named range _lstRgns in Sheet 1 of my workbook, how would I write the code to pick this up?
The named range is expandable, as it is an offset formula in the name manager.
clsName is the name of my defined array in my code
Excel 2010 :: VBA To Test If Named Range Exists On Active Sheet?
Aug 14, 2012How can I test in my VBA wether a named range (with a scope of sheet) exists on the active sheet?
Code:
If "EmployeeEmail" exists on the active sheet Then,
xxxx
Else
yyyy
End If
Using Excel 2010.
Excel 2010 :: Changing Pivot Table Report Filter Via VBA With Named Range Or Array
Jul 21, 2011I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.
My code thus far:
Code:
Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
[Code]....
emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").
I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.
When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:
For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False
When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.
Here's the corresponding code for that:
For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True
Next
I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).
I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.
(Using Excel 2010, Windows 7.)
VBA To Get Average Of Range Of Cells Based On Named Range In Different Column
Apr 10, 2013I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
Excel 2010 :: Color Fill A Range Of Cells If Specific Cells Not Blank
Feb 7, 2013I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference
Nov 11, 2012I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
Excel 2010 :: Row Height And Column Width For Specific Range?
Jul 6, 2013I have tried and been searching but cannot find the answer. Is it possible to change the row height and column width for only a selected range in my spread sheet, for example, F13:I23? I am using Excel 2010.
View 2 Replies View RelatedExcel 2010 :: Validate All Cells In Column To Xx / Xx / Xxxx
May 14, 2014I will get right to it. I am using the following:
=AND(LEN(Y2)=10,MID(Y2,3,1)="/",MID(Y2,6,1)="/",ISNUMBER(SUBSTITUTE(Y2,"/","")+0))
To create a custom date validation formula. But it doesn't work.
I need to insure the user has to put in the format "xx/xx/xxxx" where x is a 0 to 9. I am using Excel 2010 and when I put in a year greater than 1899, my error message pops up. I'd also like to restrict the month to up to 12 and yes the days up to 31. I used the "date" utility but it allows stuff like 4/25 and then puts in this year for you. It also accepted three digits such as 786 and I don't know what that means.
Excel 2010 :: Macro To Format All Cells In Column According To Value
Mar 27, 2014I need Macro to Format all cells in column according to value. The cutoffs are >20, between 21 and 50 (>21;<50) and greater than 51 (<51).
I have macro to consolidate cells from multiple files to single workbook. Now I have to 'color code' the value in all cells in column 'B', all as part of one large macro.
Excel 2010 :: Clear Contents Of A Range Of Contiguous Cells
Jun 4, 2013I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.
I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.
If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.
Excel 2010 :: Shade Range Of Cells Based On Particular Cell Value?
Feb 17, 2013I'd like to shade a range of cells based on a particular cells value.
Specifically:
If cell E3 is JUNE then I'd like the range of cells from B10 thru I10 to be shaded.
If E3 is JULY then it would shade C10 thru I10....etc.
Excel 2010
Excel 2010 :: Search For A String In Contents Of Range Of Cells
Sep 26, 2013I have a range of cells each containing different values. Each of these different cells has a related date contained in the row underneath.
I want to be able to search the range of cells and find it in the contents of a cell and return the corresponding date from the row below.
Excel 2010
A
B
C
D
E
F
G
[Code]...
Row A4:O4 is the range of cells to be tested. Row A5:O5 are the corresponding dates. Cell B1 is the value I want to search for and B2 is where the date I would like to appear.
In the example shown I'd like 01-Sep to appear in B2.
I'm using Excel2010 and Windows 7.
Excel 2010 :: Copy And Paste Cells Within Date Range
Mar 27, 2014I have on sheet "Charts" in cell "B20" a "date from" and in cell "C20" a "date to" (these will be input manually).
What I need is to check Column "F" on sheet "Report" for any cells within those dates, I then want to copy the entire row(s) that contain these dates and paste them into sheet "Weekly" starting at cell "A2".
Using excel 2010
Excel 2010 :: Auto Lock Selected Range Of Cells After Saving?
Jul 17, 2014I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
Excel 2010 :: Expand Text Number Range Into Separate Cells
Feb 20, 2014Using Excel 2010.
I have data in excel which looks like this:
Column 1 has 1200-1209,1300-1350,1523-1563
Column 2 has 1400-1409,1600-1650,1823-1863
I would like to take the range of e.g. 1200-1209 and have excel put 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 into separate adjacent cells for me. And be able to do this for each column/cell of data I have like this.
Column 1 1200
Column 2 1201
Column 3 1202
Like that only. Is it possible?How?
Excel 2010 :: Auto Lock Selected Range Of Cells After Saving
Jul 17, 2014I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
Excel 2010 :: How To Create Named Tab Sheets
Jan 7, 2014I have a workbook which needs a new sheet for each day of the month.I normally just create and re-name each sheet with the date...is there a simpler way where i can create the new sheet with a date for each month instead of doing indiviually?
Excel 2010
Excel 2010 :: Unmerge Cells And Fill Down Values In Newly Inserted Column
Jul 9, 2014I am using Excel 2010 and have the problem as shown in the attached file.
Input Sheet shows the Data I have at present
Output Sheet is the desired result.
I need a macro which should create an "Output" sheet by doing the following on the Input Sheet
1)Insert a Blank Column before Column A
2)Unmerge the Region Heading and insert the respective Region Name in the newly inserted Column. Region Heading will be in Bold Font.
3)Repeat Step 2 for all Regions
4)Delete the Rows which was merged.
Please note that the number of Data Rows will vary for each Region.
I have shown two Regions for explanation purpose only. There will be several Regions in reality.
The result is shown on the Output sheet
Merge Problem - Forum.xlsx
Excel 2010 :: 3+ Conditional Formatting Rules For Cell Range Based On Formula From Other Cells
Feb 5, 2013how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
Excel 2010 :: Use Range Of Cells With URLs To Files And Check It They Exist - If False Delete To End?
Oct 24, 2013This is Excel 2010 on Windows 7
Trying to figure out a VB Script but don't know where to start.
I have a sheet for each month. lets take September 2013
A Column with 200 Servers (A1:A200) on the Intranet listed as server-1/, Server-2/ Server-3 ... Server-200/
Each Server lists a location of a file for each day (31 columns per row)
I am trying to check for the existence of the files on each server (The Cells contain the URL and File Name in Range B2:AF201) starting with server-1 check the URL in B2 if it exists, go to C2, Continue with that row until the file check is False (The Page will show a 404 error) if not found delete cell url and continue. do this for all 200 rows
Excel 2010 :: Copy Range Of Cells From One Sheet To Another Depending On Option Being Selected From Dropdown Box
Aug 13, 2012Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.
Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names
E.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown
The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.
Excel 2010 :: Macro To Color Code Cells Based On Value In A Cell And Range In A Table
Dec 2, 2013I am using Windows 7 and Excel 2010.
Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?
I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)
Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......
Here is the table:
I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green
LEGEND
Earned Value Limits
Milestone
RED
Yellow
Green
Turquoise
Blue
M2-M3
2.15
M4-M6
1.66
M7-M11
1.26
Excel 2010 :: VBA Code For Inserting Text In All Column B-cells Of Multiple Selected Rows
Jul 11, 2012Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
Excel 2010 :: How To Activate File And Create A Folder In Named CustomUI
Jul 28, 2013I have a Excel 2010 file that I want to activate the file and create a folder in it named customUI.
the steps to achieve this?
Excel 2010 :: Intersection Of Named Ranges (same Worksheet) Doesn't Work?
Aug 19, 2013I'm trying to make a range selection in a pivot table as per the snapshot below:
http:[url],....
The values I am trying to select are the ones that are both red and italic. (Mobile, Other Fix, Fix) from CBU_NA.
I've searched and found a way to select both the rows for CBU_NA category and also the column categ2.
However when I try to make an intersection out of the 2 ranges I receive an error. I'm using Excel 2010 on windows 7.Below the code I am using:
Code:
Sub FCST()
Dim r1, r2, r3 As Range
r1 = pt.PivotFields("categ2").DataRange.Select
Selection.Font.Italic = True[code]...
Excel 2010 :: Formula To Determine If Duplicate Values Exist In Single Column (excluding Empty Cells)
Jun 29, 20121 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
Excel 2010 :: Select Range From Given Cell And To Last Row And Column Of That Cell?
Oct 2, 2012I am using 2010 and want to select a range starting at the same cell all of the time (regardless of whether or not it has contents - so let's say B7. Starting at B7, I want the range to include all of the columns starting at B and go to the end of all of the columns. Then I want the range to include all of the rows starting at B7 and end at the last row. I will eventually copy this range and paste it on another worksheet start on a specific cell of another worksheet. (Perhaps, we can add a name to this range).
View 2 Replies View Related
