I Need An Argument

[newdeas]

I'm trying to get information from the same colums located on multiple
(40)worksheets. I've adjusted the below formula many times using the =if, and
& or but I'm getting "value", "non bowler", or it's skipping some of cell
contents(see below):
=IF(Sheet1!C7="non bowler",'Sheet1 (2)'!C7,Sheet1!C7)

I'm stuck! Can anyone help me out with this one?[/QUOTE]

 

[newdeas]

I forgot to mention the colum range is c7:c46

 

[Wigi]

This is pretty incomprehensible, but if I can guess: try the INDIRECT function.

 

[newdeas]

I'll try it again;

I have forty worksheets where I'm putting the names of different bowlers
(location of bowlers are in c7:c46 on each sheet). If a bowler is absent I
use "non bowler".

I'm trying to find a formula that will look at all forty sheets and report
back the active bowlers by not including the "non bowlers" found on most of
the sheets.

 

[Otto Moehrbach]

I don't know what you mean by "report back". I'll just assume you want all
the active bowlers listed in Column A of a sheet named "Active Bowlers".
With 40 sheets and 39 names in each, your best bet is to go with VBA
programming. This little macro will do what you want. Note that your file
must have a sheet named "Active Bowlers" or change that name in the code to
match what you have. HTH Otto
Sub ListBowlers()
Dim ws As Worksheet
Dim c As Long
Dim rColA As Range
Sheets("Active Bowlers").Select
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Active Bowlers" Then
With ws
.Range("C7:C46").Copy
Range("A" &
Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
End If
Next ws
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = rColA.Count To 1 Step -1
If rColA(c).Value = "non bowlers" Or _
IsEmpty(rColA(c).Value) Then _
rColA(c).EntireRow.Delete
Next c
Application.ScreenUpdating = True
End Sub