I have a list of SSN #'s. I need to increment them by 1 - but in the middle. For example:
001-01-0001
002-01-0001
003-01-0001
Get it? That third number is the one I need to increment by 1 and fill down the column.
Each employee has a different plan time each day of the week.
Mon - 9 Tues - 9 Wed - 7 Thur - 8 Fr - 7
Overtime is really counted after 40
There are 5 sheets for each day of the week mon-fri lets say sheet1..2...3...etc, on each sheet there is a column a with plan and column b with actual hrs. What I'm looking for is a summary sheet for each employee to see where they trend in OT after each day so...
person1 works 10 hrs on mon with a 9 hour plan thus 1 hr trending OT. On the summary page person1 would now show 1 hrs of OT. Now if they would work 9 hrs on tues it would still show 1 hr of OT. On wed however they worked 6 hrs so now the summary page would 0.
I want to add a twist to the sumIf-function, and need some help. My request is similar to lots of other sumIf-issues posted here, which were resolved with the sumproduct-function, but is a little bit different.
I would like to place a formula in cells A2 down that does a running count of unique combinations of columns B and C.
For example, the first record is a white dog, so at that point there is 1 category of dog, next is a brown dog, so there there is now 2 categories etc.
I initially tried to adapt an array formula 1/COUNTIF($B$2:$B8,$B$2:$B8) but this wont quite do the trick.
I can delete duplicate rows easily enough, but I can't seem to capture a couple variables where I would not want to delete the selected row. There are no headings, all data starts in Row 1.
I start in Row 1 Columns C and D, compare that to Row 3 Columns C and D (Every other row). If it is a duplicate entry in both columns, delete that row and the row below it. Pick up at next row and continue. See attached Excel file (Excel 2003). The rows with fonts colored RED are the only rows from this data that should get deleted.
using a macro to print all sheets in a workbook. During this process each sheet has a print area to print as well. A button will be placed on all the sheets. No matter which sheet you are on I would like it to print all sheets. The code I have works I think, but I would like to not have to go by sheet names in case there are some added later, etc. Also, is there a way to do this without going to each sheet and the user seeing all this? Would like to do it in the "background" so to speak.
Sheets("sheet1").Select
'Range("B2").Select
Range("H1:K1").EntireColumn.Hidden = True
Range("O1:S1").EntireColumn.Hidden = True
Range("V1:W1").EntireColumn.Hidden = True
Range("Y1:AC1").EntireColumn.Hidden = True
Range("L1:N1").EntireColumn.Hidden = False
'Print range column A thru AD
ActiveSheet.PageSetup.PrintArea = "A1:AD84"
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.Zoom = 58
' .FitToPagesWide = 1
.BlackAndWhite = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, collate:=True
Range("H1:K1").EntireColumn.Hidden = False
Range("O1:S1").EntireColumn.Hidden = False
Range("V1:W1").EntireColumn.Hidden = False
Range("Y1:AC1").EntireColumn.Hidden = False
Range("L1:N1").EntireColumn.Hidden = True
Range("A1").Select
I have a spreadsheet that has 36 columns and 6000+ rows. Each column has a mix of blanks and content, which is an X. See example of the pattern below, column A. I want to run the COUNTBLANK function through the entire column A so that it assigns a value in column B, next to the content it relates to. Line B4, B9, and B11 would have results, in the example below. I want to be able to apply a formula the entire worksheet, all 36 content columns, and over 6000 rows, so that I can obtain the results quickly by filling down, across, etc. I suspect a loop might be in order. Currently, I have to run the COUNTBLANK between one X and another, repeating that tediously over and over again.
---A----------------------------B
1 X
2 blank
3 blank
[Code]....
I have a spreadsheet with three columns (check #, invoice #, vendor #). If a check pays multiple invoices, there is a separate row for each instance of invoice #. (ie if a check pays 10 invoices, there are 10 rows all with the same value for check # and vendor #, but different invoice #s). I need to concatenate the multiple rows to just one row per check #, with all the invoice #s in separate columns on the same row and delete the duplicate check # rows.
However, I can only use 10 columns, so if there are more than 8 invoices matching the same check #, the 10th column will need to contain invoices 9-n concatenated and separated by a space. I have over 5000 rows in the spreadsheet.
I would like cell CM2 to look at cell G2 and get the value there. Here is where it gets interesting. I would like it to reference sheet "LookupTables" in column A.
Cell G2 will be a 4 digit number.
Cells in column A of sheet "LookupTables" will be formatted like this: 3 digit number-4 digit number-4 digit number, like this "001-0001-0001".
If cell G2 exists in column A of the LookupTables sheet, (but it needs to ignore the first 4 digits and the last 5 digits of that number) then it should return that value in cell CM2. I would like it to look through the entire column A of the LookupTables sheet and return all numbers that match the above criteria, and separate them with a comma.
So, if G2 is 0001 and column A of the LookupTables sheet contains several values including:
001-0001-0006
004-0001-0187
098-0001-0874
098-0002-4356 (it should ignore this since the middle numbers don't match the value of G2)
then CM2 should display: 001-0001-0006,004-0001-0187,089-0001-0874
Also, if there are no matches, then it should just return a blank cell.
I have a workbook with many different worksheets containing data. I am looking to find a certain city name in each worksheet and then replace the region name immediately to the left of the city name.
Example:
Before:
Region City
SCAL ALBUQ
After:
Region City
SCAL_O ALBUQ
how to enter data in non-adjacent cells using a fill command.
Here is what I am trying to do:
in the column, I am holding ctrl button to select every 10th cell down the sheet. I need to enter a date in every selected cell that is exactly 7 days apart, i.e., 11/7/08 then 11/14/08 then 11/21/08, etc.... but no other dates or data.
I have tried to figure out a way to do this other than manually, but am confounded.
Is there anyway to automatically fill the empty added cell after inserting a row without using the fill handle? For example, for a series of numbers: [URL]
2. Drag the fill handle Selected cell with fill handle across the range that you want to fill.
Or running balance: [URL]
2. Extend the running balance formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle.
I am currently working on a project and want to use a picture of a cauldron and have that filled in as opposed to a bar graph filled with cauldron pictures. Is this possible? I picture something similar to how someone would fill a picture on a piece of paper until they hit their goal.
Secondly, If I need to do a bar graph I know I can do conditional formatting on the values within their cells but can I create this formatting specifically to the graphs themselves?
Is there some easy way to fill out column B referencing column A, but only incrementing by 1 row in A every 2 cells in B?
Example:
Column A:
A1 = 1
A2 = 2
A3 = 3
etc...
Column B (I would like to fill this, referencing column A):
B1 = A1
B2 empty
B3 = A2
B4 empty
B5 = A3
etc
I'm trying to loop through a list of about 1300 items and copy the rows where one of the cells has a fill color to 2 different sheets in the same workbook. If there's no fill color, the row moves to a different sheet.
My code is determining that all cells have a fill.
For Each cell In Range("Classification")
If cell.Interior.Color blank Then
cell.Select
Range(Selection, Selection.Offset(0, -9)).Copy Destination:=Range("Compliance")
Range(Selection.Offset(0, -5), Selection.Offset(0, -9)).Copy Destination:=Range("Mstar")
Else
cell.Select
Range(Selection, Selection.Offset(0, -9)).Copy Destination:=Range("RP")
End If
Next cell
I have Column with numerical series.
Like,
Column A Column B
B6 1
B7
B8
B9 2
B10
B11 3
B12
B13
B14
B15 4
B7 and b8 is blank above, so want VBA to automatically put preceding value as "1" in B7 and B8. Same way B10 which is blank, have the preceding value 2 to update there. Like wise do for all below. Update preceding value.
I have having trouble here with this code below, as I have fill down range to AM11:AM55, but when i delete or keep adding new rows, an error appears saying out of range. So how can i get the fill down to fill to the last row i have?
how to get this to work without error appearing?
Range("F11:F500").Select
Selection.ClearContents
Range("H11:AM500").Select
Range("AM500").Activate
Selection.ClearContents
Range("AM3:AM5").Select
Selection.Copy
Range("AM11:AM13").Select
ActiveSheet.Paste
Range("AM11:AM13").Select
Application.CutCopyMode = False
I'm sure the answer is simple, but I can't seem to get it.
B2 = "T1"
I need B3 to be T2, B4 should be T3, etc. I know i can fill this down, but is there a formula or something.
I am trying to type in a number and have it populate in specified areas like on Monday then go to the next Monday an in put of the same value.
View 8 Replies View RelatedCan anyone supply a formula which can be filled down column J of s/s. The formula should look at column H and find those cells where the content of the cell is lower than the one above it. I supply, in the enclosed attachment, three examples and have marked with an X any qualifying cells in adjacent column I. The formula needs to incorporate cells above and below so as to produce a running average whereby no above cell is higher than any below.
View 5 Replies View RelatedI'm looking for an easy way to fill-in 1600 cells, in Col. "A", with pairs of sequential numbers [from 1 to 800] - for example:
1
1
2
2
3
3
4
4
5
5
6
6
I managed to achieve something close with: =INT(ROW()/2.05)+1 but this is not accurate enough.
How can I fill downwards the following formula?
=(SUM(B$2:B13)*0.05)*(J2/12)
=(SUM(B$2:B12)*0.05)*(J3/12)
=(SUM(B$2:B11)*0.05)*(J4/12)
Is there an easy way to fill down this formula?
=SUM(INDIRECT("F$2:F"&ROW()-6))*0.8
=SUM(INDIRECT("F$2:F"&ROW()-8))*0.8
=SUM(INDIRECT("F$2:F"&ROW()-10))*0.8
I would like to fill down references in a column this way: =A1 =A2 =A3 consecutively. Is there a way to do this?
View 7 Replies View RelatedI'm looking to create a macro, one where depending on which the active cell is, if I hit say Ctrl + Shift + D. The macro would fill down the row I selected one row, but there are certain columns I want it to fill down: I want it to Fill Down columns: A to E, G, I, K, M to P, R to T.
For example, my cursor is in cell N45, I want it to fill down row 45, but only columns A to E, G, I, K, M to P, R to T to row 45.
Can this be done. My testworkbook is attached.
I wrote below code, I am expecting column to be updated as 1, 2, 1, 2, 1 ,2 till last row, but it is updating as 123456...
[Code] .....
I am creating a simple userform wherein when I clicked the save button, it will look for the value of a textbox in the excel column then it will save the details on that row. For example, the value of textbox is "12345" and it is located on D5, the value of textbox2, textbox3, textbox4 will be saved in E5,F5,G5.
View 2 Replies View Relatedi have attempted may be totally in the wrong direction. I have a worksheet where column A should hold the same value for many Rows of information, but instead only prints once in the first row of that section. The # of rows in each section vary; sort of like this - where ... is a blank cell:
A 1
... 2
... 3
... 4
B 1
... 2
... 3
I would like to write a macro that loops through Column A and replaces empty cells with the most recent value. When i run the following code it puts the value in the blank cell, but fails to loop to the next.
I am trying to color code certain rows in my data. there are only two columns a, column b. my code below will look at some row number in column 1 and look for a value "total", then again, look on the same row number, column 2 and look for value "1", then color fill it.
View 2 Replies View RelatedI'm creating a financial document, the currency is in £ and then I'll need to convert to $ based on the decided rate. So I'm trying to make 1 cell (A1) as my current exchange rate and then have the formula =sum(B5*A1) so that if the rates change, I can just change that one cell and all will be updated. I'm doing this for hundreds of rows so I need an efficient way to do that.
The problem happens when I try to continue that formula down for B6, B7, etc. It just keeps changing A1 to A2, A3, etc, it's filling down that as well and I don't want it to.