Sum Turnover Where Name Is PART Of The Description
May 14, 2009
I am looking for a way to use the sum.if formula, combined with the find.spec (I am not sure if this is the correct formule, at the moment I only have the Dutch Excel version, it is meant to find a word as part of a cell)
The part I am struggling with is how to find the correct cells to "sum" when the criterium is part of the cell contents. e.g. Paul is the description in am looking for in "Paul is riding his bike" If so, then add the turnover to Paul. I have made a demo to clarify what I mean.
View 4 Replies
ADVERTISEMENT
Mar 18, 2009
I am trying to use VLOOKUP to auto-populate a description from an entered part number. After checking up on how to do this in several different places I applied this formula to the relevant cell but all that it returns is #N/A.
I am very confused as all seems to be correct, but I am new to this and I am sure I am missing something silly. :P
On entering a part number into cell C13 on sheet 'Stores Receipt' it should search and find that number in column A on sheet 'Product List', it should then return the adjacent description from column B on sheet 'Product List' and show this in cell C17 on sheet 'Stores Receipt'.... Sounds simple hey! :D
The formula used is:
View 3 Replies
View Related
Nov 19, 2008
i need to work out employee turnover in excel has anyone got any useful tips?
i have a spread sheet with start and end dates so i need to work out who was in the company etc
View 9 Replies
View Related
Oct 12, 2006
Question
Is there a formula that calculates my desired result which is row 5? I tried creating a formula in row 6 but it fails.
What I am trying to calculate is in week, if I have inventory then how many weeks does it cover?
For Instance, Week 1 I have inventory of $39,120 which covers until 4 weeks and then calculate total of wk1 to 4 and subtract it from inventory value 39,120.
The resultant from this calculation is divided by week 5 and expected production in order to derive the proportion covered in week 5
Please refer to my workbook
View 9 Replies
View Related
Jul 13, 2006
Need formula to calculate employee turnover ratio and if there is a place to get a free template to download?
View 9 Replies
View Related
Jan 5, 2009
I'm trying to find a simple Excel template to add my sales invoices together for a year...just so I know what I've earnt...nothing complicated.
The Columns I need are;
date - invoice no. - customer - labour - materials - total
I'd like it to add together labour and materials to the total box ( or just labour in the total box if no materials for that job.)
Then each invoice adding up as I add more with subtotal of labour and materials then a grand total.
View 11 Replies
View Related
Feb 12, 2009
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
View 3 Replies
View Related
Nov 18, 2008
Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).
=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))
View 3 Replies
View Related
Feb 12, 2014
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
View 5 Replies
View Related
Jan 31, 2009
Example: Table ...
View 9 Replies
View Related
Dec 10, 2008
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
View 3 Replies
View Related
Jun 17, 2014
Attached is a file containing stock codes and description. The stock code is repeated but i would like to have one line description per stock code.
View 4 Replies
View Related
Mar 13, 2009
I have a column with a description that includes color in the description, I want to sort by color with breaking up the description, is there a formula that will look at the description such as "Am Cannoe black tecan" and in the cel next to it come back with "black". Then I would be able to sort by color column, otherwise I would have to text to column and break up the description, etc,,, ect....
So column a1 has the description, I want a formula that will put in column b1 "black" I have 3 colors, so I can do one at a time or add to same formula.....
View 6 Replies
View Related
Dec 9, 2009
I need help to split text (shown below) into 3 columns, now all text is in one cell.
e.g.,
HIPP 1 PLUS PROBIOTIK 600 G MILCHNAHRUNG PULVER 1607000286
I have over 30,000 rows of data which contains different number of spaces within a cell. Please see attached file.
View 14 Replies
View Related
Jul 29, 2009
I have created a pull down menu but cannot figure out how to add descriptions with them. The items in the pull down menu are codes instaed of making people look up their definition in a printed out table,I am hoping to figure out how to make it so that as people scroll through the pull down menu a corresponding definition for the "code" listed appears.
View 9 Replies
View Related
Apr 8, 2008
I'm trying to find
1) the last and
2) first values in a time series of data in which the the data points are scattered all over the place
AND I'd also like to
3) get the corresponding year ( Heading name) for both the
3.1) last and the
3.2) first data point
I've found solutions for all but 3.2. Thus the question remains:
How could I lookup the heading description for the first value on a row?
example:
row #; lookup; year1; year2; year3; year4;
1; year3; ; ; 23; ;
2; year2; ; 21; ; 53;
3; year1; 12; ; 45; 2;
In case it helps anyone looking for same information or providing help to solve my problem 3.2 the other solutions I've found are:
1) lookup last value in row
i) Find Last Number In Row & Return Heading
=LOOKUP(9.99999999999999E+307,R5:AI5)
... also an additional VB solution
ii) Find Last Number In Row & Return Heading
=INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500))
... page also includes solution for 2), below
iii) Find Last Number In Row & Return Heading
... 2 VB solutions
2) Lookup first value in row
Find Last Number In Row & Return Heading
=INDEX(M12:500,MATCH(TRUE,INDEX(M12:M500<>0,0,0),0))
... page also includes solution for 1), above
and
3) Lookup heading of last populated cell in row
Find Last Number In Row & Return Heading
=LOOKUP(2,1/(I9:CN9<>""),I$5:CN$5)
4) Could I e.g. modify the above to lookup the year of first value?
View 4 Replies
View Related
Aug 4, 2007
I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop.
Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...
View 6 Replies
View Related
Feb 24, 2014
Any way to display the description of options (list) which in this case is a numbering code (like ECG201) in a data validation cell.
The length of the cell is not long enough for the users to know the appropriate description of the selected lists. I am now thinking there should be a way to display the description of each item so that users will know that ECG201 means "International College of Excellence", for example, instead of having to go from one worksheet (area) to another to know what an option means.
View 1 Replies
View Related
Aug 10, 2009
I often use pivot tables to summarize accounting data. I wish to summarize the data by account number, but also wish to display the account description next to each account number. Both the account number and account description are separate columns in my original table of data.
I've always managed to do this by the use of lookup formulas after the formation of the pivot table in a column outside the pivot table, but it would be preferable to have those descriptions as part of the table.
If I designate both the account number and account description as row labels, they land on two different lines.
View 7 Replies
View Related
Jul 3, 2014
I'm trying to map the cases present in the sheet 1 to Sheet 2. Here the sheet2 I have highlighted the rows yellow color that needs to be updated by using excel formulas.Here the sheet should be updated with the description below mentioned along with the formulas..Highlighted cells in the sheet2 is B,C,I,J,T,U. I have designed the below condition in the same order
B cells should be updated with the reference of Sheet1 with the below condition:
Identify the "(B Value)"Claim with below condition (D Value)
C cells should be updated with the reference of Sheet1 with the below condition:
Verify whether (I Value) Mapped to the below Coverage in CAS (K value)
C cells should be updated with the reference of Sheet1 with the below condition:
Verify whether the Incident (Q value)is below for the Coverage (K Value)
J cells should be updated with the reference of Sheet1 with the below condition:
Verify the the Exposure type(P) is below for the Coverage (K)
T cells should be updated with the reference of Sheet1 with the below condition:
Verify the cost created the reserve coverage (K value) is below (N value)
U cells should be updated with the reference of Sheet1 with the below condition:
verify the line category of the payment done on the coverage
(X value of all the conditions for Sheet1 value)
View 1 Replies
View Related
Nov 25, 2008
I have created a custom add-in with custom functions and am trying to make it user friendly for other users. By running the following macro I can add functions to different categories, but I was wondering if there was a way to do this automatically so that anyone that adds this add-in will have the functions show up in the proper category without having to manually run this macro.
View 6 Replies
View Related
Apr 14, 2009
I have a macro that inserts a blank column for every sheet in the workbook. The problem i have is that i only want a limited number of columns inserted. I only want a column to be inserted if the sheet tab has the name "detail" in it. I have tried for hours to get this to work, but i keep getting errors. Here is the code i am using.
View 4 Replies
View Related
Dec 28, 2005
I really don't know how easy or difficult it might be, but here it is:
I have something like this (example) :
--------------------------------------
| A | B | C | D |
--------------------------------------
1 | 15| W1 | | |
2 | 5| W2 | | |
3 | 7| W1 | | |
4 | 9| W3 | | |
5 | 21| W4 | | |
6 | 14| W2 | | |
--------------------------------------
I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.)
View 9 Replies
View Related
Sep 2, 2013
I have about 70K rows of data. This is a pull from a CRM system and one of the columns contains Free text which may or may not contain a reference ID.
So basically, I need to be able to identify which (if any) of the 60 or so Reference ID appears somewhere in the text. It may be at the beginning, somewhere in the middle or at the end.
So i created two formulas:
=ISNUMBER(SEARCH("MEBIG3",$P:$P))
I created 60 or so of these columns, all with a different Reference ID (MEBIG3 being the ID in this instance). I put the Reference ID in the Cell 1 of each of the columns. I then got a True in the cells wherever I matched the Ref ID to in the description field.
I then created another column with this:
=IF(AO4=TRUE,$AO$1,IF(AP4=TRUE,$AP$1,IF(AQ4=TRUE,$AQ$1,IF(AR4=TRUE,$AR$1,IF(AS4=TRUE,$AS$1,
IF(AT4=TRUE,$AT$1,IF(AU4=TRUE,$AU$1,IF($AV$2=TRUE,AV3,IF($AW$2=TRUE,$AW$1,IF(AX4=TRUE,$AX$1,
IF(AY4=TRUE,$AY$1,IF(AZ4=TRUE,$AZ$1,IF(BA4=TRUE,$BA$1,IF(BB4=TRUE,$BB$1,IF(BC4=TRUE,$BC$1,
IF(BD4=TRUE,$BD$1,IF(BE4=TRUE,$BE$1,IF(BF4=TRUE,$BF$1,IF(BG4=TRUE,$BG$1,IF(BH4=TRUE,$BH$1,
[Code] ........
So wherever there was a True, it returned the Cell at the top of the column which has the Ref ID in it. I put a pivot table and it worked, but the problem is that Excel just can't really handle the number of calculations. The file keeps crashing and becoming corrupt (There are 70K rows, times 60 or so columns). Also I have actually run out of columns I can add in the nesting, I really need more.
Any way of doing the whole piece of analysis all in one Cell. So basically it says "If ADM1 appears anywhere in Column P return ADM1, If ADM2 appears anywhere in Column P return ADM2 and so on".
View 8 Replies
View Related
Dec 2, 2013
One of the fields I am importing has got a description with commas in. When I import the description is wrongly split. How can I fix that? KS14411011,22490030,"Airconditioner,Wall",5512
Code:
Private Sub cmdImportFile_Click()
Dim ImpRng As Range
Dim Filename As String
[Code].....
View 2 Replies
View Related
Jan 21, 2008
I have a table I down load from SAP with the following headers;
Route, Description, DChl, YF21 (SD),YF31 (SD),YF21 (MM),YF31 (MM),YF10 (MM),YF11 (MM),YF12 (MM).
All I need to do is look up and match the Description and then insert the value (they are percentages) found under the columes YF21 onwards.
I have tried VLOOKUP until I am blue in the face and cannot get it to work.
View 9 Replies
View Related
Aug 24, 2008
I have a list of data in column A. This is grouped by a set of key information and then subsets. The beginning of the key data is signified by the cell colour blue and then the subsets signified by the colour yellow. I want to insert a row just below the colour blue and enter the value " Header" and then when each colour is yellow, insert a row and enter "Subset 1" for the first and then "Subset 2" for the second etc.. The number of subsets can vary.
ABC Customer (Colour Blue)
Info
Info
Product 1 (Yellow)
Info
Product 2 (Yellow)
Info
Info
Product 3 (Yellow)
Info
XYZ Customer (Colour Blue)
Info
Product 1 (Yellow)
Info
Info
Product 2 (Yellow)
Info
Should be:..................
View 5 Replies
View Related
Jan 8, 2013
User inputs cost in a cell then clicks macro button that will run a macro that ask for a task description. The user inputs the task info that relates to the cost (i.e. weld pipe). This data is stored on a seperate sheet in the corresponding cell (cost is on sheet "hours" and task description is on sheet "task description" both in same cell. This allows a copy of the cost sheet to reflect the task description. I have tried recording macros but can't get anywhere.
View 1 Replies
View Related
Feb 1, 2014
I have a small problem here with my worksheet. In my college i need to make a project about sales in a wooden floor shop.
I have done one part of work but actually i'm stuck at the specifications of my sheets.
What I need to do i added the screenshot with design Screenshot 2014-02-02 01.29.03.png
For my project i need to describe everything in my workbook as you can see in screenshot, i have made some notes but i'm note sure if its really right my teacher don't want or can't explain me well this part of work, i don't know why...
Now the question> i would ask some nice people if they can check my sheets and show me where i need to put inputs and outputs notes in the design sheet. Also in the screenshot I surrounded a part with red line there is a very strange thing no one know what is it 3 teacher telling different things.
View 1 Replies
View Related
Apr 16, 2009
Is there a way where i can have the figures in Red align with the corresponding description.
As it is when i run my report the account code will come on top of the description and i want to find a way to align them in the yellow cells.
View 6 Replies
View Related
