Creating automatic listings
 

Can I create a list on the first worksheet in a workbook to include a
particular cell content in each of of the subsequent worksheets and will
automatically include new sheets as they are added to the workbook? Sort of
like an index for the workbook. Thanks for any help.

Creating automatic listings
 

One way using INDIRECT ..

Assuming the target sheetnames are/will be listed down within A2:A20 (say)
and the target cell references (e.g: A1, B3, D5, K20, whatever)
are/will be listed across in B1, C1, D1, ...

Put in B2:
=IF(OR($A2="",B$1=""),"",INDIRECT("'"&$A2&"'!"&B$1 ))
Copy down to B20, fill across as far as required

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff H" wrote:
Can I create a list on the first worksheet in a workbook to include a
particular cell content in each of of the subsequent worksheets and will
automatically include new sheets as they are added to the workbook? Sort of
like an index for the workbook. Thanks for any help.

Creating automatic listings
 

If we have in A2: Sheet2, in A3: Sheet3 (say)
and in B1: A1, in C1: K10 (say)

then
B2 will return the contents of: Sheet2's A1
B3 returns: Sheet3's A1
C2 returns: Sheet2's K10
C3 returns: Sheet3's K10
and so on

Empty target cells (if any) will be returned as zeros
but we can suppress the display of extraneous zeros in the sheet via:
Clicking Tools Options View tab Uncheck "Zero values" OK

Note that the sheetnames entered in A2:A20
must match exactly* what's on the sheet tabs
*except for case
(watch out for inconsistencies, typos, extra spaces, etc)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Creating automatic listings
 

Thanks for the quick response. I think what your suggesting is what I'm
looking for. However, the formula in B2 returns #REF. Also, when you say
fill in across as desired, do you mean Copy across? Many Thanks

"Max" wrote:

One way using INDIRECT ..

Assuming the target sheetnames are/will be listed down within A2:A20 (say)
and the target cell references (e.g: A1, B3, D5, K20, whatever)
are/will be listed across in B1, C1, D1, ...

Put in B2:
=IF(OR($A2="",B$1=""),"",INDIRECT("'"&$A2&"'!"&B$1 ))
Copy down to B20, fill across as far as required

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff H" wrote:
Can I create a list on the first worksheet in a workbook to include a
particular cell content in each of of the subsequent worksheets and will
automatically include new sheets as they are added to the workbook? Sort of
like an index for the workbook. Thanks for any help.

Creating automatic listings
 

"Jeff H" wrote:
Thanks for the quick response. I think what your suggesting is what I'm
looking for. However, the formula in B2 returns #REF. Also, when you say
fill in across as desired, do you mean Copy across? Many Thanks

.. the formula in B2 returns #REF

As per my clarification follow up post, pl re-check that the sheetname
entered in A2 matches exactly what's on the sheet tab. And, of course, the
cell ref entered in B1 is a valid cell reference. If either A2 or B1 is out,
INDIRECT will return #REF!

.. fill in across as desired, do you mean Copy across?

guess you were referring to my line <g:
Copy down to B20, fill across as far as required

Yes, fill across means copy across. We put/enter the suggested formula into
B2 first, then copy B2 down to B20. Then with B2:B20 selected, we copy across
as many cols as we have cell references labelled in the 1st row (in B1, C1,
D1, etc)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Creating automatic listings
 

Excellent! Just what I need. Now I think I can name the referenced cells
across the top and they will be my column headers. Thanks for your help.

"Max" wrote:

"Jeff H" wrote:
Thanks for the quick response. I think what your suggesting is what I'm
looking for. However, the formula in B2 returns #REF. Also, when you say
fill in across as desired, do you mean Copy across? Many Thanks

.. the formula in B2 returns #REF

As per my clarification follow up post, pl re-check that the sheetname
entered in A2 matches exactly what's on the sheet tab. And, of course, the
cell ref entered in B1 is a valid cell reference. If either A2 or B1 is out,
INDIRECT will return #REF!

.. fill in across as desired, do you mean Copy across?

guess you were referring to my line <g:
Copy down to B20, fill across as far as required

Yes, fill across means copy across. We put/enter the suggested formula into
B2 first, then copy B2 down to B20. Then with B2:B20 selected, we copy across
as many cols as we have cell references labelled in the 1st row (in B1, C1,
D1, etc)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Creating automatic listings
 

"Jeff H" wrote:
Excellent! Just what I need. Now I think I can name the referenced cells
across the top and they will be my column headers. Thanks for your help.

Glad it worked out fine for you !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---