I have a dataset in the following manner
A B C D Value
1 0 2 3 D
2 1 3 4 D
6 2 3 4 A
The VALUE field is my problem. I need a formula so that the value field takes the max of the other four columns and returns a string (e.g. 'A' in the column header)
I am having trouble developing a formula which will return a field (specific cells back to me) based on some conditions.
Basically I want a formula which will return the date (G3-T3) to me on each client. On the conditions of there usage.
So I want it to extract the value of the first number to comes across (the value of the number does not matter).
E.g. Gissle (D5) "The formula should go along and provide me with the date (value) "1/7/2013" [Cell I3) in cell [E5).
It just needs to hit the first value it comes across horizontally and provide me with the date (field) which it is located in Column E (E5-E11).
save at work because I had to manually scope out the first value and insert and copy the date (took an hour).
Sheet1 *DEFGHIJKLMNOPQRST2***Usage*************3*
Date*1/5/20131/6/20131/7/20131/8/20131/9/20131/10/20131/11/2013
1/12/20131/13/20131/14/20131/15/20131/16/20131/17/20131/18/20134
Client*************8**5Giselle*********5******6Elaine******4*********7Lidi**4*************
8Eliza****************9Ji***************410Erika****1***********11Maria********5*******12*****************
Can I use conditional formatting to check if a field has a value or a formula?
View 4 Replies View RelatedI have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
VB:
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String
StringReturn = Str1 & Str2 & Str3
End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..
buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..
i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula
"if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..
so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.
I am look for a formula that takes the value from each worksheet and enter this to a summary sheet. I do not what to use formula = then press the enter key when you have select the cell with value in.
View 1 Replies View RelatedI attached an example excel sheet. I tried to figure out but could not get a way. I need a formula which takes the value in D2, search it in column A, if the value is in the list, type the corresponding result in E2.
View 7 Replies View RelatedI have a formula as follows:- =COUNTIF(C$2:C2,C2)
The problem is I have to fire it down 35,000 rows and it takes forever and freezes the PC. Is there a VBA code that will do it quicker or do I have to put up with it?
I have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.
View 5 Replies View RelatedBasically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...
1.5 O.D. - 16GA - 304SS = $X
but
1.5 O.D. - 18 GA - 304SS = $Y
and such.
The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.
O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.
Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.
=V7+(Q7*M7*(IF(N7=2=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$19,IF(N7=2=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$20,IF(N7=2=AND(O7=20)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$21,IF(N7=2=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$16,IF(N7=2=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$17,IF(N7=2=AND(O7=18)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$18,IF(N7=2=AND(O7=16)=AND(L7=304),’Steel Tube Cost
[Code] ......
Mentioning LOOKUP, can I even use it? I've attempted many times however it isn't going too well What can I do?
I have one data tab which constantly changes. (adding more products and corresponding volume/prices) For the lookup tab, I want to look up one value and return multiple values.
I have figured out an index array formula but it just calculates so slowly. My live file is about 8MB now. When I apply my formula, it takes forever to save.
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
=SUM(IF(FREQUENCY(IF(Detail!$A$7:$A$40000=C$7,IF(Detail!$B$7:$B$40000=$B10,IF(Detail!$D$7:$D$40000<> "",MATCH(Detail!$D$7:$D$40000,Detail!$D$7:$D$40000,0)))),ROW(Cust)-MIN(ROW(Detail!$D$7:$D$40000))+1),1))
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
I used the following guide to construct a hyperlink formula which takes me to the coresponding part number entered into a box,
http://www.ozgrid.com/Excel/hyperlink-lookup.htm
It works well in the database I set up with all the part number the problem comes when I tried to use the same hyperlink formula in a diffrent work book referencing the database.
I get a correct returned line reffrence from the macro but it will not open a link to the work book
=HYPERLINK('Database Final.xls'!wbsheet & ADDRESS(MATCH(N16,'[Database Final.xls]DATABASE'!$A$1:$A$9999,0),1),N16 & "s' Info")
For some reason, it seems that this following line returns the value its supposed to be searching for instead of the range where it finds the value.
View 5 Replies View RelatedMy code below returns a type mismatch? It is looking at the values in column B which are formatted as text and the output is in column J. An example of a value is 2.1.15 I want to extract 1 (i.e. the central character between two ".").
VB:
Sub ConvertLineNo()
Dim r As Long, TempStr As String
For r = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -2
TempStr = Cells(r, "B")
TempStr = Split(TempStr, ".")
Cells(r, "J").Value = TempStr
Next
End Sub
I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?
View 4 Replies View RelatedCode:
ActiveSheet.Range("$A$1:$AM$14502").AutoFilter Field:=1, Criteria1:= _ "30/06/2013"
When I run the above on my Worksheet to Filter for Rows not equal to 30/06/13 this works fine
I want amend the Criteria1 to a string that is derived off a value in my worksheet I have done this as below where Range("D8").Value is 30/06/13
It is deleting al my data and not keeping the rows with 30/06/13 in it.
Code:
Dim rng As Range
Dim LastQtrDate As Date
Dim LastQtrDateString As String
LastQtrDate = shtControlTab.Range("D8").Value
LastQtrDateString = "" & LastQtrDate
[Code]...
I've noticed recently that a sumif formula will only ever add up 1 column's worth of data. Is it possible to get it to do multiple columns?
For eg, I have a spreadsheet that looks like:
Using the formula shown, I can easily add up values for 'A' codes for one of the 3 columns, but short of adding together multiple sumif's, there must be a more intelligent way of doing it?
I'm using the following to return multiple reponses to a criteria. However, it returns the answers in rows. i.e. A1, A2, A3. I want it to return in columns. i.e. A1, B1, C1.
=INDEX(RMList!$A$1:$B$32381,SMALL(IF(RMList!$A$1:$B$32381=Errors!$D$2,ROW(RMList!$A$1:$B$32381)),ROW(1:1)),2)
I am trying to work with conditional formatting to turn a field a specific color based on two conditions, one of which is variable. The first condition is a list of ongoing events (which can change) and the second condition is a set of known milestones. I want to create a control (check boxes?) that will turn a given field (which has dates in it from another calculation) to either Red, Yellow, or Green.
I was thinking two Dropdown boxes - one for the events and one for the milestones. The user would choose an event, choose the milestone, then choose a checkbox or something to change the color of a field elsewhere in the spreadsheet. Right now, I'm doing this in a very clumsy way by having three checkboxes for each milestone for each event. There are five milestones per event (so 15 checkboxes per event on Sheet2). I will likely have 70+ events so I was hoping for a better way to do this. I've done this for the first three events and need a more efficient way of doing this.
See the attachment : Event Milestones based on Go Date Uploadable.xlsx
a
b
c
d
1
monday
tuesday
wednesday
thurs
2
ted
bill
frank
mary
[Code] .......
We use excel for scheduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers experienced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day.
For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.
I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.
I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work because I would like the names that are being defined on a separate sheet.
when i use a simple sum it only returns 0 but if I use the same cells and manually add them ie A1+A2+A3 and so on it give me the answer ? As there are over 500 cells it would be a pain to type them.
View 9 Replies View RelatedI have a workbook that is taking the avarage percentage of efficency over a possible of 7 days. If there were only 5 days of production it would average only 5 days. It pulls info from a tab that is call "Datalink" which is just that. As we have started our new fiscal year the person who uses this came to me with this problem
1) GrpIV on C15 is returning # DIV/0! and we need it to return 0. See the attachment.
I have always used unique ways to randomly make selections in my life, and was thrilled to learn that Excel could pick a name(not just a number) from a list. I've used the =INDEX($A:$A,RANDBETWEEN(1, COUNTA($A:$A)),1) function and entered items into Column A (or another if needed, and updated by trial and error accordingly). It worked very well for me for a long time and then stopped working. In multiple documents, with multiple lists (not connecting to each other), all of a sudden every random item started giving the #NAME error.
View 5 Replies View RelatedWhat I am trying to achieve using the example below:
ID Subject 1 Subject 2 Subject 3
1 Italian French German
2 Italian Art Physics
3 German French Italian
4 French Italian German
the result:
Italian French German 3
Italian Art Physics 1
As in the example, the combinations of Italian, French and German where counted, irrelevant of whether the subjects are in 2nd, 3rd or 4th column.
I tried to do this task by creating a pivot table but there are so many permutations and subjects that it would take me a long time to add the combinations.
I have a big table with lots of columns for which I want a pivot to sum the data in the columns. Most likely because there are lot of blanks in the data Excel automatic makes a count of the data when I drag it into the pivot table. Now I have to change for each column one by one the Value field settings from count to sum. Is there a way to change the Value field settings for multiple columns at once from count to sum? If not can I somehow change the settings of the pivot table so default the Value field settings will be sum when I drag it into the pivot table?
View 4 Replies View RelatedI have a spreadsheet with a few thousand rows, yeah i know..lol Each one of the rows has contact details for individuals. I have the address for each person in one field with up to four different sections, seperated by tabs. How do I go about seperating each part of the address so it is in a different column? I have tried text to columns, and it only seperates the first portion of the address, seperated by the delimiter 'tab'.
View 9 Replies View RelatedWhen i tried to drop another field into data field, by default, the new data field will appear below the field which was already in there. However, i would like the new field showing in an new column - horizontally - rather than showing in a new row.
For example:
Rather than the Pivot table appear like this:
Q108A 72
____B84
____C68
____D5
Q109A 64
____ B 73
____C58
____D 5
I would like the privot table present like the following format:
____ A_ B_ C_ D_
Q108 72 84 68 5
Q109 64 73 58 5
Is there setting I can change, so that the new field is added to a new column.
I need a formula that can return the value from a cell in the same row it searches. The row is unsorted and has blank cells. Seacchs row A12:X12 returns the text in C4 (data validation List) in A19 on a different sheet.
View 6 Replies View RelatedThe results of the formula in cell K36 in the attached spreadsheet returns a value of null. It should be $1,200. Am I blind or have I done something wrong.
View 7 Replies View Related