Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Vlookup And Matching 2 Criteria


I have an output table that looks like the followings:......

There is 1 input for the spreadsheet: What month? Enter 9, 10, 11 etc. I need it to pull from a data sheet that is set up as such:......

So effectively it needs to vlookup the name in column A, (A, B, or C), and then it needs to match that to the column that has the same name as the output table (Crit.1 or Crit.2 and the number of the month above it (10,11,etc). I attached an excel sheet to better demonstrate


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Add Two Matching Values Using Vlookup
How can i add a value suppose 10 which is in cell A2, now i use vlookup to search
lookup_value=A2(i.e.10),
Table_array=column c,
Column_index_number=1 and
range_lookup=0

so when vlookup finds the matching value the sum should be 10+10=20
also it should match -10, so that sum will be 0.

View Replies!   View Related
Vlookup Matching And Copy The Value
I tyring to match column A:B with column F:G and copy the value in column H to column C.

View Replies!   View Related
Vlookup Of Partial Matching Values
I have a worksheet with 2 tabs. Tab 1 has data in two columns, column A contains a part number (6 characters long) and column B contains a value for each part number.

Tab 2 is a sheet with multiple colums of data. I need tab 2 to look at the data in tab 1 and display the value of each part number in a specific column that has corresponding part numbers but are 15 characters in length. The part numbers in tab 1 are the last 6 characters of the part numbers in tab 2.

I have been trying to use vlookup in tab 2 but it won't work because the column of part numbers in tab 2 is a full part number and the part numbers in tab 1 are only a partial part number (last 6 characters of part number in tab 2).

Tab 1 is a worksheet that is created from a Access query of a sql database. I cannot control the abbreviated part number that the query generates.

Tab 2 part numbers (the full 15 character number) also is generated from another access query which I also can't control.

Is there any way I can have tab 2 sheet look at the data in tab 1 and display the values for the part numbers in tab 2.

View Replies!   View Related
Vlookup (matching Phrases For Values)
I am doing a project where I have two lists, one list is much larger than the other but has more info. I need some of that info for my other list, so I have used vlookup, but it hasn't helped.

The formula that I am using is
=VLOOKUP(B2,[Report.htm]report1183662451726!$A:$B,2,FALSE)

now the value I am searching for is "ANOS" which I know is in the search column, but the answer when I search is #NA. When I take out false the right value will fill in fro "ANOS", but if two words are similar such as "ALLN" and "ALEX" then it will give the same values for both which isn't right.

View Replies!   View Related
Lookups Matching 2 Criteria
If you look at my attached example. I'd like the date line/columns in Sheet "8002372008" to be populated automatically with data from Sheet "Data".

Is this possible? VLOOKUPs and MATCH are what I was thinking, but I'm not sure how to do it.

I know it could probably be done in VB, but I don't want to have to click on each "800 Number" sheet to update it every time, so that my summary sheets are accurate.

View Replies!   View Related
Matching Data With Specified Criteria
I need to match data with specific criteria. Details of the problem are as follows:

I have 2 sheets: "Main" and "Sub"

In "Main" there are 3 Important Columns: Col "C", "I" and "AF"
In "Sub" there are 2 Important Columns: Col "I' and "N".

Value in column "C" of "Main" is same as value in column "I" of "Sub", but not in same order.

Now the specific criteria:

If value in Column "I" of "Main" is not equal to "Zero", then the macro should pick up the value from the corresponding column "C" of "Main" and search for the same in column "I" of "Sub". After matching the same, it should find corresponding value in column "N" of "Sub". Then the macro should pick up the value from column "N" of "Sub" and put the value in the column "AF" of "Main".

This is basic requirement.

I am unable to retrieve the right value for the same.

Fine tuining will be:

If the value in column "N" of "Sub" is "Fully Automated" then it should only write "Automated" in column "AF" of "Main". And If the value is "Manual + ...." then it should write "Manual" in column "AF" of "Main".


View Replies!   View Related
Results From Two Matching Criteria
I am trying to solve a small problem that i have, I am not that good with VBA and need somehelp.
heres the situation.
Sheet 1 is filled in col.1= title, col.2= table# (ex. 1,2,...11)col3.= grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10). I will enter a title then pick a table then a grid location.
On sheet 2 also known as table 1. col.1 =grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10) col. 2 = title.

I want to create a macro or function that can evaluate sheet1 and pick out when col.2=table 1 and col.3 = grid loc. A-1.... through D-10 then give me what is in col.1.
this will have to be done for 11 sheets 1 sheet for each table.


View Replies!   View Related
How Can Find A Value Using Two Different Matching Criteria
Col cCol dFormula
Lease TermCompany #6023%
3637%
4843%
6052%
12620%
247FALSE
368FALSE
48105%
609FALSE

Residual Table

Company #
127
438
569
10

Col b
Lease TermResidual %
1210%20%9%
245%9%6%
364%7%3%
483%5%2%
602%3%1% ......

View Replies!   View Related
Sum Based On Matching Criteria
How do I return an offset value within a named range using a formula? For example, my range "RngTest" is from C3:J43. If a value of "Product123" is found within the range, (lets say its found in cells C3; E5 and E7), I need the accumulative values in the cells 'below' (in cells D3; F5 and F7) added ( SUM) and returned to cell L3.
Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5,
the value of cell L3 should be 12. Also, do I need to express "RngTest" or "C3:J43" or does it matter?

View Replies!   View Related
Three Matching Criteria Of Data, Is Sumproduct Appropriate?
I have two sheets where I dump data into. Widget sales and Widget Accessory sales. I'm trying to display for the widget salesmen the amount of dollars they sell in widget accessories ONLY for the transactions in which they also sell a widget at the same time.

So I need a formula or process (taking and comparing the data from both sheets) to do the following

IF
1. The dates of a widget sale and the accessory sale match (in both sheets)
2. The salesman ID in both sheets match
3. The names of the customer in both sheets match
THEN
1. Add up the total dollars by salesman for sales that match the above criteria

I've tried using sumproduct... I've tried using countif and concatenating pieces of data to make unique sales codes... I've exhausted my meager excel skills .

View Replies!   View Related
Copy Rows Matching Criteria
setup a vba code to find values which were separated by a certain value, thus there would be pairs of numbers sought after. What occured was that the first value is bolded, followed by the second not bolded, this pair would be separated by 75. Now I want to apply the same code, but instead of copying just the two cells which were separated to someone on the current sheet, I would like it to copy the entire row (as there is more data associated with it now) with the same bold/not bolded pairing to a new sheet, called "Numbers". I would imgine it be straight forward, yet I am very new to this and still tinkering.

Heres the ....

View Replies!   View Related
Delete Rows Not Matching Criteria
I have been using the code found here

Sub DeleteRowsFastest()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria

On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else.............................

to delete rows that match the given criteria. I am now wanting to do the opposite, keep the rows matching my given criteria and delete all others.

View Replies!   View Related
Count Matching Multiple Criteria
I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got

View Replies!   View Related
Matching Criteria From A Virtual ( Non-Existing Range)
Matching criteria from a Virtual ( Non-Existing Range)

I need help on two things:
1> Virtual Range
2> Displaying TRUE or FALSE

I want to perform a Lookup Using the Regular Match Function with the following Syntax for checking Dates falling in a Fixed Range...

View Replies!   View Related
Sum Index Results Matching Multiple Criteria
I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.

HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.

C is the part number column in the schedule
K is the date row in the schedule ....

View Replies!   View Related
How To Use SUMIF On Consecutive Cells Matching Criteria
How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)

=IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"")

More data:

Column A is a group of dates, but not in order, they are slightly mixed up
Column H is a monetary value.

View Replies!   View Related
Array With Values From Cells Matching A Criteria
How do you load an array with values from cells matching a criteria?

I want to look for the number 1 in row 5 and put the Adjacent values in Row 2 in an array.
So here my array should have 1,3,6

Dim CountOne As Integer
CountOne = Application.WorksheetFunction.CountIf(Worksheets("Tool Administration Form").Range("B8:AE8"), "1")

Dim All As Variant
Dim rCell As Range
Dim ArrCnt As Integer
Dim lArrVar As Long
ArrVar = 0

Do
For Each rCell In Worksheets("Tool Administration Form").Range("B8:AE8")
If rCell = 1 Then
ArrCnt = ArrCnt + 1
All = Cells(-3, 0).Value
End If
Next rCell

Loop Until ArrCnt = CountOne
I keep getting errors... I don' tknow how to fill the array properly wiht the values in the cells.

Example:
A B C D E F G
1
2 1 2 3 4 5 6 7
3
4
5 1 2 1 3 3 2 1 4



View Replies!   View Related
Adding To Summarize Values After Matching Criteria
I have three worksheets. The first one has a 20 digit code that is broken into eight sections ( cells) and an amount assign to it. There are currently 150 codes but can grow bigger as new codes are assigned with their own amount. The other two worksheets have the same breakdown for the code but will be filled in as needed. They will record additions or subtractions to the code, like a register. I would like to then summarize (on a fourth worksheet) the +/- activity from worksheet 2 and 3. To do this I will copy the first worksheet then setup three additional columns one will be Increase the other Decrease and the last one Balance. What I will need to make sure that I only pick up the amount for the right code. So I need to create a formula that makes sure that it compares wks 2, cell A1 to wks 4, cell A1 AND wks 2 cell B1 to wks 4, cell B1 AND wks 2, cell C1 to wks 4, cell C1 and so fourth until the eighth breakdown of the code. When this is true then I will need to add the amount that was recorded on wk2 or wk3 for that code on wk4. The codes may be used various times so the sum of the values of wk2 or wks3 will need to be cumulative.

I've been reading about Sumproduct and I have created this formula but the result is # NUM....

View Replies!   View Related
Delete Rows Not Matching 3 Static Criteria
I have a worksheet with 20,000 plus rows, header=yes, and I need to delete all rows NOT containing the numbers 6600, 6700 or 6800 in column C. I'm currently using the below code which works, but is DOG slow.

Sub TestDeleteRows()

Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With

View Replies!   View Related
Return Value By Multiple Matching And Unmatching Criteria
I am developing Spreadsheet that you will enter a list of parts that you need. it will have many columns but only 4 are vital to the check. this will be generated for each project.

With each part i want to then look up in another file that is a list of all the possible parts you can have with there cost. this file will be standard for everything and it will be updated from time to time.

now not all the criteria to look up will be exact matches.

here is an example

code temp/pres rating diameter material
GLV 4500 15 16Mo3

in the other file we will have multipu entries for GLV with different values.

code temp/pres rating diameter material Cost Reference
GLV 4500 20 16Mo3 400 aaa1
GLV 5500 15 16Mo3 350 aab1
GLV 5000 15 16Mo3 300 aab2
GLV 5000 15 13CrMo4-5 600 acb1
GLV 5500 15 13CrMo4-5 600 acb2

For Code i need it to match (there are lots of codes even including variations like GLV.)
For Temp/pres rating i need it to be equal or higher to the value
For diameter i need a match
for material i need a match.

so as you can see from the list above there are 2 options. so then i want it to return the cheapest.

that is step one.

step two is to repeat it for multiple list of parts.

View Replies!   View Related
Copy Cells Matching Multiple Criteria
On my worksheet (which I have stripped down significantly to be able to upload here), I am looking for a way to copy certain cells over when the TCR (machine) numbers match. So, on the spreadsheet, if there is a 1 on the De La Rue sheet in the A column, it will then copy the value that is in cell B (directly next to it) and paste it into the balance sheet tab in column J in the row that has the same number in it. It would then do this for all machines in the balance sheet.

Then it would go to the "Ecoin Amounts" sheet, and do the same match, copy, paste, but this time it would be if the machine number matches, it takes the value from column D and the value from column H and pastes these into the balance sheet sheet in columns C and E respectively.

As you can see from the balance sheet tab, I tried using a VLOOKUP function, but when I import my initial data to the sheets, and manipulate it the way that I want, it gives be a reference error. I could possibly circumvent this by writing the VLOOKUP amounts as part of my macro, but I wanted to see if this could be done any easier.

View Replies!   View Related
Move Worksheets To Workbook Matching A Criteria
What I want to try and do is move certain worksheets if their name is changed into another workbook. So there are two scenarios:

1. If any worksheets name is changed to begin with "Old", then it is automatically moved to a workbook called "OldPlans"
2. If any worksheets name is changed to begin with "Expired", then it is automatically moved to a workbook called "ExpiredPlans"

When worksheets are moved to new workbook I would like them to be positioned towards the end of the workbook. So I am not sure how to go about this, first would it have to have a Name Change Event or something to trigger off what gets moved?

View Replies!   View Related
Find Rows With Cells Matching Criteria
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.

The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)

Step 1:
"Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".

Step 2:......................

View Replies!   View Related
Count Cells Matching Multiple Criteria
I am using the below code to count records in a range that meet multiple criteria.

myCount=0
For Each dCell In Range("MyRange")
If dCell = var1 And dCell.Offset(0,-1) = var2 Then myCount = myCount + 1
Next dCell

Is there a faster way to count multiple criteria in a VBA procedure than what I'm doing, but without using worksheet formulas? I tried "WorksheetFunction.Sumproduct" like you would in a worksheet formula, but that didn't work (unsurprisingly).

View Replies!   View Related
Count Unique Values Based On Matching Criteria
I have a worksheet with two tabs.

First Tab
Account Name
Account Number

Second Tab
Account Name
Account Number
Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

View Replies!   View Related
Two Page Lookup Matching Criteria Based On Cell Value
I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.

With that said....
1. How do you put in the drop down box that references information on a different sheet?

2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?

View Replies!   View Related
Find Value Matching Multiple Text Criteria And Max Date
I have the following dataset:

[Date] [Category] [Currency] [ExchangeRate] [.....], etc.
1-3-09 A USD 0,8
1-6-09 A EUR 1
1-7-09 A USD 0,7
1-8-09 B USD 0,9
1-9-09 B USD <formula>

I'd like to have the value of <formula> looked up in older records. Currency and category should match and it should pick the exchange rate with the maximum date.

Which formula and what syntax should I use to have this done?

I use Excel 2007.

View Replies!   View Related
Vlookup Using 2 Criteria
Is it possible to lookup first & last from sheet 2?

I know how to use 2 lookup values from sheet1 but not from sheet2:

=vlookup(B5&C5,???,3,false)


*BCDEFGH3Sheet 1***Sheet2**4FirstLastSales*FirstLastSales5JohnSmith**JohnSmith5006JohnNelson**JohnNelson3007JohnPeterson**JohnPeterson400

Excel tables to the web >> Excel Jeanie HTML 4

View Replies!   View Related
Vlookup With Two Criteria
Is there a way to do a vlookup with two separate criteria?

View Replies!   View Related
VLOOKUP Using Two Criteria
I have data organised as follows:

Column A:Date
Column B: Fund Name
Column C:Fund Value

In D1 I have the phrase “Fund A”, in E1 I have the date 31/08/2009 and in F1 I would like to create a VLOOKUP formula that looks in the range A:C and returns the value of Fund A on 31/08/2009. Fund A has a value for many other dates too so the VLOOKUP would have to be able to accommodate both criteria.

View Replies!   View Related
Add Another Vlookup Criteria Into The Statement
I want to add another vlookup criteria into the statement below and im having some difficulty, its currently has two called "Name" and "QrtName", i want to add another called "PlaceName". I have a named range called "Data" for the data the vlookup options use. It is working fine other only i want to add the third vlookup option:

View Replies!   View Related
Vlookup - 3 Unique Criteria
Is there any way to nest a vlookup within an If statement that is capable of looking through 1 column on a separate worksheet for any of the following words "BASE" "MID" or "TOP" and then return a serial number in a separate column?

View Replies!   View Related
Vlookup Formula (2 Criteria)
currently my formula is as follows:

=VLOOKUP(H42,POQntyTOTAL,5,FALSE). i need to add another criteria to this formula.

that is....i need to have it return column 5 from POQntyTOTAL, where H42 AND where E42 is found.

View Replies!   View Related
Vlookup / Index On 2 Criteria
I have tried the Vlookup formula, and also the index/match formules but I can't get my desired outcome. As you can see the setup is fairly simple. For turnover I want to lookup the values based on Period AND profit center This last bit is my problem, how to define a double criterium.

The idea is to choose the wanted latest estimate (cell B1) and that for turnover, discount etc the values are based on 2 criteria (periode and profit center)
I have also added the desired outcome to clarify my question.

View Replies!   View Related
Vlookup Worksheet Name As Criteria
Is there any way that a worksheet tab name can be used as part of vlookup? I have got a spreadsheet with many worksheets (the names of these worksheets are different account numbers eg 3010,3020 etc). I have then got a summary sheet which summarises the balances.For eg on the summary sheet, I have set up all account numbers (as per the worksheet tab names) in Column A: 3010 3020

In Column B, I have to manually pull in the balances from various worksheets. Is there a way this can be done automatically and save me time as I normally have to pull in balances from over 50 worksheets.

View Replies!   View Related
Vlookup With Unique Multiple Criteria
Im just going to start with the tables that will help show what i am trying to do...

TABLE 1
File #Dateid
215?
211?
27?
26?

TABLE 2
File #StartStopid
29131
214162
2183

Ok so say i have these two tables...i want to be able to look at the file # of line 1 in table 1, go to table 2 and return the id where the date from table 1 is between the start and stop in table 2. ie...in that example it should return id "2".

View Replies!   View Related
VLOOKUP Multiple Criteria (Dates)
In column I, there is a list of dates - month and day only

In column J there is a list of dates, month,day, and year.

--I----------------J
1/14----------1/14/2009
1/14----------1/15/2009
1/14----------1/16/2009
1/15----------1/17/2009


in column K, I am trying to setup a Vlookup statement that states if the month and day in column I = the month and day in column J then return the value in column J.

I have the following formula, but it just returns N/A.

View Replies!   View Related
Vlookup Formula Is Working On Some Criteria But Not Others
The vlookup formula is only partly working on the attached sheet, but I cant find an explanation as to why it can look up some criteria but not others.

View Replies!   View Related
Multiple Criteria Vlookup In Vba Userform
I have a problem with vlookup, some of the items in the lookup array are not unique and vlookup gets confused with multiple matches, however if I can use a combination of 1st and second columns as the criterion then it would result in a unique match.
Problem is I don't know how to acheive this in vba (but I can with the worksheet formulas). (attached is a sample worksheet)

View Replies!   View Related
“Vlookup” Or “Sumif” With Multiple Criteria ?????
I would like to take a precise value from one table which corresponds to two different criteria. See the example attached with this message.

View Replies!   View Related
Vlookup Multiple Criteria On Two Lists
I have a workbook with two sheets

sheet1(Invoices) is the master and sheet2 (GR) contains information that i need to put onto sheet1.

I need a formula that will allow me to lookup sheet2 based on the criteria from 3 columns on sheet1... (PO no, Part No, Qty)

At the moment I am using 3 different vlookups to achieve what I need

If there are records on sheet2 which matches sheet1 then I would like to have the word MATCH if there is a match and CHECK if there is not?

View Replies!   View Related
Fill Cells Based On Corresponding Cells Matching 3 Criteria
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.

I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.

I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.

View Replies!   View Related
Delete Rows Matching Criteria & Move Rows
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:

1- all rows with the value "TOM" in column C will have to be deleted.

2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).

View Replies!   View Related
Multiple Criteria - Vlookup For Numerical Values
I have the following 'numerical values'

2510000.011
3010000.011
3510000.011.3
2518000.032.5
3018000.043
3518000.043.7
2530000.125.5
3030000.145.8
3530000.176.2

With the first two values as criterias, I need to find the 3rd and 4th value
Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3

View Replies!   View Related
Vlookup:keep Cell Reference As Lookup Criteria
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.

View Replies!   View Related
Next Date Matching Criteria Within Date Range
I'm setting up a spreadsheet to manage recurring tasks that fall within a given date range.

I have a named variable (theDate) which pulls the list of matching tasks that fall on a single day, but some fall on multiple days, so I need to somehow extract the next valid date the task will come due.

I've been playing with formulas until my brain got scrambled. I suspect I may not have got enough sleep over Christmas and there's something staring me in the face I'll kick myself over.

Column Headings are:
Description (Col B), [various notes C:E], M, T, W, T, F, S, S, (Cols F:L, marked with x when relevant), StartDate (M), EndDate (N) and my nemesis, NextDate.

That formula should check that 'theDate' falls within the range, if so, lookup whether the appropriate weekday is null or not, and if it's not, then the current date comes back. If it is, then I need it to find the next weekday (by that I don't mean M-F, I mean any of the 7) when it will fall and return that date. I do have the weekday number in F2 if needed, and I'm using a weekday return type of 2 (Mon=1).

'theDate' will usually be in the future, but not always. It needs to not fall over if it's past.

I'd like to do this via formula ideally, since the SOE I'm on keeps losing my Analysis Toolpak, but if I have to code, I have to code.

View Replies!   View Related
Vlookup Formula That Incorporates Multiple Search Criteria
Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11.

agent rank score supervisor
A B C D
1 leo 1 99 jim
2 bob 3 68 jim
3 sam 1 73 ann
4 tim 2 84 adam
5 alf 2 55 ann
6 dan 3 42 adam
7 will 2 79 jim
8 sara 1 91 adam
9
10 Rank: 1
11 Supervisor: adam
12 Agent: " "

View Replies!   View Related
Bank Reconciliation - Many To One Matching And Partial Cell Matching
programming 2 macros in a bank reconciliation sheet I am trying to create.

Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.

The following two situations can occur and which need to be covered with a macro.

Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.

Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.

View Replies!   View Related
Vlookup With Multiple Criteria: Creating A Drop Down List Showing Alternative Choices With Sex
I am trying to create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:

There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.

Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have:
M 60%
M1935 64%
M1935B 35%....

View Replies!   View Related
Matching Cell Formats Based On Matching Cell Values
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.

I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved