Go to sheet based on column

[Tim M.]

How would I go to Sheet1, Sheet2 or Sheet3 in a database of names with
32 rows, 17 columns? If a name I find a match for is in Column B, go
to Sheet1, if a name is in Column C go to Sheet2, etc. (I need to
start from Column B) My lookup value will be a name, not a cell
reference. Thanks.

 

[d.winkler]

Not sure if I completely follow.
If the list of names is in an Excel worksheet and the matching results
in the cell being selected, then I'd set it up as follows:

Select Case ActiveCell.Column
Case 2 'column B
Sheets(1).Select
Case 3 'column C
Sheets(2).Select

(and so on...)

End Select

 

[Patrick Molloy]

depends on how the result is determined.
For instance, if the return is a cell then Cell.Column
could be used

Worksheets(Cell.Column-1).Activate

Otherwise if the result is simply the column number

Worksheets(FoundColumn-1).Activate

If its a letter (A,B...) then
Worksheets(ASC(ColLetter)-65).Activate

...."A" is ASCII 65 so B is 66 hence if B is passed
ASC("B") - 65 = 1


HTH
PAtrick Molloy
Microsoft Excel MVP

 

[Tim M.]

I'm going to have to find out how to do this by using ADDRESS, MATCH,
& INDEX I guess. I've got a headache trying to figure this out!!
Suppose I have a list of names from B24:R24. In A24 I have a State
name, Texas. In D:24 I have the state Arizona. I also have Arizona in
M:24. I want to do a match for Texas in A24, searching that row for
Arizona. If it finds Arizona in D24, (the third column from columnA, I
want to goto Sheet3 and see if cell C24 is greater than D24. Then I
want to do another Match on Row 24 (the Texas row) and find the second
"Arizona". It's in M24, the 12th column from ColumnA (Texas). Then, I
need to goto Sheet12 and see if C24 is greater than D24. I can do all
this with a lot of typing and a lot of "IF's", but I reuse this
program yearly, and the names of states are in different places year
to year. Maybe I'll have an answer this weekend, or figure it out.
I've searched by keywords in this forum and haven't found this
question answered yet. I was hoping to do it with a formula, but the
VBE may be what I need. I'll start trying it from that method. Thanks
for the time. Tim

 

[Tim M.]

I found a workaround that does well. I'd still like to know if this
can be done in a formula. Thx, Tim.