Help,

Pretty new to functions in excel. Here is the delimma, I have an
if-then statement in a cell, when the value is false i want the cell
that i am in to display nothing however i want it to place a value in
another worksheet. Ultimately I would like to have it run a macro or
some other code, but I would imagine that at the present time, that
may be a little advanced for me.

Thanks for your help,

Dontwanna

=IF(A950,"",A9*2) This places an empty space in the
cell and is treated as text

You can enter a formula directly into sheet 2 and
reference another sheet like this.

=IF(Sheet1!A950,0,Sheet1!A9*2) This places a zero in the
cell and is a number. If you do not want to see the zero
choose Tools, Option, View Tab and un check the Zero check
box

Rgs
Peter

-----Original Message-----
Help,

Pretty new to functions in excel. Here is the delimma, I
have an
if-then statement in a cell, when the value is false i
want the cell
that i am in to display nothing however i want it to
place a value in
another worksheet. Ultimately I would like to have it run
a macro or
some other code, but I would imagine that at the present
time, that
may be a little advanced for me.

Thanks for your help,

Dontwanna
.

Worksheet functions can only return values to their calling cells -
they can't call macros and they can't affect other cell's values.

One way to accomplish the first part of your request is:

In sheet1:

A1: =IF(<condition, TRUE, "")

then in Sheet2:

J10: =IF(Sheet1!A1="", 10, "")


which will return the value 10 to Sheet2!J10 if the condition in
Sheet1!A1 evaluates to False.

To run a macro, you'll need to use Event Macros. Do a Google search
of the archives:

http://google.com/advanced_group_search?q=group:*excel*

or post back if you want to go that route.


In article ,
(dontwanna) wrote:

Help,

Pretty new to functions in excel. Here is the delimma, I have an
if-then statement in a cell, when the value is false i want the cell
that i am in to display nothing however i want it to place a value in
another worksheet. Ultimately I would like to have it run a macro or
some other code, but I would imagine that at the present time, that
may be a little advanced for me.

Thanks for your help,

Dontwanna

Thanks for the help. I just have one more questions (hopefully)

I need to create a procedure that excel can call that will then take
in a string variable and return another string variable.

Here is what i need.

Say I pass in the string 'jones' from worksheet2 cell c2. I need the
procedure to loop through all of the cells in column g of a seperate
excel spreadsheet (say the name is data1) and once it reaches a cell
with a string other than 'jones' I need the procedure to exit and
return the value of the string that does not equal the string 'jones'.

Is this possible in Excel???

Thanks again in advanced for your help!!


(dontwanna) wrote in message . com...
Help,

Pretty new to functions in excel. Here is the delimma, I have an
if-then statement in a cell, when the value is false i want the cell
that i am in to display nothing however i want it to place a value in
another worksheet. Ultimately I would like to have it run a macro or
some other code, but I would imagine that at the present time, that
may be a little advanced for me.

Thanks for your help,

Dontwanna

One non-programming way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=INDEX(data1!G:G,MATCH(TRUE,(data1!G1:G65535<work sheet2!C2),0))

One programming way:

Public Function FirstNonMatch(sNonFind As String, rng As Range) _
As Variant
Dim cell As Range
Dim found As Boolean
For Each cell In rng
If Not IsEmpty(cell.Value) And (cell.Value < sNonFind) Then
found = True
Exit For
End If
Next cell
If Not found Then
FirstNonMatch = CVErr(xlErrNA)
Else
FirstNonMatch = cell.Text
End If
End Function


Call as

Dim myvar
myvar = FirstNonMatch(Sheets("worksheet2").Range("C2"), _
Sheets("data1").Range("G1:G" & Range("G" & _
Rows.Count).End(xlUp).Row))
If VarType(myvar) = vbError Then
MsgBox "Not found"
Else
MsgBox myvar
End If


or from the worksheet

=firstnonmatch(worksheet2!C2,G1:G52)


In article ,
(dontwanna) wrote:

I need to create a procedure that excel can call that will then take
in a string variable and return another string variable.

Here is what i need.

Say I pass in the string 'jones' from worksheet2 cell c2. I need the
procedure to loop through all of the cells in column g of a seperate
excel spreadsheet (say the name is data1) and once it reaches a cell
with a string other than 'jones' I need the procedure to exit and
return the value of the string that does not equal the string 'jones'.

Is this possible in Excel???

Thanks again in advanced for your help!!

Ok thanks for the excellent help, it really helped.

Now for an easy one

I have a cell (c3) that contains the absolute row and column of a cell (say
$e$9)
How can I pull the contents out of a cell in another excel file using the
contents of the cell above and place in the current cell that i am looking
at (g5).

Something like [spreadsheet.xls]sheet1=cell("contents",c3)

I know the above will not work but it might help explain the issue.

Thanks again for your help.

"J.E. McGimpsey" wrote in message
...
One non-programming way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=INDEX(data1!G:G,MATCH(TRUE,(data1!G1:G65535<work sheet2!C2),0))

One programming way:

Public Function FirstNonMatch(sNonFind As String, rng As Range) _
As Variant
Dim cell As Range
Dim found As Boolean
For Each cell In rng
If Not IsEmpty(cell.Value) And (cell.Value < sNonFind) Then
found = True
Exit For
End If
Next cell
If Not found Then
FirstNonMatch = CVErr(xlErrNA)
Else
FirstNonMatch = cell.Text
End If
End Function


Call as

Dim myvar
myvar = FirstNonMatch(Sheets("worksheet2").Range("C2"), _
Sheets("data1").Range("G1:G" & Range("G" & _
Rows.Count).End(xlUp).Row))
If VarType(myvar) = vbError Then
MsgBox "Not found"
Else
MsgBox myvar
End If


or from the worksheet

=firstnonmatch(worksheet2!C2,G1:G52)


In article ,
(dontwanna) wrote:

I need to create a procedure that excel can call that will then take
in a string variable and return another string variable.

Here is what i need.

Say I pass in the string 'jones' from worksheet2 cell c2. I need the
procedure to loop through all of the cells in column g of a seperate
excel spreadsheet (say the name is data1) and once it reaches a cell
with a string other than 'jones' I need the procedure to exit and
return the value of the string that does not equal the string 'jones'.

Is this possible in Excel???

Thanks again in advanced for your help!!

OK I was able to figure out the last problem just one more and i will
leave you people alone (at least for a little while)

Can you please tell me how to pull data from another row

ex

I pass a string and a range to a function

I then compare the string to the cells in the range.

say the string is in cell g4

say i want to retrieve the string that is in cell b4. how can i tell
it to pull from b4

(if string was in cell g8 then i would want to retrieve the string
from b8)

Thanks.

dontwanna

"Dontwanna" wrote in message ...
Ok thanks for the excellent help, it really helped.

Now for an easy one

I have a cell (c3) that contains the absolute row and column of a cell (say
$e$9)
How can I pull the contents out of a cell in another excel file using the
contents of the cell above and place in the current cell that i am looking
at (g5).

Something like [spreadsheet.xls]sheet1=cell("contents",c3)

I know the above will not work but it might help explain the issue.

Thanks again for your help.

"J.E. McGimpsey" wrote in message
...
One non-programming way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=INDEX(data1!G:G,MATCH(TRUE,(data1!G1:G65535<work sheet2!C2),0))

One programming way:

Public Function FirstNonMatch(sNonFind As String, rng As Range) _
As Variant
Dim cell As Range
Dim found As Boolean
For Each cell In rng
If Not IsEmpty(cell.Value) And (cell.Value < sNonFind) Then
found = True
Exit For
End If
Next cell
If Not found Then
FirstNonMatch = CVErr(xlErrNA)
Else
FirstNonMatch = cell.Text
End If
End Function


Call as

Dim myvar
myvar = FirstNonMatch(Sheets("worksheet2").Range("C2"), _
Sheets("data1").Range("G1:G" & Range("G" & _
Rows.Count).End(xlUp).Row))
If VarType(myvar) = vbError Then
MsgBox "Not found"
Else
MsgBox myvar
End If


or from the worksheet

=firstnonmatch(worksheet2!C2,G1:G52)


In article ,
(dontwanna) wrote:

I need to create a procedure that excel can call that will then take
in a string variable and return another string variable.

Here is what i need.

Say I pass in the string 'jones' from worksheet2 cell c2. I need the
procedure to loop through all of the cells in column g of a seperate
excel spreadsheet (say the name is data1) and once it reaches a cell
with a string other than 'jones' I need the procedure to exit and
return the value of the string that does not equal the string 'jones'.

Is this possible in Excel???

Thanks again in advanced for your help!!

one way:

Dim myrng As Range
Dim myString As String

Set myrng = Range("G4") 'or G8
myString = myrng.Offset(0, -5).Text



In article ,
(dontwanna) wrote:

OK I was able to figure out the last problem just one more and i will
leave you people alone (at least for a little while)

Can you please tell me how to pull data from another row

ex

I pass a string and a range to a function

I then compare the string to the cells in the range.

say the string is in cell g4

say i want to retrieve the string that is in cell b4. how can i tell
it to pull from b4

(if string was in cell g8 then i would want to retrieve the string
from b8)

Thanks.

dontwanna

"Dontwanna" wrote in message
...
Ok thanks for the excellent help, it really helped.

Now for an easy one

I have a cell (c3) that contains the absolute row and column of a cell
(say
$e$9)
How can I pull the contents out of a cell in another excel file using the
contents of the cell above and place in the current cell that i am looking
at (g5).

Something like [spreadsheet.xls]sheet1=cell("contents",c3)

I know the above will not work but it might help explain the issue.

Thanks again for your help.

"J.E. McGimpsey" wrote in message
...
One non-programming way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=INDEX(data1!G:G,MATCH(TRUE,(data1!G1:G65535<work sheet2!C2),0))

One programming way:

Public Function FirstNonMatch(sNonFind As String, rng As Range) _
As Variant
Dim cell As Range
Dim found As Boolean
For Each cell In rng
If Not IsEmpty(cell.Value) And (cell.Value < sNonFind) Then
found = True
Exit For
End If
Next cell
If Not found Then
FirstNonMatch = CVErr(xlErrNA)
Else
FirstNonMatch = cell.Text
End If
End Function


Call as

Dim myvar
myvar = FirstNonMatch(Sheets("worksheet2").Range("C2"), _
Sheets("data1").Range("G1:G" & Range("G" & _
Rows.Count).End(xlUp).Row))
If VarType(myvar) = vbError Then
MsgBox "Not found"
Else
MsgBox myvar
End If


or from the worksheet

=firstnonmatch(worksheet2!C2,G1:G52)


In article ,
(dontwanna) wrote:

I need to create a procedure that excel can call that will then take
in a string variable and return another string variable.

Here is what i need.

Say I pass in the string 'jones' from worksheet2 cell c2. I need the
procedure to loop through all of the cells in column g of a seperate
excel spreadsheet (say the name is data1) and once it reaches a cell
with a string other than 'jones' I need the procedure to exit and
return the value of the string that does not equal the string 'jones'.

Is this possible in Excel???

Thanks again in advanced for your help!!