Use funtion to "jump" to worksheet

  • Thread starter Thread starter Tsjompie
  • Start date Start date
T

Tsjompie

I want a column with a list of values, exactly named like worksheets.
The second column need to be filled with a reference to one of the
worksheets, based on the name the sheets has (that value comes from the
first column).

Is there any way to make a reference to another worksheet based on a
variable name (= same as worksheet name)?

Thanx,

Tsjompie
 
The pages given shows me how to do it using VB code.

In fact, "the only thing" I want is to insert a value in a cell form another
worksheet, using a variable name to get to that sheet.
I want one main overall sheet (with in the A column all available worksheet
listed) and other sheets (exact duplicates getting an internal projectnumber
as name). The main sheets column B needs a listing from (e.g.) all A$1$
cells, information from all the projectsheets.

Because all sheets will have the same layout, I'm looking for a function
(within a cell, no vb) to get to the correct worksheet using a column with
values (using the worksheetnames).

Is this more clear?

Thanx
 
Hi
then try the following:
- column A: your sheet names
- cell B1:
=INDIRECT("" & A1 & "'!A1")
and copy this down
 
Hi
first: NEVER attach files to this newsgroup (most people won't open
them)
Second: I was in a very nice mood, downloaded the file and what should
I say: it does contain MACROS (or at least I got a macro warning)

-> I deleted this file (and shame on me I downloaded it at the
beginning)

So trying again to explain this formula:
1. Assumption: in cell A1 is the name of the sheet you want your data
from
2. Assumption: You want the value from cell X1 from this specified
sheetname

Use the formula
=INDIRECT("" & A1 & "'!X1")

So now you have to change the following according to your layout:
- A1: insert the cell reference on your summary sheet which contains
the sheetname
- X1: Replace this with the source cell reference from your desired
worksheets
 
Hi,
There is an even easier way: I wrote this formula:

=RIGHT(CELL("filename",Sheet1!$A$1),LEN(CELL("filename",Sheet1!$A$1))-SEARCHB("xls]",CELL("filename",Sheet1!$A$1))-3)

Regards,
H
 
Thanx!!

Got it working
Sorry for the file attachment, I tough that was the easiest way to explain.

Tsjompie
 
Back
Top