Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Determine Cell Value Based On Multiple Criteria In Two Columns

Hi All,

I need some help in determining how to go about getting information populated in a cell. In one column I have various codes for Instrument Types. In the next two columns I have a S&P rating and a Moody's Rating. I'm trying to populate a percentage amount in another column based on what is reported on in the first three columns.

The Criteria I have been given in layman's terms is as such:
If column A is either "ABS", "CMP","CMV","CRF","CRP", or "CRZ",
AND the corresponding cell in Column B has "AAA*","Aaa*","Aa*","A*","BBB*","Baa*" where the asterisk denotes wildcards,
AND the corresponding cell in Column C also has "AAA*","Aaa*","Aa*","A*","BBB*","Baa*",

Then the value in Column R is good and should not be altered,
Otherwise the value in Column R should be .15 .
This is somewhat related to my previous post below, but I was given additional criteria to work with and I can't get my head around it. http://www.ozgrid.com/forum/showthread.php?t=93124

Any help would be very appreciated.
Thanks!

JL

FYI....Here is the code I've been playing around with for the past couple of hours. Looks logical to me, but what do I know. I must be doing something horribly wrong as it's not coming up with my expected results.


	VB:
	
 MyTry() 
    Dim c As Range 
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) 
        If c.Value = "ABS" Or c.Value = "CMP" Or c.Value = "CMV" Or c.Value = "CRF" Or c.Value = "CRP" Or c.Value = "CRZ" _ 
        And Left(c.Offset(0, 1), 1) = "A" Or Left(c.Offset(0, 1), 3) = "BBB" Or Left(c.Offset(0, 1), 3) = "Baa" _ 
        And Left(c.Offset(0, 2), 1) = "A" Or Left(c.Offset(0, 2), 3) = "BBB" Or Left(c.Offset(0, 2), 3) = "Baa" Then 
             'Do Nothing
Else: 
            c.Offset(0, 7) = 0.15 
        End If 
    Next c 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks again for taking the time to look at this.

JL


Post your answer or comment

comments powered by Disqus
Okay here goes;

I have a table that contains training data received from the business. I need to cross reference an employee list with this table and have it return values based on multiple criteria. I've tried combining VLOOKUP and IF, I've also tried using the INDEX, MATCH functions and I've made some unholy combinations of many others.

The one that works the best seems to be a combination of IF & COUNTIFS.

=IF((COUNTIFS('DoC Results'!$C$2:$C$581,A4,'DoC Results'!$U$2:$U$581,"Completed"))>0,"Completed","Incomplete")

True values are returned in all my tests.

First problem;

In the next column (beside the one in my forumla), I'd like to display a specific column value (it's a date) from any records that meet the criteria i.e =1

Second Problem;

Some employees have done the same training twice i.e. >1. I'd like the above formula to return the most recent date of all the records that are counted. I've read about the MAX function but can't use it until I have a formula that returns a value (Problem 1)

I'd love to post a sample of the workbook, but it'll take more time than I'm willing to give to mock-up values. Let me know if you want me to post the actual spreadsheet.

Please help!!!!!!

I have an expenses sheet set up as follows:

A/status B/date C/expense type D/amount

1 allowed 05/07/06 car £20.00
2 notallowed 05/07/06 car £450.00
3 notallowed 05/07/06 car £15.00
4 notallowed 05/07/06 car £26.00
5 allowed 05/07/06 post £20.00
6 allowed 05/07/06 post £20.00
7
8 total car allowed
£--.--
9 total post allowed
£--.--
10
11 total car notallowed
£--.--
12 total post notallowed £--.--

what formula can I use to say total all instances of "car" & "allowed"...
or of "car" & "not allowed"

I have tried =SUMIF, but it will only recognise the first column in the
range..
eg in D8 I wrote:

=SUMIF(A1:C6, "allowed""car", D16)

but it will not recognise multiple criteria ie "allowed" & "car".

how can i total the values based on multiple criteria in different columns?

thanks in advance

nicky

I have an expenses sheet set up as follows:

A/status B/date C/expense type D/amount

1 allowed 05/07/06 car £20.00
2 notallowed 05/07/06 car £450.00
3 notallowed 05/07/06 car £15.00
4 notallowed 05/07/06 car £26.00
5 allowed 05/07/06 post £20.00
6 allowed 05/07/06 post £20.00
7
8 total car allowed
£--.--
9 total post allowed
£--.--
10
11 total car notallowed
£--.--
12 total post notallowed £--.--

what formula can I use to say total all instances of "car" & "allowed"...
or of "car" & "not allowed"

I have tried =SUMIF, but it will only recognise the first column in the
range..
eg in D8 I wrote:

=SUMIF(A1:C6, "allowed""car", D1:D6)

but it will not recognise multiple criteria ie "allowed" & "car".

how can i total the values based on multiple criteria in different columns?

thanks in advance

nicky

Hello Excel Gurus,

I am trying to return a value based on multiple criteria using a nested IF statement. Basically, I have a table that contains costs for a direct mail campaign, and I am trying to return the cost of the program based on the values that can be selected in two separate cells which both have drop-down lists. There are 20 different possible values that can be returned based on the possible combination of selections in those two separate cells.

If you look at the attached spreadsheet, the cell that I am trying to return the costs to is cell B4 (Total Program Costs). The possible values that can be returned are all in column G.

The statement should return values based on two criteria:

1) It should compare the value of B2 with the values in Row F
2) It should also compare the value of B3 and compare with the values in Row A.

It should then return the value based on the way the table is set up.

So, if a user selects "Long Mailer" in cell B3, and 100,000 in B2, then cell B4 should return a value of $43,300.

If a user selects "Jumbo Postcard" in cell B3, and 25,000 in B2, then cell B4 should return a value of $11,500.

I attempted to solve the problem using a really long nested IF statement using the AND command as well. I got an error message in Excel telling me that I had exceeded the max amount of levels in a nested statement. I am assuming there is a more elegant way to do this, but I cannot figure it out.

Help!

Thanks.

How do I set a cell value based on a formula in another cell?

=IF(D4="P.B.A.",G8,0) ,This works to set the value in the cell the formula
is in, say cell A1.

I would like to have this formula reside in cell A1 but set the value in
cell A2 to (G8,0).

Also it would be helpful if I could lock cell A2 based on a False condition
from this formula in cell A1.

Thanks

Gary

How do I set a cell value based on a formula in another cell?

=IF(D4="P.B.A.",G8,0) ,This works to set the value in the cell the formula
is in, say cell A1.

I would like to have this formula reside in cell A1 but set the value in
cell A2 to (G8,0).

Also it would be helpful if I could lock cell A2 based on a False condition
from this formula in cell A1.

Thanks

Gary

Hi,
I am new to the excel macro world and my requirement is to get the summation of few(4) columns based on selection criteria of two columns. I have attached the dummy excel sheet. I want to make a generic macro which when run give me the desired output as per the values in excel sheet. Currently m using below code.
Please run the macro in the attached sheet you will come to know my requirement.

Sub test()
Dim v_count As Integer
Dim F_count As Integer
Dim U_count As Integer
Dim AA_count As Integer
Dim AG_count As Integer

Worksheets("Sort").Range("D3") = Worksheets("DM Piping").Range("D1").Value
Worksheets("Sort").Range("E3") = Worksheets("DM Piping").Range("H1").Value
Worksheets("Sort").Range("F3") = Worksheets("DM Piping").Range("I1").Value
Worksheets("Sort").Range("G3") = Worksheets("DM Piping").Range("O1").Value
Worksheets("Sort").Range("H3") = Worksheets("DM Piping").Range("U1").Value
Worksheets("Sort").Range("I3") = Worksheets("DM Piping").Range("AA1").Value
Worksheets("Sort").Range("J3") = Worksheets("DM Piping").Range("AG1").Value

Worksheets("Sort").Range("D3:J3").Select
Selection.Font.Bold = True

v_count = 0
F_count = 0
U_count = 0
AA_count = 0
AG_count = 0

'IF YOU WANT TO ADD ANY OTHER "MOC & SIZE" THEN JUST COPY PASTE THE BELOW "FOR LOOP"
'MAKE SURE FOR ANY NEWELY ADDED "FOR LOOP" SHOULD HAVE NEW VARIABLE NAME OTHER THAN(i,j,k,m)
'ALSO YOU HAVE TO CHANGE THE PRINING CELL VALUES, WHICH ARE D4,E4,F4,G4,H4,I4,J4 IN THE BELOW CASE

'START OF "FOR LOOP"
For i = 1 To Sheet1.UsedRange.Rows.Count

If Worksheets("DM Piping").Range("D" & i).Value = "MS" And Worksheets("DM Piping").Range("H" & i).Value = "50" Then
v_count = v_count + Worksheets("DM Piping").Range("I" & i).Value
F_count = F_count + Worksheets("DM Piping").Range("O" & i).Value
U_count = U_count + Worksheets("DM Piping").Range("U" & i).Value
AA_count = AA_count + Worksheets("DM Piping").Range("AA" & i).Value
AG_count = AG_count + Worksheets("DM Piping").Range("AG" & i).Value

End If

Next
Worksheets("Sort").Range("D4") = "MS"
Worksheets("Sort").Range("E4") = "50"
Worksheets("Sort").Range("F4") = v_count
Worksheets("Sort").Range("G4") = F_count
Worksheets("Sort").Range("H4") = U_count
Worksheets("Sort").Range("I4") = AA_count
Worksheets("Sort").Range("J4") = AG_count

v_count = 0
F_count = 0
U_count = 0
AA_count = 0
AG_count = 0
'END OF "FOR LOOP"

For j = 1 To Sheet1.UsedRange.Rows.Count

If Worksheets("DM Piping").Range("D" & j).Value = "MS" And Worksheets("DM Piping").Range("H" & j).Value = "250" Then
v_count = v_count + Worksheets("DM Piping").Range("I" & j).Value
F_count = F_count + Worksheets("DM Piping").Range("O" & j).Value
U_count = U_count + Worksheets("DM Piping").Range("U" & j).Value
AA_count = AA_count + Worksheets("DM Piping").Range("AA" & j).Value
AG_count = AG_count + Worksheets("DM Piping").Range("AG" & j).Value

End If

Next
Worksheets("Sort").Range("D5") = "MS"
Worksheets("Sort").Range("E5") = "250"
Worksheets("Sort").Range("F5") = v_count
Worksheets("Sort").Range("G5") = F_count
Worksheets("Sort").Range("H5") = U_count
Worksheets("Sort").Range("I5") = AA_count
Worksheets("Sort").Range("J5") = AG_count

v_count = 0
F_count = 0
U_count = 0
AA_count = 0
AG_count = 0

For k = 1 To Sheet1.UsedRange.Rows.Count

If Worksheets("DM Piping").Range("D" & k).Value = "MS" And Worksheets("DM Piping").Range("H" & k).Value = "300" Then
v_count = v_count + Worksheets("DM Piping").Range("I" & k).Value
F_count = F_count + Worksheets("DM Piping").Range("O" & k).Value
U_count = U_count + Worksheets("DM Piping").Range("U" & k).Value
AA_count = AA_count + Worksheets("DM Piping").Range("AA" & k).Value
AG_count = AG_count + Worksheets("DM Piping").Range("AG" & k).Value

End If

Next
Worksheets("Sort").Range("D6") = "MS"
Worksheets("Sort").Range("E6") = "300"
Worksheets("Sort").Range("F6") = v_count
Worksheets("Sort").Range("G6") = F_count
Worksheets("Sort").Range("H6") = U_count
Worksheets("Sort").Range("I6") = AA_count
Worksheets("Sort").Range("J6") = AG_count

v_count = 0
F_count = 0
U_count = 0
AA_count = 0
AG_count = 0

For m = 1 To Sheet1.UsedRange.Rows.Count

If Worksheets("DM Piping").Range("D" & m).Value = "MS" And Worksheets("DM Piping").Range("H" & m).Value = "350" Then
v_count = v_count + Worksheets("DM Piping").Range("I" & m).Value
F_count = F_count + Worksheets("DM Piping").Range("O" & m).Value
U_count = U_count + Worksheets("DM Piping").Range("U" & m).Value
AA_count = AA_count + Worksheets("DM Piping").Range("AA" & m).Value
AG_count = AG_count + Worksheets("DM Piping").Range("AG" & m).Value

End If

Next
Worksheets("Sort").Range("D7") = "MS"
Worksheets("Sort").Range("E7") = "350"
Worksheets("Sort").Range("F7") = v_count
Worksheets("Sort").Range("G7") = F_count
Worksheets("Sort").Range("H7") = U_count
Worksheets("Sort").Range("I7") = AA_count
Worksheets("Sort").Range("J7") = AG_count

Worksheets("Sort").Range("D3:J7").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

End Sub

I am trying to count rows based on multiple criteria in cells that are non continuous and the criteria may be repeated.
There are 4 output buckets: Not yet called (1), Called but not contacted (2), Callback(3), and Callback with Apt(4). A row with empty criteria cells is (1), A row with only "1" in any of the criteria cells is (2), if any of the cells have "2" they count as (3), and if any of the cells have a "2" and either a "b" or "c" in the adjacent cell, they should count for (4). No rows should be double counted except in the event of (3) and (4).

For example

Row 1 values (non-continuous) are: 1 2 c 1 2 ... The number '2' and 'c' (separate cells) defines the output and this row should be counted once as "Callback with apt"
Row 2 values (non-continuous) are: 1 1 1 ... This should be counted once as "Called but not contacted"

The attached worksheet has an abbreviated data set and the output that I am looking for. I have attempted versions of COUNTIF or array SUM(IF functions and I can seem to come up with the right solution.

I don't know how to write macros, which could very well be the solution.

Any help would be appreciated.

I would like to sum a column of values listed in a table based on multiple criteria. I have created cells for the user to input the varying criteria. I'm trying to use a sumproduct formula to evaluate the criteria and calculate the filtered summation. The problem that I have is that not all the criteria will be used. If one of the criteria inputs is left blank, the return value is always "0". How can I setup the formula to ignore the blank cells? Please let me know if there is a more efficient method to complete this calculation. Thanks for your help.

Here's the formula: =SUMPRODUCT((B4:B19=I3)*(C4:C19=I4)*(D4:D19=I5)*(E4:E19))

i am not sure how to phrase this, but this is what i am after...

I want to populate cell B1 with a particular value based on multiple
conditions in cell A1. I need cell B1 to do the following:

- If cell A1 = "Dog" then the value of B1 should equal the value in cell E21
- If cell A1 = "Cat" then the value of B1 should equal the value in cell F21
- If cell A1 = "Mouse" then the value of B1 should equal the value in cell G21

how the heck do i do this? is this even possible?

many thanks,
doon

i am not sure how to phrase this, but this is what i am after...

I want to populate cell B1 with a particular value based on multiple
conditions in cell A1. I need cell B1 to do the following:

- If cell A1 = "Dog" then the value of B1 should equal the value in cell E21
- If cell A1 = "Cat" then the value of B1 should equal the value in cell F21
- If cell A1 = "Mouse" then the value of B1 should equal the value in cell G21

how the heck do i do this? is this even possible?

many thanks,
doon

Hello,

I think the best thing is to take a look at the attached, then read what I'm looking for, as it would make more sense...

That being said: what I am looking to do is change cell L3 based on new criteria in cell M3.

Right now if B3=Stationery World then L3=Stationery.

What I then want to happen is if E3=Toner then M3=67200 which then has L3=Equipment Consumables and NOT stationery.

Is this even possible? Also, is there a term for this kind of thing?

Thanks for the help!

I am using Excel 2003. I created an application using GUI that asks for input and then goes to a worksheet. All the information is kept on one worksheet. I have created several reports based on the criteria in certain columns. That copy and paste only pertinent information onto a new sheet. These reports work however I can not make the reports work using multiple criteria such as if column a = "Accepted" and column b = "Pass". I believe I am having issues with my loops. Here is an example of a report that works currently but with only one criteria.


	VB:
	
 
Private Sub CommandButton1_Click() 
    Application.ScreenUpdating = False 
    Sheet10.Visible = xlSheetVisible 
     
     
    Dim temp As String 
     
    Sheet10.Activate 
    Dim ocell As Range 
    Dim bnone As Boolean 
    bnone = True 
    Dim current_row As Integer 
     
     
     
    For Each ocell In ActiveSheet.Columns(1).Cells 
        If IsEmpty(ocell) Then 
            ocell.Select 
             'MsgBox ("found at " & oCell.Row)
            bnone = False 
            Exit For 
        End If 
    Next 
    current_row = ocell.Row 
     
     
    Dim blah As String 
    Dim blah1 As String 
     
    Sheet3.Activate 
     
     
    For Each ocell In ActiveSheet.Columns("DD").Cells 
        If Trim(ocell.Text)  "" Then 
             
             
            blah = "CS" & Trim(Str(ocell.Row)) 
            blah1 = "G" & Trim(Str(current_row)) 
             
            Sheet3.Activate 
            temp = ("Onboard") 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            Sheet10.Visible = xlSheetVisible 
             
            blah = "A" & Trim(Str(ocell.Row)) 
            blah1 = "A" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            blah = "B" & Trim(Str(ocell.Row)) 
            blah1 = "B" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            blah = "I" & Trim(Str(ocell.Row)) 
            blah1 = "C" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "J" & Trim(Str(ocell.Row)) 
            blah1 = "D" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "K" & Trim(Str(ocell.Row)) 
            blah1 = "E" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "BY" & Trim(Str(ocell.Row)) 
            blah1 = "F" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
            current_row = current_row + 1 
             
             
        End If 
         
        If ocell.Row = 2000 Then 
            bnone = False 
             
            Exit For 
        End If 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(in this report everyone who has a blank in column DD is printed on the worksheet Daily Snapshot(report). Certain information is carried over onto this sheet as well as the status Onboard.)Sheet 10 is the report worksheet and sheet 3 is the main worksheet titled "application".

I have tried putting another for loop around this for loop to add a criteria such as
For Each ocell In ActiveSheet.Columns("A").Cells
If Trim(ocell.Text) = "Accepted" Then...

but it does not work.

Any help is much appreciated!! Thanks!!

I am trying to create a COUNTIF function in 1 worksheet based on multiple
criteria in another worksheet. Basically, I want to count the number of
cells that the criteria of 3 different columns in a separate worksheet (i.e.
$A:$A="Smith" and $AS:$AS="4/19/2007" and $CL:$CL="$419"). I want to count
the number of cells that meet all 3 of these criteria. Can it be done?

I need to create a formula to return a value based on 3 criteria.

if A1 < 75 return "9" in B1
if A1 is between 75 & 95 return "3" in B1
if A1 is > 95 return "1" in B1

Thanks in advance.

Mike

Hello everyone! I just registered and I need some help counting unique values based on multiple criteria. Currently I am using a different program to do this calculation because counting unique values is something that it does very easily and quickly. The problem is that I am using the data in a number of different ways in Access and Excel and I need to be able to reproduce the count on another sheet (in the same file where I am using the data for other things) when the data table is refreshed.

I've done quite a bit of searching but I think I've just confused myself and I'm not sure what the best way to do this would be.

Here's the scenario that I am working with: I am trying to count unique persons associated with a certain program in a certain "area". There are multiple values for each of these different fields and I want to be able to associate unique persons with 1 program and 1 "area". (Programs and "areas" can be mixed and matched as they could be associated with any quite a few different combinations of these")

Sample data layout:

person_____program_____area
Person 1___Program 1___Area AA
Person 1___Program 1___Area AB
Person 2___Program 2___Area CD
Person 3___Program 3___Area CE
Person 4___Program 4___Area ME
Person 4___Program 4___Area EI
Person 4___Program 4___Area LK
Person 5___Program 5___Area PO
Person 6___Program 6___Area TR
Person 7___Program 7___Area EE
Person 8___Program 8___Area QW

Although it looks like we can assume that the same person will always be associated with a specific program, it could change and I would want to identify them uniquely in each program.

Would it be better to create a concatenation of these three fields and then count uniques? I just don't know, I've come up with a few different ways but they have been labor intensive and I can't reproduce them easily.

Thanks!
Clint

I have huge amount of raw data in excel 2010 spreadsheet and I must filter it in order to make some reports from it. So far I made some of the filtering but hit a rock when I had to compare multiple values based on multiple criteria.

Here is how the part of the raw data locks like:

Column A Column B Column C Column D Column E Server Name Backup Date Start Time Elapsed Time Backup Size Server 1 05.04.2012 18:00:00 00:30:00 100 MB Server 1 05.04.2012 18:10:00 00:50:00 50 MB Server 2 05.04.2012 18:30:00 00:15:00 75 MB Server 2 05.04.2012 18:15:00 00:30:00 110 MB Server 1 06.04.2012 00:15:00 01:00:00 1000 MB Server 2 06.04.2012 18:30:00 00:15:00 50 MB Server 2 06.04.2012 18:15:00 00:20:00 115 MB Server 1 06.04.2012 18:00:00 00:25:00 100 MB

Here is what I have to provide:

Backup sessions that are running within 1-2 hours period can be considered as running simultaneously so there time can be calculated as follows:
(The Largest Start Time + Corresponding Elapsed Time) - Smallest Start Time = Backup Time Interval

So for Every Date such Time Interval I have to provide. Like This:

Column A Column B Column C Backup Date Backup Size Backup Time 05.04.2012 335 MB 01:00:00 06.04.2012 1265 MB 01:45:00
Backup Size for 05.04 is a SUM of all backup sizes for 05.04 that i have calculated with "sumifs" like this:

SUMIFS(
'RAW Data'!E:E;
'RAW Data'!A:A;"Server 1";
'RAW Data'!B:B;"05.04.2012";
)

But following part I cannot figure it out:

Backup Time for 05.04 is = (18:10:00 + 00:50:00) = 19:00:00 so 19:00:00 - 18:00:00 = 01:00:00 or 60 min. with is largest time interval (example: 18:10:00 - 19:00:00 > 18:00:00 - 18:30:00 or 18:30:00 - 18:45:00)
Backup Time for 06.04 is = ((((00:15:00 + 01:00:00) = 01:15:00) - 00:15:00) = 01:00:00) + ((((18:30:00 + 00:45:00) = 18:45:00) - 18:00:00) = 00:45:00) = 01:45:00

Hello,

I've tried to look into various options and searched numerous forums but couldn't find any solution therefore decided to post it as a new thread.

I have 2 sheets, 1 is the criteria and the other has the actual data. The position of columns in both sheets are fixed and will not change but also the headers on the criteria is identical to actual data if that helps too. What I'm trying to do is write a vba code based on the multiple criterias on criteria sheet and do ACTION on the actual data.
In the given example (attached) the criteria's are defined from column A to K and the ACTION defined on columns L to P in the Criteria sheet. The ACTION involves 2 parts, a) to change value of column in actual data based on the criterias defined and b) to delete the cell values in the actual data again based on the criteria.

In the attached example - search for Shipper Name, Consignee, Type, Carrier etc in Actual Data and change values of VIP TYPE (column I on actual data) to what is defined in Criteria "VIP TYPE" column and also delete the cell values on actual data if "YES" is defined for the headers in Criteria. I want to loop it until all the rows in the Actual Data has been processed.

As I mentioned before the columns in criteria and Actual Data sheet will not change so can ignore the header names if that will be too complicated.
I've spent a lot of time to figure out on how to do this but have failed. Any help would be highly appreciated.

Thanks and look forward to some genius answers.

Hi All,

I have a spreadsheet that's generated from a download. I need to modify one column of cells based on multiple criteria. Started to try with a nested If statement, but soon passed the limits allowed. Not sure if some kind of VLookup or VBA formula could help. Any input that would point me in the right direction would greatly be appreciated. To summarize what I'm looking for: If column A has a value of any of the following (10D, 11Z, 10Z, 2CS), then I need to change the Rate in column D based on the Years left to maturity in column B. The Rate percentage to be used is based upon the table shown in the attached spreadsheet.

In addition, there is an exception if Column A has a value 2CB and an inventory # of 9300276 or 9300277 in column C, then I need to change the rate in column D based on the table as well. Otherwise the rates stay as originally imported.

Hope this makes sense. Again, any ideas would be greatly appreciated.
I have attached a sample spreadsheet.

Thanks a bunch!
JL

Could someone point me in the right direction?
I am trying to delete the contents of a cell based on to criteria.
I could use formulas, but I have 6,000 plus rows.

Cell formula to be typed in cell I26 would be:


	VB:
	
 
=If(And(I26="a",COUNTBLANK(K26)=0),"","a") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am trying a slightly different approach with the code, but can not get my language right.


	VB:
	
 
If Cells(r, 11).Value  "" And Cells(r, 9) = "a" Then 
    Select Cell (r,9) clear contents 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any suggestions?

Thank you,
-Marc

Greetings,

I am trying to create string values that I can copy into the "To:" and "Cc:" fields in an MS Outlook email. I want to manually copy this string from a single cell on a spreadsheet. My goal is to create a string of user names that I can copy based on lookup criteria.

In the attached example, I want to populate cell A5 with this string: "Smith, Adam; Adair, Chuck;" based on the values found in cells E5:H5 ("A" for Approvers)

I would also like to populate cell B5 with the following string: "Jones, Bob;" based on the values found in cells E5:H5 ("N" for Notifiers)

Here are my requirements: I need to be able to handle up to 135 project names in Column C (135 rows of data), and 90 user names in Row 4 (90 columns of data). I also need to be able to insert and delete project names (rows) and user names (columns) as needed.

Here's the catch: I have worked with CONCATENATE and IF functions and made this work. However, these two functions are long and cumbersome working with 90 columns of data. CONCATENATE only accepts 30 values at a time, and adding or removing rows or columns when using the CONCATENATE or IF functions only screws up the cell references in the formulas.

I would greatly appreciate any help to solve this problem.

Thanks!

Paul

I'm trying to write a formula to display the contents of a cell found within
a named range based on multiple criteria. I've written similar formulas in
the past, but this one escapes me. I've narrowed it down to a simple
example, which if I can get this to work, I can apply the knowledge to my
more complex spreadsheet.

Given the following data table with named ranges the same as the column
headings in Row 1:

-A- -B- -C-
1 Name Num Mon
2 Dale One Jan
3 Barb One Feb
4 Dale Two Mar
5 Barb Two Apr

I want to return the cell value of the Mon column that corresponds to a
particular Name and Num, resulting in a grid that *should* look like this:

-A- -B- -C-
1 One Two
2 Barb Feb Apr

The incorrect array formulas I currently have in B2 and C2 a

B2 formula: {=IF((Name=$A2)*(Num=B$1),Mon,"X")}
C2 formulat: {=IF((Name=$A2)*(Num=C$1),Mon,"X")}

I've also tried:

B2 formula: {=IF(AND(Name=$A2,Num=B$1),Mon,"X")}
C2 formula: {=IF(AND(Name=$A2,Num=C$1),Mon,"X")}

When tracing the evaluation of these formulas, everything seems to work fine
until the final step. It just doesn't seem to want to select an appropriate
single cell to return from the Mon range. Instead of the appropriate month
text, it displays the X.

What's the right way to write these formulas?

Thanks in advance,

Bill

Can someone help me out with this problem. I'm trying to have a cell lookup up a certain value based on three different criterias. Here's what i have,

In cells L8:Q44 are where i want the lookup formula on sheet 7-4-10.

The value it needs to look up are in cells D8:D44 (on sheet 7-4-10) and needs to look in Sheet "Weekly Total" in Column D cells D712:D1500.

The other value it will need to look up are in cells L7:Q7 (on sheet 7-4-10) and needs to look in Sheet "Weekly Total" in Column B cells B712:B1500.

And what I need cells L8:Q44 in sheet 7-4-10 to return is the value in Column H cells H712:H1500 on sheet "Weekly Total".

If There's no value to return then i don't want anything in the cells on sheet 7-4-10.

Thanks in advance
Windows XP
Excel 2003

I have a somewhat suitable formula to enter values based on multiple criteria into a worksheet from other worksheets in my workbook.

I would like to know if there is anyway i can use a cell reference to the dates i specified in row 1 of cashflow worksheet,
rather than manually entering in the dates in the formula. This will help when i have to change the dates in row 1. I tried
to enter C1 and D1 as "datevalue" to see if that would help...

Any suggestions are appreciated.


No luck finding an answer? You could always try Google.