test for value and return position

I am trying to accomplish two things.
1) I want to test for the existance of a value in a range of number that
varies from day to day.

2) I want to return the cell reference of the data obtained in question (1)

Any IDeas? I though of  using something akin to H2:INDEX(H:H,COUNT(H:H)+1))
but I think I'm not on target here

Jeff



0
bubs (4)
12/10/2004 2:54:28 AM
excel 39879 articles. 2 followers. Follow

6 Replies
423 Views

Similar Articles

[PageSpeed] 42

On Fri, 10 Dec 2004 02:54:28 GMT, "Buster" <bubs@gmail.com> wrote:

>I am trying to accomplish two things.
>1) I want to test for the existance of a value in a range of number that
>varies from day to day.
>
>2) I want to return the cell reference of the data obtained in question (1)
>
>Any IDeas? I though of  using something akin to H2:INDEX(H:H,COUNT(H:H)+1))
>but I think I'm not on target here
>
>Jeff
>
>

=MATCH(val,H:H,FALSE) will return #N/A if the val does NOT exist.

=ADDRESS(MATCH(val,H:H,FALSE),8) will return the cell reference.

Use something like:

=IF(ISNA(MATCH(val,H:H,FALSE)),"value does not exist", "value exists")

and

=IF(COUNTIF(H:H,val)=0,"",ADDRESS(MATCH(val,H:H,FALSE),8))

Note that only the first MATCH will be returned if there are duplicates.


--ron
0
ronrosenfeld (3122)
12/10/2004 3:43:59 AM
Gave you the response below in your earlier multi-post*
in .worksheet.functions

*something to refrain from
--
Perhaps this example might help move you along a little?

Assume you have
in Sheet1, in A1:A5
---------------------------
11
22
33
44
55

In Sheet2
-------------
Assume you want to sum Sheet1 col A's numbers
from cell A1 until a cell in col A which number
matches a number input in A1 (in Sheet2)

Put in A1, say: 33

This number 33 matches the number in cell A3 in Sheet1
and what you want is: =SUM(Sheet1!A1:A3)
(i.e. 11+22+33 = 66)

If the number in A1 is 44,
what you want is: =SUM(Sheet1!A1:A4)
(i.e. 11+22+33+44 = 110)

And so on

Should there be no matching number in Sheet1's col A,
if Sheet2's A1 contains say: 35 (not found in Sheet1's col A),
let's assume we want blanks: "" to be returned

Try this to get the equivalent of the above ..

Put in B1:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"",SUM(INDIRECT("'Sheet1'!A1:A"&MATCH(A1,Sh
eet1!A:A,0))))

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Buster" <bubs@gmail.com> wrote in message
news:Ed8ud.565774$SM5.33599@news.easynews.com...
> I am trying to accomplish two things.
> 1) I want to test for the existance of a value in a range of number that
> varies from day to day.
>
> 2) I want to return the cell reference of the data obtained in question
(1)
>
> Any IDeas? I though of  using something akin to
H2:INDEX(H:H,COUNT(H:H)+1))
> but I think I'm not on target here
>
> Jeff
>
>
>


0
demechanik (4694)
12/10/2004 5:19:54 AM
I've tried your suggestions and I'm not having much luck. When I tried
=IF(ISNA(MATCH(val,H:H,FALSE)),"value does not exist", "value exists")
I get Value Exists no matter what I have in the column. I tried
clearing all data and formulas, but the anomoly still exists.

When I tried the
=IF(COUNTIF(H:H,val)=0,"",ADDRESS(MATCH(val,H:H,FALSE),8)) calculation
I get a blank result even though the column i populated with at least
one value. To check things I did a Count(H:H) and get 1 so I should
therefore invoke the remaining portion of the If statemnet but it does
not

0
12/10/2004 2:40:50 PM
On 10 Dec 2004 06:40:50 -0800, "jeff.potts@cibc.com" <jeff.potts@cibc.com>
wrote:

>I've tried your suggestions and I'm not having much luck. When I tried
>=IF(ISNA(MATCH(val,H:H,FALSE)),"value does not exist", "value exists")
>I get Value Exists no matter what I have in the column. I tried
>clearing all data and formulas, but the anomoly still exists.

What did you substitute for val?  If that is giving an error, you will get the
result you are seeing.

>
>When I tried the
>=IF(COUNTIF(H:H,val)=0,"",ADDRESS(MATCH(val,H:H,FALSE),8)) calculation
>I get a blank result even though the column i populated with at least
>one value. To check things I did a Count(H:H) and get 1 so I should
>therefore invoke the remaining portion of the If statemnet but it does
>not

>I did a Count(H:H) and get 1 

	That means there is only one numeric value in column H but says nothing
about what it is.

I suspect that whatever value you are substituting for "val" and the values in
column H are not of the same type.  Especially in view of your COUNT function
results, it is likely that the values in column H are really TEXT, and whatever
you are substituting for "val" in the formulas is NUMERIC.

If that is the case, try substituting a text string for "val":  e.g. "1"
instead of 1.

If these pointers don't help, post an example of your actual data.






--ron
0
ronrosenfeld (3122)
12/10/2004 6:47:39 PM
The problem is the Val replacement issue. The colum to be checked is a
date formatted column upon import. How do I manage my "Val" to pick up
the dates. I get #NA when I use a date as my val replacement.

Do I need to convert it into excel serial date format and check for
existance and then convert back?

0
12/10/2004 7:20:57 PM
On 10 Dec 2004 11:20:57 -0800, "jeff.potts@cibc.com" <jeff.potts@cibc.com>
wrote:

>The problem is the Val replacement issue. The colum to be checked is a
>date formatted column upon import. How do I manage my "Val" to pick up
>the dates. I get #NA when I use a date as my val replacement.
>
>Do I need to convert it into excel serial date format and check for
>existance and then convert back?

You have to make them the same, somehow.

From what you've written, especially the results of the COUNT formula, I
suspect that the dates that you have imported are not an Excel serial date but
rather are in text format (except perhaps for one of them, since COUNT(H:H)
gave you a 1 as a result).

You can prove or disprove that thesis by entering an ISNUMBER(H5) function
(assuming one of these dates is in H5).  If it is an Excel date, you should get
a TRUE result.  If you get a FALSE, it is likely a text string that looks like
a date.  And, if you are importing it from a web page, there may be a <hard
non-breaking space> at the end (CHAR(160)).

Whether it will be easier to convert the dates in Column H to real dates, or
convert Val to an identical text string, is a decision that will await further
information from you about this data.


--ron
0
ronrosenfeld (3122)
12/10/2004 8:54:30 PM
Reply:

Similar Artilces:

Can the Description Field be change in Position Setup later?
Hello, My accounting department is wanted to change the description field from the Position Setup to another name than they 1st started out with. Located in Tools/Setup/Payroll/Position Setup. Do anyone know how this change will effect the current accounts establish the posting connection between Payroll and the General Ledger? I have read about the Departments and Positions are simple to setup and are critical to a successful payroll system, but non thing on making changes to them after they are setup. Any help would be appreciate. Ed ...

Overlaying bars so that lesser value is on top
Hello, I have a chart with the following data : - 2004 2005 Jan 3 4 Feb 2 2 Mar 5 1 Apr 4 2 May 2 3 Jun 2 4 Jul 5 3 Aug 3 2 Sep 4 5 Oct 4 4 Nov 3 3 Dec 1 2 I would like to show a horizontal bar chart, but with only one bar for each month, what i would like is to see the lower value on top of the higher value. is this possible? -- Lynxbci3 ------------------------------------------------------------------------ Lynxbci3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10377 View this thread: http://www.excelforum.com/showthread.php?threadid=396147 If I...

"The messaging interface has returned an unknown error"
When trying to access archived folers I get the error message "the messaging interface has retunred an unknown error". I'm using outlook 2007. IT person is looking into buying software to fix problem, not sure if there is an alternate remedy. Are you archiving from an Exchange mailbox? What is the size of the pst-file you are archiving to? Is this pst-file located on your local hard disk or somewhere else? Also, have you checked your pst-file for errors already with scanpst.exe? See http://www.msoutlook.info/question/77 -- Robert Sparnaaij [MVP-Outlook] Coauth...

ANOVA procedures and t-test equal variance
In looking through Excel I see that it has both the ANOVA procedure and a t-test with equal variances and a t-test for unequal variances. One of the assumptions that is made in both the "standard t test" and ANOVA is that the variances are equal. Yet, Excel seems to have no test for homoscedasicity (oh what a cool word). How do you know these procedures are ok to use if you have not checked the assumption of equal variances? Noosa - > In looking through Excel I see that it has both the ANOVA procedure and a > t-test with equal variances and a t-test for unequal vari...

Combo Box default value
I have table that stores group members and their relative information (name, supervisor, etc). I have created a query to find only the current members. I also have a table to store meeting attendance. I am developing a form for this table. I have 10 combo boxed to select a members name. The row source for these combo boxes is the membername field from the aforementioned query. Insted of manually selecting each members name in the combo boxes, I would like to set a default value. For example I would like the default value for Member1 to be the first person listed in the current me...

test of wizard
-- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=f61980a5-9a9d-4399-8044-95f7dc1810...

convert formulas to values in place
I know how to convert formulas to values by copying to another sheet with Paste Special. Is there a way to change the formulas to values in place, without having to copy the data to another sheet? I have a data table created by pointing to cells in different parts of the workbook. I am trying to create a "static" table that does not get updated with changes in the source data (why? you may ask, but that is a long story) Hi! Yes. Copy the data. Keep the same selected cells (i.e. don't click any other cell). Choose Paste Special: select Values: OK. Obviously, the formul...

Setting values of a filtered array
Hi All I want to read in a csv file, filter it based on the values of two of the fields and set the value of another field. Here's a simple example of what I'm trying to achieve: c:\somefile.csv contents: firstField,secondField,thirdField 1,2,"somevalue" 2,2,"avalue" 3,1,"somevalue" # Import file into array $csv = Import-Csv c:\somefile.csv # Where secondField = 2 and thirdField = "someValue" set thirdField = "anotherValue" $csv | where {$_secondField -eq 2 -and $_.thirdField = "somevalue"} | <set valu...

Hit testing
I'm trying to implement client area hit testing in a MDI application. In my document, I have a list of graphic objects (e.g. polygons) that are displayed in the view. The list can get pretty long, so the hit test algorithm needs to be fairly efficient. My first attempt at this was to add HitTest(CPoint) function to the graphic object class. In this function I created a Region and then tested it. Something like... int CPolygon::HitTest(CPoint Point) { CRgn Rgn; Rgn.CreatePolygonRgn(PointList.GetData(), PointList.GetSize(), WINDING); if (Rgn.PtInRegion(Point)) ...

Hyperlink using multiple cell values
Excel 2002 I want to make a hyperlink to a web address using the values of two seperate cells. Example: A1 (contains a constant) http://finance.yahoo.com/q/hp?s= A2 (Contains a variable) DIA, SPY or QQQQ Need to goto each one at a time: http://finance.yahoo.com/q/hp?s=DIA http://finance.yahoo.com/q/hp?s=spy http://finance.yahoo.com/q/hp?s=qqqq I can make a macro to do what I want on a single cell value, but I do not want to make a macro for each variable I have. Try this: =3DHYPERLINK(A1&A2,"jump") The cell will display "jump" as a hyperlink - click on it and...

ignore -- testing
Haven't seen a posting in several NGs since 6/25. Just curious if GG is updating these NGs. I'm using the "old" user interface. Can't find these NGs using the new GG "experience". just testing "joeu2004" <joeu2004@hotmail.com> wrote in message news:812e733c-2d95-4174-8a3d-d08ecc672145@e17g2000prj.googlegroups.com... Haven't seen a posting in several NGs since 6/25. Just curious if GG is updating these NGs. I'm using the "old" user interface. Can't find these NGs using the new GG "experience". testing a...

Testing an Exchange backup
Hello everyone I have been backing up my Exchange database (SBS 2003) using an online backup with the NTBackup utility, and everything appears to be working fine: backup files created in the right place at the right time, and a log file saying that the backup was successful. However, I would like to make sure that the backup has worked by using it to restore data. My problem is I want to be absolutely sure that if everything goes wrong and I can't restore, that I won't lose any data. I wonder whether the following plan would work? 1. Dismount my databases. 2. Rename my priv1.e...

Moving Gmail Acct to Outlook? Passes Test but won't work Why/Fix?
I have followed the directions on the gmail page on moving my acct to outlook. It passes the test and yet does not work. What gives? How do I fix? Is this some weirdness between google and microsoft. Any suggestions? Help! Could you be a little more specific? What doesn't work? Does it send? Doesn't receive? Do you get any error messages? What version of Outlook? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to direct r...

Append Rows based on field value
I would like to have the append query create a fixed number of rows based on the value from a field value. For example, field value = 10. I would like my append query to create 10 records. Bernie wrote: >I would like to have the append query create a fixed number of rows based on >the value from a field value. > >For example, field value = 10. I would like my append query to create 10 >records. You could do that using a VBA procedure and add the rows through a record set opened on the destination table. If you want to do it using a query, then cr...

Equation to refer to data value(s) and not the cell??
hi, i'm trying to have a cell reference in an equation following a moving data point ... Assumption #1 - column A has data A1=1, A2=5, A3=3 Assumption #2 - column B has an equation referring to the "3" in A3 Goal - to have my equation follow the data point "3" Initially - my equation refers to cell A3 (where the value "3" is located) Interaction - I **sort** column A so that the data is ascending order, i.e., A1=1, A2=3, A3=5 Problem - my equation still refers to cell A3 (i.e., value is now 5, originally was 3). I want the equation to refer to wherever m...

Closed Positions of Equity Options in Money 2004 dissapear
Most of the equity options positions that are closed in my portfolio simply dissapear from everywhere I am aware of looking. They do not show in the Portfolio view, in the investments reports, in the list of investments in the Work With Investments menu. However, the transactions appear in both the investment account registry and the cash account registry. I´m not sure if the portfolio totals are correct and if these totals include the missing options gains or losses. Does anyone have encountered a similar problem and what is the solution? Thanks for the help In microsoft.public....

Query Syntax to determine if the value of one field is in other fi
Hi, I'm having a brain blip - in a query I would like to test if the value from one field (example: Field 1: "ANN" Field 2: "ANN SMITH") is contained in another field of the same table. If it weren't a field I would use LIKE. Thanks in advance. Bonnie OOPS - don't want to waste anyone's time - I figured it out. I made a calculated field in the query that is the result of INSTR(table.field1,table.field2) with the criteria >0 and it works fine. Thanks. "Bonnie" wrote: > Hi, > > I'm having a brain blip - in a query I wou...

Tests?
Why do so many individuals need to jump on someone who posts a test? A higher percentage than Ivory soap put a clear indicator in the Subject line it's a test message. Seems the least educated of us all could identify this to make the decision to go right on by, it's a test. But what happens is a deluge of messages are sent to criticize the individual who is trying to solve a problem. And many of the criticisms are "not on topic" (like the ng they're in has "and criticism" as part of the ng title. But what is allowed is the use of vomit vocabulary! Certainly...

How to flush values in editable CListCtrl
I have two checkboxes. When I check the first one, a page containing an editable CListCtrl will be loaded. After I modify some value there, I check the second checkbox which will load second page also containing an editable CListCtrl. Before I moved from the first page to second page, I modified some values in its CListCtrl and I don't have a choice to click Apply. When I came back to first page, the modified values are gone. I would like to flush the values in the CListCtrl in the first page before the second page gets loaded. However I couldn't find a way to do that. Is there somet...

development/test server
Hi Can we development/test server with its own database ..and at the same time have a production server with its database which host the real data... Also is it possible to export customisation to the production server from development/test server..will there be any kind of conflicts i have to keep in mind... please help.. Thanks I setup a CRM test envionment using the Adventure Works Cycle database. This is where we made all customizations, which were somewhat extensive. When I exported the customizations from the test environment and imported them to our production server e...

Test
Sorry for wasting bandwidth here... think I'm having problems sending to the forums. We'll see if this gets through -- Merv Porter [SBS-MVP] ============================ ...

Meeting request returns NDR after a mailbox has been deleted
Hi, Can anyone help? We have a problem where if someone sends a meeting request to one particular person, the meeting requestor gets an NDR back from a user that was deleted some months back. The invited person still receives the request and can accept as usual. I've seen similar instances of this type of problem on this group and it has been suggested that the person being invited to the meeting has this other user (thats NDR'd) as a delegate and in this case, that was true but the delegate was removed (some time back) from Outlook, Toolls,options.delegates. I've checked the u...

test message
This is a test to see what email is registered with my account. Thanks, ------=_NextPart_0001_5C4BECA8 Content-Type: text/plain Content-Transfer-Encoding: 7bit Good morning Nick, Sorry for the delay, but I have been asked to have you try creating another new test thread. Thank you in advance. Todd Berger Microsoft Online Support Engineer Get Secure! - www.microsoft.com/security ============================================= When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================...

Now to unhide columns ... and test for upper/lower case
I've got two more questions ... Having the routine to open the rows, I need to apply that to opening the required columns. I assume that the columns are referred to numerically, rather than alphabetically? When I run the routine, the first part runs fine (opening rows), but the second part generates the error message : Run-time error '1004': Application-defined or object-defined error (This occurred with the two routines below combined as one) Seperating them ( as pasted here ) and running it on its' own gives the error message: 400 Any ...

Look up multiple values
I have a table where I want a value based on looking at data in two columns as follows; Table: A B C 1 Bore Pressure Length 2 100 760 80 3 100 1050 100 4 100 1200 100 5 125 670 80 6 125 830 100 I want to find a value in column C, based on an exact value from column A and when given a value for column B the value immediately above it. (e.g If i had a bore of 100 and pressure of 900, I would want the value of 100 from row 3) Hope this explains it, i have tried SUMPRODUCT in various f...