I want the first 60 rows of column C to be constant meaning C1, C2, C3, C4..C59 and after 60 rows it should start again with C1, C2, C3.....C59 rather than C60, C61, C62. In other words i+1 but after 60 rows i should be reset to 1 and then again increase by 1. how can i implement these changes
[Code] ......
i have a list of part numbers that all have prefixes and i need to sort them into regular part number order and can't seem to figure out why i'm having such a hard time. the order i need them is this example. i need after part number 1-1 to be 1-2 followed by 1-3 etc. not 1-10 followed by 1-100. i hope i'm explaining this correctly.
View 9 Replies View RelatedI am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?
I am attaching a SAMPLE of data. The actual worksheet is much more larger.
Hlookup.xls
My wife and I run an apartment complex with 2 employees, a manager and a maintenance man.
We use excel to produce a paystub for these employees twice a month.
There are a number of variables including incentives for renting apartments, overtime, variable hours in a pay period etc.
We have a separate spreadsheet for each employee and a separate sheet tab for each pay period named for the pay date... e.g. the first paystub had one sheet named "Jan 15"... the next pay period the paystub had 2 sheets: "Jan 31" and "Jan 15". The third paystub had 3 sheets and so on.
The paystubs have a column with the numbers for the pay period and then a column with the year to date sum (for straight pay, over time, federal withholding, social security, medicare, state withholding, etc.)
Right now I edit the formulas for all these sums for each payperiod. e.g. =sum('Feb 15':'Jan 15'!B35) for the third pay period is editted to =sum('Feb 28':'Jan 15'!B35) for the fourth pay period.
I would like to be able to just copy the prior spreadsheet and not have to find and replace values in 11 spreadsheet cells for each pay period.
I have figured out how to get the name of the current spreadsheet to appear in a cell on that sheet using VBA... but not how to get that cell content to be part of the arguments for =sum(. on the formula bar...
I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?
I am attaching a SAMPLE of data. The actual worksheet is much more larger.
I have a worksheet where over time I add columns that need to be added in a "Total" cell.
=M3+W3+AG3+AQ3+BA3+BK3+BU3+CE3+CO3+CY3
Above example, the cell being added is 10 columns after the previous one.
Question:
What formula can I use to automatically pull the value from every 10th cell starting with M3?
Preferably every 10th cell till a value I determine... i.e every 10th cell but only for the first 15 occurrences.
(Is this anything to do with the Series command?)
I need a formula that will calculate the number of days (ex from 10/1/08-1/1/12) and will differentiate between leap year and a regular year. I am trying to calculate interest between 2 dates and leap year is calculated on a different interest rate. Right now I have it set up so I have to manually type in the leap years in a separate field instead of being able to just you point a to point b.
View 2 Replies View RelatedI've got an excel sheet that has a series of successive numbers down the side from 1-15 per sheet. I've done this by putting the cell formulas as previous row +1. (these are being used as unique reference numbers)
I want to be able to print this out multiple copies of this in one instance without having to manually adjust the first number multiple times then print the sheet. I also want it to be automatically saved as the most recent page printed, so that when it's printed the number continues on from the previously printed sheet.
I found a macro online which I have adjusted to increase a cell by 15 each time it is printed (to change the first number of the row). But I've realised that I'm printing the final page twice if I run the macro again. Is there any way to get round this? If I print 1 sheet there is also no adjustment in numbers.
I also haven't got onto the save part.
This is the macro I am currently using:
"Sub PrintSequence()
'
' PrintSequence Macro
' Macro recorded (altered) 8/16/2005 by Roy Wagner
[Code].....
I have a 2010 Excel spreadsheet where I have a macro set up to print the page. What I need it to do though is to increase a number in a cell and display it each time I print the page.
View 9 Replies View RelatedPre-requisite: I would consider myself to be very poor with excel, based on what I've read on this forum and found on my web-searches. I have a worksheet that has a list of data on the left going vertically, then a summary of this data going horizontally across the top. It is not arranged in such a way that transposing the data will do what I want. I am pulling the 5th word out of the title of each block of the vertical data and need to show this word on the horizontal section.
When I use this formula to pull the 5th word: =MID(MID(MID(SUBSTITUTE(A2," ","^",4),1,256), FIND("^",SUBSTITUTE(A2," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A2," ","^",4),1,256),FIND("^",SUBSTITUTE(A2," ","^",4)),256))-2)
I need to increase A2 to A30, then A58 (up by 28 every time) in every instance in that formula. The fill handle increases the values by 1, instead of 28 (even if I do 3 or 4 instances manually) How do I do this? I've run into this problem in other scenarios, and there HAS to be a way to get around it.
I am new in extracting data from excel .
There is a report that contains a various amount data, in one cell it describes the outcome in a summary format of how an issue was resolved.
Is it possible to search a cell of summary text that contains a set pattern with letters & numbers i.e."CL900962" then either place a "YES" or "NO" in another cell if found?
The pattern will always begin with letters "CL" followed by 6 digit number.
What I want to do: find the four cell that have the same value "99" in belows configurations
A B C D
1 99
2 99
3 99
4 99
A B C D
1 99
2 99
3 99
4 99
i have been working on 2 sheets receiving and selling item and QTY, or in other words i have a Stock IN sheet and a Stock Out sheet
is there some sort of a formula which links 2 cells, columns, together in a way that, if one cell's number increased the other would decrease automatically ?
say iv set 50 in cell a1, i want to set 10 elsewhere and the 50 in cell a1 will automatically decrease from to 40
here's my excel worksheets i uploaded it,
I was just wondering what is the formula to convert a date serial number back to an actual date?
View 1 Replies View RelatedFirst I would like to say that I am not English nor very good to explain myself so hope the title is according to the forums rules. Now to my problem
I would like to to put in for instance the number 100 in cell A and then the number should appear in cell B. I would like to remove the number in cell A without the number in cell B to dissapear. Then add for instance 50 in cell A to get the number in cell B to add up to 150 and so on. How can I do this? I would like to add that cell B is already connected to a different cell. And I am using Vista.
Documents......Data.xls
where "..." is any old string of characters?
I'm trying to do a COUNTIF using regular expressions. I can do it in SQL, but I'm having a hard time figuring it out in Excel.
My SQL query looks like:
[Code] ....
In Excel, I'd like to do the same thing so that I can send it to a client and have the counts already in the spreadsheet. Basically, I want count anything that has 5 numerical digits followed by "_.....".
I want to apply regular expression for password
a) The first letter cannot be number
b) the password should have a combo of numbers letters and one special character
c) The password should begin with a big case letter
Hope the title isn't too broad. Every time I think I have got to grips with RegExp I get stuck on something apparently quite simple. So, I have a few queries so I hope OK to bundle them together.
1. How do I remove the word 'of' only if it occurs after certain other words (the other words should also go). E.g.
"Vale of White Horse" should stay the same
"Borough of Lambeth" should become "Lambeth" (because 'of' after 'Borough')
I tried "Borough(sof*)" but that doesn't remove either word.
2. How do I remove everything after a '/' (inclusive)?
I have tried "/sw+" which removes the first word after '/' but there could be other words
3. How do I remove everything inside brackets (inclusive)?
I have built a custom .xlam file that I"m using as an excel add-in.
I'd like to write some VBA code which takes ranges from any random worksheet (into an array), and get a range from the xlam (into array).
I can't seem to figure out what combination of This.Worksheet and Active.Worksheet I should use.
I'm trying to figure out the whole Regular Expression thing...so I figured Hotpepper's EXDATA UDF would be a good place to try and rewrite with Regular Expression...I got it to work...but not sure why as it seems opposite of what I would have expected...
eg pattern [^/d] i thought would give me everything except numerics...but it appears to be giving me numerics....
here's the code and sheet...can someone help explain what's going on? .......
I have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.
View 5 Replies View RelatedI'm just fiddling around with excel at the moment and have a created a cell with a value of 16. It represents the number of days an event has been running for.
I'm not sure how to make it increase by +1 every day without me having to open excel and change the value in the cell.
I am creating an electronic timesheet. I have created drop downs for times of the day, which then automatically calculates the number of hours worked based on the times selected. The trouble I am having is, I also need to incorporate "Vacation", "Sick Day", etc into the drop down box where the time of day is located.
The sheet works great until someone selects a non-time related answer, such as "Vacation". When this option is selected, the cell that is supposed to contain the number of hours worked says #VALUE! when I, in fact, need it to list a value of 0 for the number of hours worked. Any idea on how to combine my formula =(D6-D5)*24+(D8-D7)*24+(D10-D9)*24 with an IF statement that will automatically assign a value of 0 if a text answer is selected from the drop down?
spreadsheet has daily hours worked per employee and a weekly total. What is the formula to take the weekly total per employee and separate the regular hours (first 40) and have the overtime show up in the next column (without getting negative numbers for the totals under 40).
View 4 Replies View RelatedI want to track a friendly competition of squash games with a friend.
player1
player2
January
[Code].....
I have a simple column chart showing the progress of each month but I'd like a 13th pair of bars showing the percentage, with the percentage scale on the secondary axis. Do I have to choose the percentage cells as a separate series?
Basically the two instances shown are the same, one is a pivot table, the other is copy pasted values off the pivot.
I need to prepare a chart for this data such that the years are show on X axis and the "frequency intervals" and the Count are shown on Y axis ( Im assuming one would have to be one on primary and one on secondary).
What this data is conveying is that e.g. in year 2002 there were 16 customers who bought between 0-799 quantity of a given item, in 2003, 48 customers bought a quantity between 0-799 and so on and so forth.
See attached the image [URL] .......
I need to extract DIGITS from following: C23, C24, C515-C519. It may be done with regular expressions. Does anybody have experience with VBA code for RegEx?
Input: C23, C24, C515, C519.
Output: 23,24, 515, 519
I'm trying to find a formula that increase a cell by 1/3 that will work with auto fill.
For Example if auto-filling a column:
3
3
3
4
4
4
5
5
5
Or if auto-filling a row:
3 3 3 4 4 4 5 5 5
in a1 I have a number 1, a2 is a number 2. I want to keep on counting down to number 7691. Just do not know how to do this.
View 5 Replies View Related