On Dec 20, 11:45 pm, Gord Dibben <gorddibbATshawDOTca wrote:
I am confused.

You have a workbook with a couple of sheets in it....right?

You are sending the workbook or one sheet to a user?

Which of the above applies?

Where are the 2006 and 2007 sheets? In one workbook or two?

You don't know what the user has named a sheet? Which sheet?

In cases where you don't know the name of the previous sheet you could use a
user defined function.

Function PrevSheet(rg As Range)
Application.Volatile
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Enter =PrevSheet(B2) on target sheet and you'll get B2 from sheet to
left(previous)

How-to install a UDF.

With your workbook open....................

Hit Alt + F11 to open the Visual Basic Editor. CTRL + r to open the Project
Explorer.

Select your workbook/project and InsertModule

Copy/paste the above UDF into that module.

Alt + q to return to Excel

Enter the formula as above in the target sheet.

Gord Dibben MS Excel MVP

On 20 Dec 2006 20:22:31 -0800, wrote:



wrote:
Gord Dibben wrote:
I may have misinterpreted but..............

In Sheet2 select A1 and enter =Sheet1!A1

Gord Dibben MS Excel MVP

On 17 Dec 2006 13:26:05 -0800, wrote:

How do I pick up info from a cell from the sheet to the next sheet at
the same location and in th esame workbook?

Thanks.

There is one more twist to this question.
Unfortunately, I am sending on esheet each to the user. For example, IN
2006 I created this workbook. Now I am sending the 2007 sheet. What can
I do to the 2007 sheet so the name and ID # filed is automatically
picked up from the 2006 sheet? I don't think that all users have given
the same name to 2006 sheet. So a macro that looks for the infor froma
particular location and the sheet that is in front of 2007?

Thanks


I am sorry If I confused you.

Here is the deal.

User has a workbook with one sheet for Attendance Calendar 2006(this is
the name of the sheet that everyone is using, I think).
Now I am sending Attendance calendar 2007.
I am asking them to copy it next to teh previous workbook after sheet
2006.
Now I want emp's name, ID # picked up from the 2006 calendar(the first
sheet) automatically.

Hope this clearifies.

Thanks.
I am askig each user copy 2007 calendar ands