I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
I have a Userform that when opened shows the Excel spreadsheet sitting behind. Is it possible to write code such that when opened the Excel workbook has been sized and re-positioned such that it is effectively hidden under the Userform?
Currently, as you can see, this is summing up the criteria specified from row 3 to row 2000...(which i made the last row 2000 becuase I will never exceed row 2000 with info)
What I am trying to do is in cell T22 and U22 be able to plug in date ranges and make the above formula give me the sums of thos date ranges.
For example in T22 put in 8-1-11 and in U22 put in 10-1-11, and have the sum of that date range populate in cell U24.
Now ther kicker is this: some dates are repeated so I may have six rows of date 8-3-11....and they will have to be part of the sum total.....and ALSO the dates are not in chrological order....I may have six rows of 8-3-11.......then four rows of 8-10-11.....then maybe another three rows of 8-3-11, and so on and so on...this is for all dates. Sorting the dates in order is not an option.
I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.
Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.
Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.
How do you create a reference number to be posted in a userform2 that is opened from an earlier userform1 where you have entered some initialisation data and generated a source reference number. ie set of data has reference X and there will be y items in that set want references in form X01 to Xy (y unlikely to exceed 20). Want to put references 'X01' into the userform2 and cycle through data entry in userform2 from source reference X01 to Xy. (I'm having problems with the transfer from 1 to 2!)
I am looking for a macro that will combine qty's of item codes when first 6 out of 9 numbers in the item number match. Please see example of data below. In this case, the macro looking at the first 6 numbers in used and new. I should get a total of 26 - new and 45 -used.
A B C D E Item Name yard Item Numb Inv New Inv Used Frami panel 3x6 416-100 588801000 6 27 Frami panel 3x6 416-150 588801000 10 Frami Xlife 3x6 416-100 588801500 20 8
In VBA (Excel 2003), I'm wanting to get a reference to an ActiveX comboBox that is inside/attached to a cell.
I can iterate thru all the OLEObjects in the OLEObjects collection of the Worksheet and test the .TopLeftCell property to see if it matches my target cell. However, this seems like a long way around the problem - particularly if there's hundreds of comboboxes in the sheet.
Intuitively, I would have thought there would be a Cell.OLEObjects collection, but it appears that there isn't.
Is there a quicker and easier way to get a reference to the OLEObjects within a target cell?
I have a sheet (sheet 1) of employee information for multiple buildings as follows:
Employee Name, Building, Pay Grade, Full Time Equivalent (of full time hours worked)
Anne, Centre A, Anne, Grade 2, 1.0 Bob, Centre E, Bob, Grade 3, 0.50 Carl, Centre H, Carl, Grade 2, 0.60 Dan, Centre B, Dan, Grade 1, 1.0 Emma, Centre A, Emma, Grade 3, 1.0 Fash, Centre A, Fash, Grade 2, 0.40 Gemma, Centre C, Gemma, Grade 2, 1.0 Hanna, Centre B, Hanna, Grade 1, 1.0 etc, etc, etc
From another sheet (sheet 2), I wish to populate the total of "Full Time Equililents" at each grade in each centre, eg:
CENTRE, GRADE 1, GRADE 2, GRADE 3 Centre A, 0 1.4 1.0 Centre B, 2.0 0 0 etc,etc,etc
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 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.
However, there are times when I need to add a column/criteria. Thus I would have to go into conditional format and redo all the rows in the worksheet. Is there a smarter way to do this?
I was hoping to provide a list of cell address from the header or better than that look for specific numbers in the header row and if there is a number greater than zero, then highlight the entire row.
There is no specific pattern as to which columns would trigger the requirement.
Also how do you keep the ranges from moving when copying across and down. Sometimes I run into that issue. Maybe highlight the entire row and then enter the conditional format?
I wanted to know if it was possible for me to be able to copy a whole row into a different sheet based on a column value. For example, if B6 = CLOSE, the whole row would be coppied to the sheet name CLOSE. If B6 = OCCUPIED it would be copied to sheet name OCCUPIED. I also need if W7 = to a date thats passed today it would be added to the sheet name EXP PRD. An off topic question, i have this code for column "W":
It works just fine, but I also have "N/A" / "INDEF" in the blocks as well so it's not giving me the correct results. I'm using Excel 2003.
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max $987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
I was wondering if it is possible to have cells formatted based on the presence of an autofilter? I have a large worksheet, and to clean it up I would like to make the entire sheet (minus the category headers) invisible. Then, when the user applies a filter, the cells become visible.
I have three workbooks that contain various types of information. I have an Overtime workbook that lists employees and calculates the straight time and overtime worked for the day. This is the main log that information will be pulled from.
I need writing a macro that will copy over information to an Absent Log workbook and a Production Model workbook.
The Absent Log workbook:
This workbook contains worksheets from each month. I need excel to do a lookup of the name in the Overtime workbook and copy the information from the "OT" column but if that column is empty I need it to pull the information from the "Personal DT" column.
The Production Model workbook:
I need this workbook to automatically pull the total straight hours and overtime hours to the production model for the correct day. This will also pull from multiple sheets.
I am going to be writing this in Excel 2010 but it will be primarily used in Excel 2003.
I have a workbook that has 30 tabs in it. Each tab is a report card for students. What I'd like to do is create another tab with a button on it that when I hit the button it will search through each tabs range of D12:D40, D48:D76, D84:D112, D120:D136, J12:J40, J48:J76, J84:J112, and J120:J136. And if any of these cells has an MS in them then this new sheet I have created will list each students name which is in cell E5 and list what they recieved the MS for. This will be in the same row number but in column B. So if cell D12 has an MS in it then this report will list the students name and what's in cell B12.
I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.
Sheet1 sample: Type Selection
The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).
If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.
So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.
Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.
I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!
I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.
So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.
Sample of Sheet1 Country Scotland
Start Date (dd/mm/yyyy) 30/04/2013
So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.
I have an excel file (2003 version) with one sheet called sheet1.
On sheet1 I have multiple text boxes, however each text box has the same text box number "Text Box 1244" (this number appears in the top left-hand side in excel when I click the textbox).
I was wondering if it's possible to rename the text boxes based on their location on the sheet.
Eg. If i had a sheet with 5 rows of text boxes and 3 columns of text boxes (15 text boxes in total).
The top left-hand box gets renamed to "Text Box 1", then the text box below that gets renamed to "Text Box 2" and so on to the bottom of the sheet to "Text Box 5". Then the vba script would move to the text box that was to the right of the first text box (1st row again but 2nd column), and rename all the text boxes in that column ("Text Box 6" onward).
I have the following code, which I used to search Column A for a date and then copy the adjacent cell next to the date and paste it to sheet2.
However what I want to do now is be able to enter just the month or the year and the code will find the cells that contain the same year or month that I entered. I know to find a string value in a cell I can use InStr() however I don't know how to implement this into the code that I have, As depending on the month that I select I want the code to put the value in a certain cell.
Here is the code that I have so far
Sub SearchMacro() DateSearch.Show
Dim LR As Long, i As Long With Sheets("Sheet1") LR = .Range("A" & Rows.Count).End(xlUp).Row
How I can do this by edited the current code or any code for that matter.
Aim: To create a Macro to hide all rows where the date in column D is before today. Column D has about 600 rows.
Code: Sub Hide_Old2()
'Worksheet name With Worksheets("Schedule") 'set start of date range Set rngStart = .Range("D2") 'find end of date range
The problem with this solution is its speed, or lack thereof. It causes the screen to hang and flicker while it cylces through. Is there some way to create a range based on the date and hide the range? or another solution?
Note: Autofilter is not an option, as the spreadsheet with the dates needs to be kept simple for other stakholders and the macro is being run from another sheet.
I would like to be able to limit a drop down list's contents based on a user's selection in a different drop down. I am using Excel 2003, and I know how to do this in Access, but I can't figure it out in Excel. Basically, I have a list of themes in one drop down. The user picks a theme, and goes to a second drop down which is a list of sub-themes attributable to that one theme and no other theme.