Acessing values from another file and worksheet in Excel

[sdiverdan]

I'm writing a simple VBA program based on Excel which is no longer simple. Simply put, I have a workbook containing worksheets of people's names and their bowling scores. I have another Excel workbook which includes the list of bowlers and their average and handicap which is the last line of their individual worksheet. Accessing the value is no problem if I hard code it as:

=CELL("contents",'[Bowlers.xls]First Lastname'!$D$3)

The problem is that the name is in the first column so I wanted to dynamically pick it up as "[Bowlers.xls]T(A1)".Then it would be a simple matter of copying the formula down for all the bowlers. I'm stuck. How do you dynamically specify worksheet names in Excel.
I might be able to do this using VBA, however, I would have to run this program once everytime the workbook is opened. I don't even know how to do that now.


Dan

 

[jdhilljr]

The best way I have found to do this is to create the page in the workbook with the data. Then right click on the tab for that page and move move the page to the workbook where you want it. The links will stay connected.

Jim

 

[sdiverdan]

Thanks Jim, I need a dynamic method as the list of people change and I re-sort the list after additions etc.. so a formula that extracts the values I require for the corresponding worksheet is necessary.

Dan

 

[jdhilljr]

The links will update every time you open the workbook. If you use a VLOOKUP it will change to the new information.

Jim

 

[jdhilljr]

Try this.

http://www.procollectortips.com/QandA.xls

This is a spreadsheet on the net that uses VLOOKUP.

When you open it save it on your desktop. Then open the file and right click on the tab labeled Questions. Right click select "move or copy" click the dropdown to Book and select (new book) then save this book anywhere on the server as long as there is a link to the original spreadsheet. Then change one of the questions on sheet1 in the original workbook. Reopen the new book you created and the data will be updated.

Jim

 

[sdiverdan]

Jim,
That's not quite the answer I was looking for, or I'm still not getting it. VLOOKUP will find the information I want in a row by searching on a column. What I need is a formula to get a worksheet.

I'll try to explain it again. Two files(workbooks) 'A' has the summary sheets & macros , 'B' has the details with worksheets matching a name field in the 'A' workbook:

WorkBook
A B
column 1 list of names Worksheets matching names
column 2 values from 'B' worksheet contain value

A - first column has people's names, 2nd column a value
B - contains the value to be added to 'A' on a worksheet called the person's name.

Question: How does the value get on the 2nd column of 'A' using the name in the first column and retrieving the value from the person's spreadsheet in 'B'?

Even using VLOOKUP how do I specify a dynamic worksheet name?
Hope that explains it. %-


Dan