help!!! how to place value in seperate field when this field is true

  • Thread starter Thread starter dontwanna
  • Start date Start date
D

dontwanna

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(A9>50,"",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!A9>50,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
 
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.
 
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!!
 
One non-programming way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=INDEX(data1!G:G,MATCH(TRUE,(data1!G1:G65535<>worksheet2!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)
 
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.
 
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 said:
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 said:
One non-programming way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=INDEX(data1!G:G,MATCH(TRUE,(data1!G1:G65535<>worksheet2!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)
Click to expand...
Click to expand...
 
one way:

Dim myrng As Range
Dim myString As String

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



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 said:
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 said:
One non-programming way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=INDEX(data1!G:G,MATCH(TRUE,(data1!G1:G65535<>worksheet2!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)


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!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top