Results 1 to 10 of 10
Thread: WHERE formula?

20010228, 18:01 #1
 Join Date
 Feb 2001
 Location
 Louisville, Kentucky, USA
 Posts
 605
 Thanks
 0
 Thanked 0 Times in 0 Posts
WHERE formula?
I have a workbook with several worksheets.
1 of the worksheets name is "Summary" which basically summarizes bits of data from the rest of the worksheets.
In Summary "C3" I have the following formula that works just fine: (C2 is a date value)
=IF(C2='Test1'!D5,'Test1'!D18,"")
C2 looks at Test1 "D5", determines if they equal each other and if they do, it returns the value that is in Test1 "D18". (Test1 D5 is a Date Value)
What I am trying to accomplish is this:
I want Summary C3 to determine WHERE in Test1 is Summary D2 = and then return the value that is in Test1 D18.
reason: I will be changing the date in Summary C2 biweekly but the data in Test1 D5 will remain the same except biweekly I will be adding another column with a new date in E5.
Roberta NewtonRoberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

20010228, 19:08 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
In your note, the statement of your problem said, "I want Summary C3 to determine WHERE in Test1 is Summary D2 = and then return the value that is in Test1 D18." The way I read that, you always want to return Test1 D18, no matter where the date is equal to C2. If that is correct, then you don't need any kind of test at all.
If that is not correct, then could you explain better what you do mean. Also, can you tell us what you mean by "WHERE in Test1 is Summary D2 =?" Do you mean that you want to compare Summary D2 to every cell on Test1?Legare Coleman

20010228, 19:29 #3
 Join Date
 Feb 2001
 Location
 Louisville, Kentucky, USA
 Posts
 605
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
Thanks for responding. I apolgize if I wasn't clear.
Summary!C3 is a date field as well as Test1!D5.
Today is 2/28, if I updated this spreadsheet today, both Summary!C3 and Test1!D5 would have the 2/28 date therefore =. Since they equal, Test1!D18 has the data I am after. However, in 2 weeks the date in Summary!C3 will change to 3/14 but Test1!D5 will will still be 2/28. In Test1 I will add a new column, "E5", and it will have the 3/14 date and will now equal Summary!C3. Therefore E18 now has the data I am after. (the column will always change but the row will not)
Therefore, my "Where" is where Summary!c3=the "DATE COLUMN" in Test1, grab the data from that column(E) but same row(18).
I hope this is more clear.
Again thanks for your help.Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

20010228, 19:50 #4
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
Are you trying to find the most recent date in a given row ?
If so name the row xDate or whatever you wish, and the formula =MAX(xDate) would give you what you want.
Hope I have understood your problem
Andrew C

20010228, 20:14 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
A formula something like the following should give you what you want. You will need to adjust the ranges to fit your sheets.
<pre>=OFFSET(Summary!C18,0,MATCH(Summary!C3,Test1! D5:N5,0))
</pre>
Legare Coleman

20010301, 12:25 #6
 Join Date
 Feb 2001
 Location
 Louisville, Kentucky, USA
 Posts
 605
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
Thanks for the suggestion. I think this is almost what I am looking for. I'm not sure why reference C18 and when I apply the formula it returns N/A. I am not familiar with OFFSET so any help would be appreciated.
ThanksRoberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

20010301, 12:51 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
First, the formula I gave you had an error in it. I tested the formula on a single sheet and then pasted it into the message and typed in the sheet names. I typed in the wrong name for the first reference. The formula should read:
<pre>=OFFSET(Test1!C18,0,MATCH(Summary!C3,Test1!D5 :N5,0))
</pre>
In that formula, the Match function compares the value in cell Summary!C3 to the values in cells Test1!D5:N5. In your case, that should compare the date in cell C3 on sheet Summary to the cells in the range from D5 to N5 on sheet Test1. The 0 says to look for an exact match. If it finds a match in cell D5 it will return 1, cell E5 will return 2, cell F5 will return 3, etc. That value is used as the column offset value in the Offset function. The Offset function starts at cell C18 on sheet Test1 (incorrectly Summary in my first reply), and offestes it by zero rows (the second argument) and by the returned value from the Match function columns. Therefore, if the Match function finds a match in D5 the offset will be 1 column. Cell C18 offset by 0 rows and 1 column is cell D18, and the value from that cell should display in the cell with this formula.Legare Coleman

20010301, 13:02 #8
 Join Date
 Feb 2001
 Location
 Louisville, Kentucky, USA
 Posts
 605
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
Positively Brilliant!!!!!!By reading the description of the function, it is perfect...however, it is still returning N/A. I'm sure it's me. I put the formula in Summary!C4 will that cause this problem?
Thanks again.Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

20010301, 13:14 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
The formula will return a #N/A if there is no match for the value in Summary!C3 in the specified range in the Match function. It does have to be an exact match. So, if you have date values that also include time, the date serial number, including the time would have to match. Comparing date values can be tricky. If this is your problem, then you might consider changing the Match type from 0 to 1. The formula can also be changed to compare only the date part of the date values.
Legare Coleman

20010301, 13:58 #10
 Join Date
 Feb 2001
 Location
 Louisville, Kentucky, USA
 Posts
 605
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: WHERE formula?
Legare,
Thanks for all of your help...it works perfectly, exactly what I needed.
I put a call into MSSUPPORT 2 days ago, they asked for a sample spreadsheet and I haven't heard from them since. You were able to resolve this for me in a matter of minutes.
I sincerly appreciate it.Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>