I am inserting a subtotal above my data set, the first cell that this subtotal takes place is "R1", then I have an unknown number of subtotals going right, depending on the number of months FCSTed by my sales managers.
Attached you will find a subset of the data and you will see that I need to select "S1 : iLastColumn(1)".
create a formula copying a formula from cell a5 to a20 but i want the result to be 1 cell lower from a5's result
View 6 Replies View RelatedI M trying to get the lower values between to cells and have the lower valued cell highlighted,,,i have over 43 thousand lines of data to go throughand i was wondering if there was a quicker way to do this,,,for example cellA1 is $4.25 and cellA2 is $5.25 i want cell A1 to be highlighted,,is there a way?
View 2 Replies View RelatedI have a worksheet which is created monthly by one of our company's employees which reports results for that months operations and successful audits. These audits are separated into various service lines and on the report which is submitted it takes on the following format:
1|Service Line|Data A|Data B|Etc.
2|___CR_______| Data | Data | Etc.
3|____________| Data | Data | Etc.
4|____________| Data | Data | Etc.
5|____________| Data | Data | Etc.
6|___DD_______| Data | Data | Etc.
7|____________| Data | Data | Etc.
8|___MS_______| Data | Data | Etc.
9|____________| Data | Data | Etc.
What I want to do is use a macro from another sheet that has the effect of: While ServiceLine = "CR" copy the row of that line and move it to my monthly summary workbook, sheet 1. Then when the Service line changes to DD have the macro copy that information to the monthly summary workbook sheet 2, and so on. The problem is, if I test the Service Line column each time it will be an empty string the majority of the time and I'm not sure how to combat that with my If, Do, For, and While statements. As I've thought about it there are two solutions that I can think of: someone knows of a way to test the row only if it has information in it, or if there is a way that I can copy the service line information down until the change in service line so I can test Service Line on each Row.
I have a user form with some option buttons (1,5,10,etc...) that are used so the user can select a given number of cells they want selected. My last option button is labled Other(optOther) and has a textbox next to it(txtOther).
What I am trying to do is give the user the option to either select one of the given numbers or be able to enter their own number. The data will then be extracted from one worksheet to another. I have all the coding for the optiong buttons with the given numbers, just cant figure the textbox one out.
My data starts on B5, so what i am trying to do is when the user enters number n, i would need data from B5:B(n+4).
how can i (in VBA) select 1 row (eacht time, it's an ? iteration? (repeating)), while the first 2 cells are merged, like:
a b c d .....
1
2 merged
3 merged
and i need to select row 3 only
and if someone could explain me how i can use the " find" function (on another sheet)
I have a column that has mostly empty cells. I want to select only the ones that have data so I can perform an operation on all of them. Empty cells must be excluded from selection. I do not know where the bottom of the data is.
The solution needs to be in VBA where it is part of a larger macro affecting the user's worksheet.
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
I've got a column of data that I need to split into further columns, the only way of identifying the split from the data is if a lower case character appears next to an upper case character.
For example, a cell in column A could look like;
Computer ProductsDrivesDVDExternal
And I'd need to split that out to cover columns A B and C with
Computer Products / Drives / DVDExternal
I'd need to do that for all the cells in column A.
I have a huge list of data with numbers in cells. each number also has a corresponding percentage in the next cell over. In some cases, some of the numbers appear twice with differentiating percentages in the cells. What I am trying to do is automatically highlight and delete the duplicate number with the lower of the two percentages. See the below example.
2069091%
2069088%
As per above, I would want to delete the duplicate number with the 88% since it is the lower of the two percentages of that number. I have almost 2000 cells and have been going through manually deleting the lower of the two percentages for the duplicate numbers.
I would like to compare two cells side by side and populate the higher of the two cells in one color and the lower in another
View 3 Replies View RelatedI am using the Find fuction to look for values in cells that contain text in both lower and upper case. I am comparing the values that I have stored in a lookup table. I am running into trouble when the lookup table doesn't match the text cells....
View 9 Replies View RelatedThis is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
I am looking for a formula that I can use in the lower table to fill in the missing County cells, based on the values in the Town/Zip columns on the top table (I apoligize for the crudeness):
CountyTownZipAbleTownA00000AbleTownB00001AbleTownC00002BravoTownD00003BravoTownE00004CountyTownZipMemberTownD00003BlackTownC00002FrankTownA00000GreeneTownB00001JonesTownA00000SmithTownC00006ThomasTownE00004White
This would consist of hundreds of Zips and Towns and this is just an abbreviated mock up.
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies View RelatedI think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:
AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May
If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
View 9 Replies View RelatedI would like to write a macro to add cells in columns C,D,E,F,G & H based on the text (employee) entered in column B. For example I need it to add all the cells for "John S" found within column "C" and then total it at the bottom with a code JSSS (John S straight shop time) or JSOS (John S overtime shop) as per my example. The attatchment is a spreadsheet I use to total billable hours for our employees.
View 3 Replies View RelatedI have a problem which requires a vba routine that is currently outside of my level of expertise...
Here is an example of the data:
TESCO GARAGE 3729
59
REF 177 1092098207 BCC
59
CO-OP GROUP LTD
60
[Code]......
I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.
A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX
I had thought an IF formula would be the way to go.
I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.
Here is what I have:
Option Explicit
Public Sub RatingbyDept()
Dim Dept As Range
With Sheets("Master")
For Each Dept In .Range("I2:I1000")
With Dept
I keep getting error 92 - "For loop not initialized".
I am trying to sum up cells from a column based on conditions in another column (see attached file). I know this could be done using a formula but for various reasons I want to use code. I am not sure if what I am doing is the right approach.
View 1 Replies View RelatedI currently have a spreadsheet that has 4 columns. I want to review the information in column A and if it matches, I want to combine the cells in column D into one cell for the matches in column A. Here is an example of the information I have:
123456
ABC Company
901
0001
123456
ABC Company
901
0099
[Code] ....
So basically I want to review column A and any # in that column matches, I want to combine the cells from column D into one cell. So I would want the first sequence to catch that A1, A2, and A3 match... so then it would combine D1, D2, and D3 into one cell that would read "0001/0099/HRA1". Is this possible?
I need to put a part of a text from a column and place it in a new collumn. At this moment I use 2 columns and an extra sheet to solve this.
I have a collumn with a lot of data like:aaa-bc
bbb
abc-ba-bd
aa-bb
bbb-xx-yy
aa
abc-yy
klmn-zz
klmn-yy-ff
What I need is behind every cell a new cell with only the 1st part like:aaa
bbb
abc
aa
bbb
aa
abc
klmn
klmn
What I do now is use a new column with this formula:
[Code].....
And I made a translation table in which the 1st 4 characters are translated to what I want to see. In a 3d column I use
[Code]....
to get the results I need. Since I need to get results like this very often this method is very time consuming.
Is there a formula available I can use with only 1 new column?
I have data written in Cells B5, C5, D5 etc that changes as you add more column data
I am trying to figure out how to merge cells starting from Cell B3 based on used cells in row B5 to end of column.
I have a spreadsheet that has columns stating both status and then further to the right in the row, currency totals. There are only two status options, Stocked and On Order. I need to add the total currency amounts based on this other column's listing and it must change if status changes.
(Put simpler:
Column A lists Stocked or On Order and Column B is the currency tied up in that row.
I need to make totals for the cells in column B based on the status listed in Column A and it must change when Column A is changed. This should result in two totals, one for Stock Status Currency and one for On Order Curreny)
Is there a data validation that will not allow all upper case or all lower case characters?
For examples:
Allow:
John Doe
101A N Woodbine Street
Disallow:
JOHN DOE
101A N WOODBINE STREET
Disallow:
john doe
101a n woodbine street
Or perhaps formatting that would convert the words to upper/lower case?
Or, I would be interested in VBA if it is the only way.
I need to select the whole column ABOVE the active cell. Ctrl-Shift-UpArrow is no good because it stops at the first blank cell. And selecting the whole column is no good either because when I subsequently paste into the column, it pastes in all the empty cells of the column, meaning my worksheets expands from a few hundred rows to 1 million!
Shift-Home does what I want on rows. Is there an equivalent for columns?
Cells(xrow, 3).Select --
Is there any way I can select more than one column.
I tried Cells(xrow, 3:10).Select but it gives error.