Sum Fields Across Worksheets
Nov 12, 2009
I have a workbook that is used to calculate sales & commissions for our sales reps. The majority of the worksheets are "RepLast, RepFirst". All of these Rep worksheets include an identical format. I need to add another sheet which grabs the total quantity of products A for each rep and sums them on the new sheet. And repeats the process for products B to Z.
Is there a way to do this other than longhand naming each rep and then the field in the worksheet? We are regularly adding new reps (and losing some periodically), so I would prefer to not have to regularly update the longhand formulas (i.e. ='RepName1'!A25+'RepName2'!A25+'RepName3'!A25...etc) as that would seem prone to introduce calculation errors.
View 3 Replies
ADVERTISEMENT
Mar 2, 2013
I have two worksheets. Each have a common value in two cells. An ID and a currency amount. One worksheet also has a payment date. I need a worksheet that combines the two, along with the payment date.
View 3 Replies
View Related
Nov 15, 2006
I've attached an example of the .xls file.
There are four data worksheet. Each share a common column data set; ITEM NUMBER. Is there a way to create a macro so that when you enter an ITEM NUMBER into a textbox on the "Output sheet" page, it will automatically gather all rows from all worksheets that is associated with that ITEM NUMBER?
Also, on worksheet 3, the ITEM NUMBERS have an extension that defines the type. Can a wildcard be setup so that it will only match the first six characters of the cell?
View 3 Replies
View Related
Feb 8, 2014
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
View 2 Replies
View Related
May 15, 2013
I have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.
View 2 Replies
View Related
Jan 16, 2009
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
View 2 Replies
View Related
Jul 7, 2014
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
View 4 Replies
View Related
Aug 10, 2009
I'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:
View 2 Replies
View Related
Mar 24, 2009
Although I have removed the original numbers in the main area (I12 - AM12), there are still values in the fields I11 - AM11 that just won't go away. These formulas merely sums up the columns below as far as I can see so I can't figure out how come the values 101, 98, 9o etc aren't '0' since there are no values to add anymore..
NB!Not sure if the formulas will be in norwegian or if they change based on language settings but think you will be able to figure them out regardless..
('HVIS'=IF)
View 2 Replies
View Related
Oct 23, 2009
I have a list of company names and I need to find the duplicates. Of course pivot table finds the exact matches, but how do I find duplicates when only 1 character is off. Like punctuation or "s" or a mistype of a single character?
Example:
Jo Blo inc
Jo Blo inc.
Jo Blo inc
Jo Blo icn
Joe Blo inc
Jo Blo inc
View 4 Replies
View Related
Aug 25, 2007
I have a rather unique problem with a new workbook I have created and it only affects 2 sheets out of the the entire thing.
The sheet is driven by a name:
Schmoe, Joe
Diddly, Bill
These names are picked from a drop down list. When a name is picked it autofills other fields using the LOOKUP command:
=IF(A3="","",LOOKUP(A3,Roster!A2:A181,Roster!C2:C181))
By now I think this is pretty straight forward and works wonderfully well for the other 50+ sheets. However I have two employee names where the second of the two (Diddly, Bill) takes and autofills the fields with Schmoe's information. The only thing I can randomly pick up is perhaps the employee numbers are too close:
00868
View 9 Replies
View Related
Sep 28, 2007
i have created a form hich dumps into a database.
There are some mandatory fields which need to be filled in by the user on the form - how can i flag these up at the end of the form - with a message box which says - "cant complete - feild "" "" "" are missing "
View 9 Replies
View Related
Feb 26, 2008
I have a spreadsheet that I receive with a column containing some numbers. It arrives in genearl format but as part of a macro I format Column "C" to 2 decimal place numbers.
This works fine but some of the cells are completely empty. I need to have those empty cells populated with "0" which will of course display as 0.00 because of the formatting.
View 9 Replies
View Related
Feb 29, 2008
I had a spreadsheet with 45 fields, endind with a SUM in field 47.
I've inserted new fields, exactly 531. The SUM doesn't know that and still thinks that I have 45 fields:
SUM(F2;F5;F8;F11;F14;F17;F20;F23;F26;F29;F32;F35;F38;F41;F44)
How do I add automatically the value 3 to F44, continuing to add the value 3 to 531? So I want:
SUM(F2;F5;F8;F11;F14;F17;F20;F23;F26;F29;F32;F35;F38;F41;F44;F47:F49; F52; ETCETERA)
View 9 Replies
View Related
Sep 29, 2008
I am trying to figure out a solution and wondering what would work the best. Here is my situation. As an example, I have one big database with fields such as:
Item# Date Qty Price Cost ect...
1 3/4/08 3 $9.00 $7.00
2 9/5/08 5 $8.00 $6.00 ect....
This continues for up to 1000 lines from a database. I have this is a tab called "Database". From the data in the tab "Database", I want to be able to create 4 seperate reports.
The first report might only have the columns "Item #" and "Date".
The second report might only have the columns "Item #" and "Qty".
The 3rd with only "Item #" and "Price"
The 4th with only "Item #" and "Cost"
If I create a new spreadsheet called "Sales" and create the following:
ColA = Item #
ColB = Date
View 10 Replies
View Related
May 14, 2003
I'm in the process of writing some SQL in excel and normally when I want to specify a field name as another name say wx22 as Part Number i just use square brackets eg
"select wx22 as [Part Number]"
View 9 Replies
View Related
May 18, 2006
I have a field that needs to be split into a set amount of columns depending on a delimited character. This works fine with all of your standard delimeted characters such as / or ,. The only issue is I have a field that has a delimiter of the square symbol . How would I be able to insert this symbol into a input box. When I try and copy the symbol from excel it does not show in the inputbox. I also tried to copy it from the sybol that can be generated in word and that just pastes as?. I was wanting to know if there is anyway this can be entered into a variable so that I can delimit the data using this symbol. If I have to use a ascii value I don't mind I just cant find a ascii code for it.
View 5 Replies
View Related
Jun 21, 2007
I'm creating a spreadsheet that will have two types of data fields: hex and binary, with pairs of corresponding values for each record in the worksheet. I need to be able to edit the hex values and have the binary values update correspondingly and vice versa, on the same worksheet. I can't figure out how to create such a two-way feedback mechanism.
View 5 Replies
View Related
Apr 26, 2006
How do I modify this macro so that the worksheet array will select all the worksheets except sheet 1?? My workbooks will have varying numbers of worksheets ...
View 3 Replies
View Related
Apr 25, 2014
So, I've created a pivot table and need to use one of my values in both the row and value fields. I haven't seen a way to easily do that so far. Is there something I'm missing or is this not possible with Excel (yet)?
View 3 Replies
View Related
May 7, 2014
I have a workbook with two sheets, one used for referance (Column A - Product SKUS, B - Product Description and C - Carton Quantities) - Sheet 1
The other sheet I would like to import incoming shipments. (Column A - Product SKUS, B - Product Description, C - Quantity Ordered and D - No of Cartons) - Sheet 2
What I need to do is find out how to get Excel to find and match up the SKUs in Col A and then dividing Sheet 2's column C by Sheet 1's column C to give the number of incoming cartons of each product in Sheet 2's Column D
View 5 Replies
View Related
May 15, 2014
I have a excel sheet with 3 columns. In column B and C there is either a number, or empty. If both for instance B1 and C1 is empty, i want A1 to automatically be filled in with the number 1. If there is value in one of them, then i dont want anything in column A.
View 3 Replies
View Related
Nov 21, 2007
I have a column of dates (2007, 2006, 2005 etc...) and a column of data ranging from "unsatisfied" to "very satisfied". I want a formula to provide, for example, the total number of occurences of "very satisfied" in "2006".
View 9 Replies
View Related
Jun 9, 2009
I'm sure it is possible to solve this with an array, despite all my efforts having produced nothing so far. Attached is an example workbook. On the 'Data' sheet, every month the data will change and I need to fill in column A, with the approriate Name from the 'Map' sheet. Is this possible using an array?
View 3 Replies
View Related
Jul 7, 2009
I am trying to create an asset management database using excel, and would like to be able to add custom notes to each entry (index) so as time goes on, I can refresh my memory what we have done with a particular item. I have attached the current file I have created.
Essentially, when I use the GREEN drop down menu in the top right, I want it to display the notes/comments that I enter in the rightmost column of the data, as the attached file illustrates.
However, I want to be able to hide that rightmost column and be able to edit the data from the new area (white space) it is being displayed in, however as I have it set up right now, if I edit the cell, it edits the forumla and poof goes all the comments and the formula becomes messed up.
View 5 Replies
View Related
Aug 21, 2014
I have a form (Excel, of course) which needs to be filled following some criteria. I have a filed name "Clause of non-compete" and you have to choose between YES and NO. This field has to be mandatory. I did that with the following code (see below). Now i have another field that says: If YES, should it be maintained? How can i make the second field to be dependant on the first one and mandatory? I want it to be mandatory only when there is Yes in the first field (merged field N35:O35).
[Code]....
View 3 Replies
View Related
Nov 10, 2007
is there a way to protect the userform fields or the userform as whole from other people entering date accept the person who has been given the privilege to enter data?
View 14 Replies
View Related
Apr 11, 2008
I have a workbook with sheet 1 called Support which is basically an input form. The second sheet is called Database.
I want to enter data into the form and via a macro copy the data to the database. I have followed a text book to the letter on how this is to be completed to the letter.
My problem is that when the data is written to the database some of the fields are blank. Using F8 I can step through the VB script which appears to select fields at random.
View 10 Replies
View Related
Feb 11, 2010
I'm creating a spreadsheet with data I've exported from a survey.
My data begins column headings on row 10 and spans across several columns. I've applied filters to the headings.
Above column 10 I have some summary data using the countif and counta functions. Is there away to have my summary fields change as I apply my filters to the main body of data in different ways?
View 11 Replies
View Related
Jul 14, 2008
I'm doing data clean up for a client & have run across data that contains a comma at the end. They've supplied it to me this way & from what I can see it just ends (there is no space after the final comma) IE:
Cheerful, Chipper, Convivial, Delighted, Ecstatic, Elated, Exultant, Pleasant, Pleased, Sparkling, Sunny, Tranquil, Unadorned, Symphony,
The next row may be similar in descriptors but not contain the extraneous comma at the end. IE:
Compassionate, Tender, Calm, Countrified, Priestly, Tranquil, Unadorned, Symphony, Pastoral
Is there a way to remove the final comma in the fields where it's just "dangling" at the end but not remove it from the fields that don't contain the extraneous comma?
Obviously Find & Replace doesn't work because I need the commas to remain throughout the rest of the data. I've searched the boards & have not found a formula that works... I'm going a bit cross eyed at the moment & with thousands of rows to go through, I'd hate to have to remove the ending comma's one at a time!
View 9 Replies
View Related
