if statement help

  • Thread starter Thread starter johnb
  • Start date Start date
J

johnb

I have two columns with one of the two rows in the columns
containing text. Also, a column to the left that I want
to put a number in corresponding to the cell that has the
text in. Please help. Example below:

# col A col B
mon
tue
wed

I am trying to get the (#) column to return a value
corresponding to the cell: 1 for mon, 4 for tuesday, and 5
for wednesday. Is this possible. I am using excell 2000.
I am not sure if I can use an if statement or need
something else.


thanks, john
 
I would make a table called DayTable in which is listed the days of the
week, sorted alphabetically down one column and the corresponding number you
want for each day in the column immediately to the right of
it...........then assuming your days are in columns B and C and your "#"
column is A, I would put the following formula in cell A2 and copy it down
column A.......

=IF(LEN(C2)>0,VLOOKUP(C2,daytable,2,FALSE),IF(LEN(#REF!)>0,VLOOKUP(#REF!,day
table,2,FALSE),""))

Vaya con Dios,
Chuck, CABGx3
 
Hi John,

If you only have those 3 conditions to test for, then this
may be the easiest way to go:

=IF(B1="MON",1,IF(B1="TUE",4,IF(B1="WED",5,"")))

On the other hand, if you have many conditions to test for
then Chucks method is the way to go.

Biff
 
You can try this macro. My test assumed you wanted info in col F for info in
g:x

Sub findday()
On Error Resume Next
For Each c In [f1:f5]
x = Range(Cells(c.Row, "g"), Cells(c.Row, "x")).Find("*").Column
If UCase(Cells(c.Row, x)) = "MON" Then c.Value = 1
If UCase(Cells(c.Row, x)) = "TUES" Then c.Value = 2
If UCase(Cells(c.Row, x)) = "WED" Then c.Value = 3
If UCase(Cells(c.Row, x)) = "THUR" Then c.Value = 4
If UCase(Cells(c.Row, x)) = "FRI" Then c.Value = 5
Next
End Sub
 
I forgot to mention that the data in colums 1 and 2 change
each week. I edited the example below. The text in the
cells are just examples. It could be anything.
-----Original Message-----
You can try this macro. My test assumed you wanted info in col F for info in
g:x

Sub findday()
On Error Resume Next
For Each c In [f1:f5]
x = Range(Cells(c.Row, "g"), Cells(c.Row, "x")).Find ("*").Column
If UCase(Cells(c.Row, x)) = "MON" Then c.Value = 1
If UCase(Cells(c.Row, x)) = "TUES" Then c.Value = 2
If UCase(Cells(c.Row, x)) = "WED" Then c.Value = 3
If UCase(Cells(c.Row, x)) = "THUR" Then c.Value = 4
If UCase(Cells(c.Row, x)) = "FRI" Then c.Value = 5
Next
End Sub


I have two columns with one of the two rows in the columns
containing text. Also, a column to the left that I want
to put a number in corresponding to the cell that has the
text in. Please help. Example below:

# col A col B
1 cat
4 dog
5 frog

I am trying to get the (#) column to return a value
corresponding to the cell: 1 for cat, 4 for dog, and 5
for frog. Is this possible. I am using excell 2000.
I am not sure if I can use an if statement or need
something else.


thanks, john
Click to expand...


.
Click to expand...
 
This will find the column number

Sub findday_any()
On Error Resume Next
For Each c In [f1:f5]
c.Value = Range(Cells(c.Row, "g"), Cells(c.Row, "x")).Find("*").Column
Next
End Sub

I forgot to mention that the data in colums 1 and 2 change
each week. I edited the example below. The text in the
cells are just examples. It could be anything.
-----Original Message-----
You can try this macro. My test assumed you wanted info in col F for info in
g:x

Sub findday()
On Error Resume Next
For Each c In [f1:f5]
x = Range(Cells(c.Row, "g"), Cells(c.Row, "x")).Find ("*").Column
If UCase(Cells(c.Row, x)) = "MON" Then c.Value = 1
If UCase(Cells(c.Row, x)) = "TUES" Then c.Value = 2
If UCase(Cells(c.Row, x)) = "WED" Then c.Value = 3
If UCase(Cells(c.Row, x)) = "THUR" Then c.Value = 4
If UCase(Cells(c.Row, x)) = "FRI" Then c.Value = 5
Next
End Sub


I have two columns with one of the two rows in the columns
containing text. Also, a column to the left that I want
to put a number in corresponding to the cell that has the
text in. Please help. Example below:

# col A col B
1 cat
4 dog
5 frog

I am trying to get the (#) column to return a value
corresponding to the cell: 1 for cat, 4 for dog, and 5
for frog. Is this possible. I am using excell 2000.
I am not sure if I can use an if statement or need
something else.


thanks, john
Click to expand...


.
Click to expand...
Click to expand...
 
I have two columns with one of the two rows in the columns
containing text. Also, a column to the left that I want
to put a number in corresponding to the cell that has the
text in. Please help. Example below:

# col A col B
mon
tue
wed

I am trying to get the (#) column to return a value
corresponding to the cell: 1 for mon, 4 for tuesday, and 5
for wednesday. Is this possible. I am using excell 2000.
I am not sure if I can use an if statement or need
something else.


thanks, john
Click to expand...

This custom function (UDF) will return the location of "fnd" in table "tbl" the
way I think you have things laid out.

It will return 0 if fnd is non-existent and a #VALUE! error if fnd is not in
tbl.

To use this, <alt><F11> opens the VB editor.

Make sure your current project is highligted in the project explorer, then
Insert/Module and paste the code below into the window that opens.

To use this, enter =LocInTbl(fnd,tbl) in some cell. fnd and tbl can be cell
references.

===============================
Option Explicit

Function LocInTbl(fnd As Variant, tbl As Range) As Long
Dim c As Range
Dim l As Long

If Len(fnd) = 0 Then Exit Function
For Each c In tbl
l = l + 1
If c = fnd Then Exit For
Next c

LocInTbl = -l * (c = fnd)

End Function
=========================




--ron
 
In A1 put in this formula, then drag fill down column A

=IF(OR(B1<>"",C1<>""),IF(B1<>0,(ROW()-1)*2+1,(ROW()-1)*2+2),"")

--
Regards,
Tom Ogilvy


I forgot to mention that the data in colums 1 and 2 change
each week. I edited the example below. The text in the
cells are just examples. It could be anything.
-----Original Message-----
You can try this macro. My test assumed you wanted info in col F for info in
g:x

Sub findday()
On Error Resume Next
For Each c In [f1:f5]
x = Range(Cells(c.Row, "g"), Cells(c.Row, "x")).Find ("*").Column
If UCase(Cells(c.Row, x)) = "MON" Then c.Value = 1
If UCase(Cells(c.Row, x)) = "TUES" Then c.Value = 2
If UCase(Cells(c.Row, x)) = "WED" Then c.Value = 3
If UCase(Cells(c.Row, x)) = "THUR" Then c.Value = 4
If UCase(Cells(c.Row, x)) = "FRI" Then c.Value = 5
Next
End Sub


I have two columns with one of the two rows in the columns
containing text. Also, a column to the left that I want
to put a number in corresponding to the cell that has the
text in. Please help. Example below:

# col A col B
1 cat
4 dog
5 frog

I am trying to get the (#) column to return a value
corresponding to the cell: 1 for cat, 4 for dog, and 5
for frog. Is this possible. I am using excell 2000.
I am not sure if I can use an if statement or need
something else.


thanks, john
Click to expand...


.
Click to expand...
Click to expand...
 
That almost is it Tom.

It is returning the actuall count of the cell. For
instance:- the formula is returning the values that i
typed in in col c. My actuall spreadsheet is as follows:

col c col d col e
row 4 8 jones
row 5 9 smith
row 6 12 green

I changed the colums and rows in your formula to match
mine. I still neet the result "col c" to return a value
of 2 in row "4" according to the sample above. I also have
multiple instances of this in the same spreadsheet Which
puts it in different columns and rows. ex - col I, J, K
rows 18 through 30. In witch I need the same result of 1
or 2, 3 or 4, etc.

Thanks again for your help.



-----Original Message-----
In A1 put in this formula, then drag fill down column A

=IF(OR(B1<>"",C1<>""),IF(B1<>0,(ROW()-1)*2+1,(ROW()-1) *2+2),"")

--
Regards,
Tom Ogilvy


I forgot to mention that the data in colums 1 and 2 change
each week. I edited the example below. The text in the
cells are just examples. It could be anything.
-----Original Message-----
You can try this macro. My test assumed you wanted info in col F for info in
g:x

Sub findday()
On Error Resume Next
For Each c In [f1:f5]
x = Range(Cells(c.Row, "g"), Cells(c.Row, "x")).Find ("*").Column
If UCase(Cells(c.Row, x)) = "MON" Then c.Value = 1
If UCase(Cells(c.Row, x)) = "TUES" Then c.Value = 2
If UCase(Cells(c.Row, x)) = "WED" Then c.Value = 3
If UCase(Cells(c.Row, x)) = "THUR" Then c.Value = 4
If UCase(Cells(c.Row, x)) = "FRI" Then c.Value = 5
Next
End Sub


I have two columns with one of the two rows in the columns
containing text. Also, a column to the left that I want
to put a number in corresponding to the cell that has the
text in. Please help. Example below:

# col A col B
1 cat
4 dog
5 frog

I am trying to get the (#) column to return a value
corresponding to the cell: 1 for cat, 4 for dog, and 5
for frog. Is this possible. I am using excell 2000.
I am not sure if I can use an if statement or need
something else.


thanks, john


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top