Excel 2003 :: Multiple Columns With Similar Calculation?
Jun 27, 2014
I have Excel 2003 and use the following code for 50 consecutive columns, using each column's Row 1 as the multiplier. Each column has over 20,000 rows. It works, but I'd like to know if there's a quicker and / or neater way to do it.
Code:
Range("bo1").Copy
Range("bo2", Range("bo" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Range("bp1").Copy
Range("bp2", Range("bp" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
View 5 Replies
ADVERTISEMENT
Jul 14, 2014
I am using Excel 2003. I have a spread sheet and I am trying to use conditional formatting. But I can't figure out how to use conditional formatting across multiple columns and so I have been doing each column individually which has been kind of a hassle. I also am trying to figure out how to put in a formula so that every occurrence of a certain text will show up a certain color. I know that I can just do "cell value is", "equal to" and click on the cell, but the information in this spread sheet is organized alphabetically and is going to be added to frequently therefore every time I add information it re-organizes. This then messes up my conditional formatting because the text value that was in the cell gets bumped up or down.
View 2 Replies
View Related
Apr 27, 2014
The link to my file is: [URL] ........
I am using excel 2003.
My query goes like this:
I need to find out "total lifted quantity"(i.e. the sum of 'first state lifted quantity'+'second state lifted quantity'+'third state lifted quantity') for each "state" for each "size" and each "grade".
However when I am applying a pivot table (as seen in Sheet1) it is not giving data in lucid form. I don't want to split the data for three states in three different tables (as seen in Sheet 4).
View 1 Replies
View Related
Apr 4, 2014
I am trying to create a Chart from a Pivot Table. I want the Chart to show my projects and present each months amounts side-by-side comparing (Plan, Actual, Forecast) data each month for the calendar year.
I am trying to figure out how to generate the pivot table showing multiple projects so that the data is grouped by month comparing (Plan, Actual, Forecast) data. Projects are listed down the Rows while months Jan-Dec are along the columns. Each month has 3 columns because the second row contains header for the data sets of Plan, Actual & Forecast. (see sample below) When I try to create a Pivot Table, It shows multiple month fields (Jan, Jan2, Jan3, Feb, Feb2, Feb3) instead of a single month.
{Using Excel 2003}
NameOwnerJan JanJanFebFebFeb
PlanActualsForecastPlanActualsForecast
Project 1Region 1 126.1 119.6 119.6
Project 2Region 2 18.0 0.9 0.9 8.2 8.2
Project 3Region 2 80.0 2.6 2.6
Project 4Region 3
Project 5Region 3 60.0
Project 6Region 4 55.8 55.8 55.8
Project 7Region 4 19.4
View 2 Replies
View Related
Nov 19, 2012
I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.
Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.
If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.
I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.
Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.
Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.
Excel 2003
H
I
O
P
Q
R
S
1
Start Work
Time Out
Day Count
[code].....
View 3 Replies
View Related
Jun 21, 2014
Have an Excel 2010 spreadsheet with 902 email addresses in one column, and 927 email addresses in other column, sorted in alphabetical order.
Overview: Organization database has 902 Members (some have 2 email addresses subscribed to listserv) and Organizational Listserv which has all 927 (Difference is numbers are some members have 2 email addresses subscribed) BUT 902 of emails should be the same
Objective to compare Listserv Members 927 with Database Members 902. I am trying to isolate the email addresses which do NOT exist in Listserv Column, or who do not exist in Database Column for Audit purposes. All Members from the Database column should be subscribed or entered into the Listserv column.
If it's a perfect world the 902 email addresses will all be exactly the same as 902 out of the 927 Listserv email addresses.
Trying to highlight or sort and put identical email addresses on same line or using Conditionally formatting? Highlight Cell Rules/Duplicate cell rules Even when same email addresses are on the same row conditional formatting does not recognize them.
View 4 Replies
View Related
Oct 2, 2013
I need a If statement that can be used on 2 columns.
If there is a 1 in the first column I want to indicate if the 2nd column is 50% or greater with a number 1.
View 3 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
Jul 3, 2012
how to write a macro to copy a set of multiple columns (non-consecutive) from a specified row, onto a new sheet. Ie, it is always the same columns, while the row changes with the imported data.
Specifically, these are the columns I want to select, Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N,P:P").Select
Moreover, I want to specify which row to copy from, in a cell (ie, "11"). So that when I run it, values from B11,D11,F11...P11, will be copied.
View 4 Replies
View Related
Mar 29, 2012
I have a large number of product descriptions of varying lengths (column A) which I need to split into a maximum of 3 columns depending on the total length of the description. Each description in column A is less than 90 characters. Each column (B, C & D) can only be a maximum of 30 characters including spaces and commas etc. Also words cannot be split. Below is a sample of the result I would expect with the description in column A and the 3 extracted columns in B, C and D. I am using Excel 2003.
View 4 Replies
View Related
Dec 19, 2012
I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. Specifically if there is a number greater than zero.
For example the following could work:
[CODE][=OR(LEN(H9),LEN(I9),LEN(K9),LEN(Q9),LEN(R9),LEN(S9),LEN(W9),LEN(X9),LEN(Y9),LEN(AB9),LEN(AD9))/CODE]
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?
View 9 Replies
View Related
May 9, 2012
How do I compare 2 columns and then flag where there is duplication? I want to compare Column B against column C and then flag everywhere there is a match. In this case, Column B may have several thousands of entries (inclusive off all entries found in Column C) and I want to have Excel look through the thousands of entries in Column B and then just place an "x" where there was a match to the values found In Column C. For example:
Excel 2003ABC1Duplicate?MarchApril27899421458347889122254x21458112575445576332147x122258x11257921463Sheet1
View 9 Replies
View Related
Aug 5, 2012
Have Excel 2003. I have
List of email addresses in column a
List of email addresses in column b
** I would like a formula that will take all the values in column A and compare it to the entire list of entries in column B.
Would like it to show in Column C any entries in Column A that ARE NOT in the entire column B.
*Would like it to repeat for entries that are in Column B, but not in Column A and show in D.
*Then would like a count of the differences for each column (that is pretty easy).
It needs to look at the entire list of entries in the column as these will be email addresses. We want to know what is missing from Column A that is not in Column B and what is missing from B that is not in A.
View 3 Replies
View Related
Jan 29, 2014
I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.
formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?
View 2 Replies
View Related
Oct 20, 2012
I have a table in A2:F200 and want to sum the entries in column C for those rows containing both an F in column E and an L in column F. I thought this would be easy but I cannot figure it out.
I use Excel 2003 so SUMIFS don't work.
View 2 Replies
View Related
Feb 6, 2013
I have a worksheet with five columns (A, B, C, D and E)
The cells in Column B contain letters and/or numbers (without spaces) in no particular order.
The cells in Column C contain letters and/or numbers (without spaces) in no particular order.
I want to compare all characters in 1st Cell of Column B with all characters in 1st Cell of Column C, and display the matching characters in 1st Cell of Column D, and the character count of 1st Cell in Column D must be displayed in 1st Cell of Column E. note that multiple instances of the same character must not be treated as duplicates. When execution on 1st Row is finished then repeat procedure for Row 2, etc... Stop execution when first empty cell in Column B is located.
Example:
B1 = LJLM12
C1 = KY2MLK
B2 = ZCG4GM
C2 = X4GGGC
B3 = KTCBNG
C3 = GNBTBB
The script/code/formula must output the following:
D1 = LM2
E1 = 3
D2 = CG4G
E2 = 4
D3 = TBNG
E3 = 4
I am using Excel 2003. Y
View 2 Replies
View Related
Nov 15, 2012
I'd like to grant access to selective columns on a shared worksheet.
For example:
Columns A to D should be edited by only me
Columns E to G can be edited by anyone
Column H should only be edited by someone else
The worksheet has to be shared and has been shared by me. I know how to protect & hide locked cells etc but the problem I'm running into is this...
After locking & password protecting say columns A to D and then sharing the worksheet, I am unable to edit my own columns (columns A to D) without first entering the password.
However in order to enter the password I need to unprotect the sheet. And in order to unprotect the sheet, I need to unshare it!
Is there any way around this or perhaps a completely different way of approaching it?
View 3 Replies
View Related
Nov 13, 2013
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
View 2 Replies
View Related
Jan 26, 2013
How many tickets are created and fixed by a user for a certain period. See below. I've tried countif but it doesn't seem to be working for many criteria.
Start Date
11/1/2012
End Date
11/31/2012
Assigned To
Created
Fixed
[Code] .....
View 8 Replies
View Related
Nov 24, 2013
I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.
Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?
Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?
View 1 Replies
View Related
Jan 1, 2014
How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs
View 3 Replies
View Related
Dec 24, 2012
I am currently using Excel 2003. I have a worksheet with two tabs.
First tab has a list of bank Names. Second list has Bank Names and balances.
I need to find out the the average from a Bank in the first tab, to the same bank on the second tab that reflects the balances.
View 6 Replies
View Related
Mar 20, 2014
So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.
I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.
I don't have Excel 2007, only 2003 I have.
View 1 Replies
View Related
Dec 2, 2011
I have a sheet that column G and column H has duplicate values. I would like to be able to put a formula in a cell P4 that will search thru column G and column H and if there are any matching items then add the corrisponding number in column I.
So say cell G8, G25, and G30 have the same and H8 and H30 have the same value, i would like this to add the values of cell I8 and I30.
Windows XP
Excel 2003
View 9 Replies
View Related
May 10, 2012
I wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:
"Object variable or with block variable not set".
Below is the macro:
Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub
I use Excel 2003
View 5 Replies
View Related
Nov 28, 2012
Link: Create a workbook from every worksheet in your workbook
It works great, but is it possible to modify this to export each worksheet as an html file instead of an Excel workbook?
I'm using Excel 2003.
View 2 Replies
View Related
Apr 20, 2013
I am using the following formula:
=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")
to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).
There are faults in my formula, and if I'm honest I don't entirely understand it !
I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.
To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.
Using Excel 2003
View 9 Replies
View Related
Mar 20, 2014
I would like to add hyperlink to multiple JPEG images (separately) in Excel 2003 and send those images via email to another person. On receiving the mail at the opposite end, the person concerned should be able to view the images by clicking the link. How to do?
View 1 Replies
View Related
Aug 16, 2014
Intend creating a calculator that shows me how much a certain hotel room costs at different times (seasons) of the year, for example I have a One Bedroom Budget Apartment (list of room types is extensive), I also have three seasons, low, S Hols and Xmas, so I have a sheet created and inserted drop down boxes for the RoomType, Season, and Number of days, I now have to insert a formula that looks for the three variables and inserts the cost (I already have this info on a worksheet)
TotalCost
RoomTypes
Season
Days
[Code].....
Also I am using Excel 2003 so no combo box option (that I can find anyway)
View 1 Replies
View Related
Aug 16, 2013
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
I'm using Excel 2003 if that makes a difference
View 2 Replies
View Related
