I have attached a file, in which i need to get the value for IHC from a table using formula as the conditions will vary from time to time. The table is only a sample.. actually it had much more.
View 5 Replies
I am using multi-result array formulas to reduce error checking when processing arrays of numbers. However I cannot get this to work with some of the functions as they use Max or other array aware functions and so ruturn the same result for each cell they are enetered into.
Example for A1=2,B1=4
=2*A1:B1
array enetered gives 4,8
However
=Max(2*A1:B1,5)
array enetered gives 8,8 rather than 5,8. I have tried creating my own version of a max function, but am having problems getting it to evaluate array functions.
So, Excel reads my formula. It tells me it's correct, but it has too many statements. [I know this is true, because the equation is absolutely huge. )
The thing is, I need it to tell me, based on two separate columns - one being "State" and the other being "A vs. O", who this contact belongs to. [For each state, there is a different person for A and for O, and the entire spreadsheet is text.]
My ridiculously large, almost functional function is:
=IF(AND(RC[9]="WA",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="WA",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="MT",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="MT",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="ID",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="ID",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="OR",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="OR",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="APP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="OPP"),"Tony
[Code] ....
As you can clearly see, there are a lot of terms, but I need both the "State" and the "A vs. O" column to determine the outcome of the column the formula will be in.
I have a spreadsheet with data in columns A:K.
I want to be able to change font color for a row based on what is found in column J and K. In other words if J3 contains the letter M then row 3 should be Green. I know how to do this using conditional formating, however I have 2 problems.
First, I have 6 different conditions to meet.
Second, there is one more condition to meet. If the date in column K3 is older than todays date then row 3 should be turn Red regardless of what is found in column J3.
jamm
I am trying to copy rows to a new sheet that meet a couple of conditions.
They have to meet condition 1, 2, or 3 and conditions 4, 5 and 6. Meaning have any of those search terms listed in columns L, M, N columns, but no terms in columns I, J, or, K.
Sub filter_data()
Dim rng As Range
Dim header_pos As Integer
[Code]....
I'm trying to populate a dropdown Combo Box by using conditions.
In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.
I've been trying to use worksheetfuntion.Index.
find a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received.
I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.
I have a list of questions that need to be filtered by the user and i was looking for an effective way to do it.
Column A is the question number
Column B is the Question Category
Column C is the actual question.
The user wants to use a dropdown box and select the question category. the results they will get are the question number and the question. there may be several questions within a category so all questions need to be displayed for the category that is selected.
(I havnt explored the use of a list as it gives the user the chance to display all the questions and i am trying to keep the viewing space that this list takes up to an absolute minimum). Also i have mentioned a dropdown box as these are very familiar to the user and so they could use it without the risk of user error. I have attached a list of the questions.
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.
The following is a table which shows a salesmans sales. He or she has sold to 8 customers.
What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.
1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40
2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25
3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
Attached is a section of a spreadsheet containing many columns and about 20000 rows..these rows are made up of events.. named in column a. In column F I want A FORMULA to print the difference between each rating(in col C) and rating of THE ROW identified by a 1 IN the favrank col(col e) in that event. I did the first event manually to show what I mean.
View 2 Replies View RelatedI am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.
So, If PRODUCT_A with OPTION_A, then PRICE = $X.
If PRODUCT_A with OPTION_B, then PRICE = $Y.
If PRODUCT_B with OPTION_C, then PRICE = $Z.
etc.
I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
I am trying to compare two types of conditions, one that has 3 variables and the other that has 8 variables (each variable has a numeric range), which places the correct result in F6 and F7 of the atatched spreadsheet.
View 5 Replies View RelatedI have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:
Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.
i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow
However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
Let's say I have one column of;
1
2
3
4
5
6
7
8
9
What is the most efficient way to change this into '3-columns & multi-rows' like this?:
1 2 3
4 5 6
7 8 9
The actual list is a lot longer and numbers are not in order.
I have multiple rows within a cell separated by Alt+Enter, and would like to combine them as follows:
Desired Result
First
First
Name
Name
First Name
First Name
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies View RelatedI have excel result sheet which contains students information. i.e. name, subjects and their corresponding marks, grade, percentage etc. So from that sheet I want to print result cards for each students separately from the data (result sheet).
View 4 Replies View RelatedI have 4 categories A, B, C & D. These are in desending importance, means A is most important and D is least important. Now there could be many A, B, C & Ds listed in a column. The challange is if coulmn contains A anywhere then the result should be A. If A is absent, then search for B, if present anywhere then display the result as B. It doesn't matter how many times A or any character is listed in column. I am attaching a sheet for better explanation.
View 4 Replies View RelatedI created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
View 2 Replies View RelatedI have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
I want to sum data in COLUMN C that is related to COLUMN B if the COLUMN F is blank.
View 10 Replies View RelatedI have to have to have 2 criterias for it to pass before the items are counted
I have 2 columns of data.
1 being a list of clients names, and 1 being if the Connection/Abandoned
Client Column is colum "F"
Connection/Abandoned is colum "J"
I need to count how many Connected and how many abandoned for each client
So if "F" equals "BPS" and "J" equals "Connected", then count.
I have a spreadsheet which have data auto inported. (thanks to previous help on here). I have now come up against the next problem.
I wish to make the mark up variable depending on 5 options (workings I13;I17)
the choice is selected in column b after the data has been inported
then down on Rows 75 - 79 i have the totals of my choices.
Unfortunatly i do not know how to do a variable sum
I have attached a copy of the workbook.
What is the best method for searching and replacing within a string when the string to be replaced could be say 1 of 10 options and could appear in any position within the string ? To make it easier let's say wherever those sub strings appear they are to be replaced (ie none to be left behind), and lets say they are all to be replaced by another character -- specific to each string being replaced.
Example:
Say I want to replace any digits in a string, to be replaced like for like as follows:
0 -> a
1 -> b
2 -> c
3 -> d
4 -> e
5 -> f
6 -> g
7 -> h
8 -> i
9 -> j
Sample strings:
Luke is 0 Donkey
Luke is 0 Mul4
0nd Luk4 is not very cl4v4r
Luke can not h40r very w4ll.
etc...
I am trying to sum the error points of staff in their work done based on a specific time frame. For example,from 4.00pm to 4.30 pm as per the attached. I tried sum product but did not work. Is there an addition formula that I need to use when incorporating time ?
View 6 Replies View RelatedIn the attached file I managed to sum up the Quantities per Material & Category. The Original DATA is presented in columns A:E. The requested results/Formulas are at Cells B2:B4. My Question is very simple: Is there a way to sum up the Quantities WITHOUT the Help-Column G !? If so - how ?
View 9 Replies View RelatedI have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.
Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.
