Microsoft Office Forums - View Single Post

Bird_FAT · 04-30-2009, 03:18 AM

By Crickey Tinkertron - you REALLY need to turn this into a database!
This one took me and another collegue ages to work out a form of solution - whereas both of us said that it would take a minute to set up in Access!!

OK - here goes (anyone out there with a simpler, cleaner solution, PLEASE, jump right in):

You'll need to:

Create a new column (for this example, I'll call it Column C, and it will be to the right of your Hearing Type Column) (it can be hidden after you have finished setting it up!)
In the top cell add the formula
Code:
```
=Today()
```
This will make the Cell Value equal to Today's Date
In the column (eg Column C) next to your Date (eg Column A) and Hearing Type (eg Column B) we need to enter a formula that will do the following:

Look in Column A and make sure it is a later Date than in Cell C1
Look in Column B and find the different Hearing Type info
If both values are true, then return a code number for each of the different Hearing Type options (this is so we can use that COUNTIF function again to return the value to your second sheet)

Here is the unedited formula

Code:

=IF(AND(A2>=$C$1,B2="rh"),"1",IF(AND(B2="prelim",A2>=$C$1),"2","0"))

It's and embedded If function, using an AND function to make the logical test dependant on BOTH results being positive. IF(*logical test* (does it find a date greater than today in column A and the "rh" code in Column B),*If YES*, put the number 1 into the cell, ,*If NO*), go to the next IF Statement) - Normally the *If NO* would place another value in the cell
Code:
```
=IF(A1="dog","There is a DOG","The DOG was a figment of my imagination")
```
As you can see in the unedited formula above, I have, instead, added another If formula - this can be done up to 7 times in MSO 2003 (So I hope you don't need more than 7 hearing types). All you do, is start the next If Statement instead of the *If NO*

Code:

A2 = Cell one of your Date Column
B2 = Cell one of your Hearing Type Column
$C$1 = Absolute reference to the cell containing todays date
1 = Value returned for hearing type "rh" if date is equal to, or greater that date in C1
2 = etc.

The idea here is to return a "0" if the date is befor today's date, and the numbers 1-7 for each hearing type where the date is equal to, or greater than the date in Cell C1.

As long as you have made the Cell reference to C1 Absolute ($C$1) then you can drag the formula down Column C.

When you have finished setting this column up, you can hide it if you wish.

NEXT
On your second sheet, you can now use the COUNTIF function from before, but modified to look for the Code Number in Column C relating to the hearing type.