How do you use dynamic worksheet names in Excel 1

[sdiverdan]

I havee two files(workbooks) 'A' is a summary of another workbook 'B'. Workbook 'A' has a list of names and a associated value, 'B' has the details with worksheets matching a name field in the 'A' workbook. As follows:

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'?

I could use VLOOKUP, but how do I specify a dynamic worksheet name?
Hope that explains it.


Dan

 

[SkipVought]

Hi,

I'd tell you the exact syntax if I remembered. But there are some things that are not worth remembering, 'cuz it can all be do without a keystroke via point 'n' click.

With both workbooks open, activate the sheet where you want your experssion recorded.

Select the cell

Use the = toolbar icon (or type =) to begin your expression

To enter a cell or range reference from another workbook/sheet, activate that workbook/sheet, using the Window menu item and sheet tab. Then select the cell(s) of interest.

If that's all you need hit [Enter].

All the Workbook, worksheet, cell syntax has been created for you.

Ain't Excel wunderful!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sdiverdan]

Skip,
I was hoping for a dynamic way. The row the value sits on changes every week (ie. every update is a new row for every person's spreadsheet). I didn't want to do this manually for every person in my list every time there is an update(over 40 people! and an update weekly).
Do you know how to call a spreadsheet from a name in a cell?

Dan

 

[jdhilljr]

See if this give you any ideas

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

Jim

 

[SkipVought]

Well, when you use a lookup function, it does not matter that the value is in a different row. The advise I gave you is still valid.

Now if it's in a different column or on a different sheet or in a different workbook, you're up the creek.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sdiverdan]

Jim Thanks. Your example is great! This really helps I can't use a different workbook, but I can live with the worksheets being in one workbook.

thanks to you too Skip!

Dan

 

[kraptaki]

This is how I would do it:
1. Write down on a column, all the names of the worksheets you want to retrieve values from.
Example:
Col A Col B
1 sheetA
2 sheetB
3 sheetC
. .
. .
In another column, for example M, write in cell M1: a2 (as text) and fill downwards, so as that the result will be a3, a4 etc for subsequent cells.
In another cell, for example B30, write:
=$B$1&"!"&M1 and fill down. The result will be: sheetA!a2 (as text - not a reference)

In another column, for example Q, write in cell Q1:
=indirect(B30), fill down, and the result will be the contents of the cell a2 of sheetA
and so on.
Hope this helps!

 

[sdiverdan]

Thanks kraptaki, or in one line:
=indirect($B$1&"!"&M1)

works for me!

Dan