I have a spreadsheet with 50 tabs - each one numbered 1 to 50, so that the
first tab is named '1', the second is named '2' and so on.

On a summary spreadsheet, I have formulas pulling the same cell (A44) from
each tab, so that:
the formula in cell A1 looks like this ='1'!A$44 and
the formula in cell B1 looks like this ='2'!A$44 and so on.

Is there a way to get Excel to "feed" the tab names to the formula without
the help of Visual Basic. For example, I would like to list the tab names in
the spreadsheet (1 to 50) and have a formula in the cells that would
incorporate the tab names.
Tab Formula
1 ='1'!A$44
2 ='2'!A$44
3 ='3'!A$44

Does that make sense? If so, is there any way to do this?

if you the digit 1 in cell A1, then B1 could be

=Indirect("'"&a1&"'!A$44")

copy it down

"sstexas" wrote:

> I have a spreadsheet with 50 tabs - each one numbered 1 to 50, so that the
> first tab is named '1', the second is named '2' and so on.
>
> On a summary spreadsheet, I have formulas pulling the same cell (A44) from
> each tab, so that:
> the formula in cell A1 looks like this ='1'!A$44 and
> the formula in cell B1 looks like this ='2'!A$44 and so on.
>
> Is there a way to get Excel to "feed" the tab names to the formula without
> the help of Visual Basic. For example, I would like to list the tab names in
> the spreadsheet (1 to 50) and have a formula in the cells that would
> incorporate the tab names.
> Tab Formula
> 1 ='1'!A$44
> 2 ='2'!A$44
> 3 ='3'!A$44
>
> Does that make sense? If so, is there any way to do this?

If you want a list enter this and copy down
=ROW(A1)&"!a44"
if you want to sum the cells
=sum(1:50!a44)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"sstexas" <(E-Mail Removed)> wrote in message
news:8809E879-1F20-4F12-876C-(E-Mail Removed)...
>I have a spreadsheet with 50 tabs - each one numbered 1 to 50, so that the
> first tab is named '1', the second is named '2' and so on.
>
> On a summary spreadsheet, I have formulas pulling the same cell (A44) from
> each tab, so that:
> the formula in cell A1 looks like this ='1'!A$44 and
> the formula in cell B1 looks like this ='2'!A$44 and so on.
>
> Is there a way to get Excel to "feed" the tab names to the formula without
> the help of Visual Basic. For example, I would like to list the tab names
> in
> the spreadsheet (1 to 50) and have a formula in the cells that would
> incorporate the tab names.
> Tab Formula
> 1 ='1'!A$44
> 2 ='2'!A$44
> 3 ='3'!A$44
>
> Does that make sense? If so, is there any way to do this?

You are awesome!!!
Thanks so much!

"Duke Carey" wrote:

> if you the digit 1 in cell A1, then B1 could be
>
> =Indirect("'"&a1&"'!A$44")
>
> copy it down
>
> "sstexas" wrote:
>
> > I have a spreadsheet with 50 tabs - each one numbered 1 to 50, so that the
> > first tab is named '1', the second is named '2' and so on.
> >
> > On a summary spreadsheet, I have formulas pulling the same cell (A44) from
> > each tab, so that:
> > the formula in cell A1 looks like this ='1'!A$44 and
> > the formula in cell B1 looks like this ='2'!A$44 and so on.
> >
> > Is there a way to get Excel to "feed" the tab names to the formula without
> > the help of Visual Basic. For example, I would like to list the tab names in
> > the spreadsheet (1 to 50) and have a formula in the cells that would
> > incorporate the tab names.
> > Tab Formula
> > 1 ='1'!A$44
> > 2 ='2'!A$44
> > 3 ='3'!A$44
> >
> > Does that make sense? If so, is there any way to do this?

I tried using the formula you listed below, but it didn't work. Maybe I
didn't understand how to use it?
However, Duke Carey's formula worked perfectly.

"Don Guillett" wrote:

> If you want a list enter this and copy down
> =ROW(A1)&"!a44"
> if you want to sum the cells
> =sum(1:50!a44)
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "sstexas" <(E-Mail Removed)> wrote in message
> news:8809E879-1F20-4F12-876C-(E-Mail Removed)...
> >I have a spreadsheet with 50 tabs - each one numbered 1 to 50, so that the
> > first tab is named '1', the second is named '2' and so on.
> >
> > On a summary spreadsheet, I have formulas pulling the same cell (A44) from
> > each tab, so that:
> > the formula in cell A1 looks like this ='1'!A$44 and
> > the formula in cell B1 looks like this ='2'!A$44 and so on.
> >
> > Is there a way to get Excel to "feed" the tab names to the formula without
> > the help of Visual Basic. For example, I would like to list the tab names
> > in
> > the spreadsheet (1 to 50) and have a formula in the cells that would
> > incorporate the tab names.
> > Tab Formula
> > 1 ='1'!A$44
> > 2 ='2'!A$44
> > 3 ='3'!A$44
> >
> > Does that make sense? If so, is there any way to do this?
>
>

His gave you the value using indirect. I assumed you only wanted a list....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"sstexas" <(E-Mail Removed)> wrote in message
news:2F3E3371-3A51-444B-9835-(E-Mail Removed)...
>I tried using the formula you listed below, but it didn't work. Maybe I
> didn't understand how to use it?
> However, Duke Carey's formula worked perfectly.
>
> "Don Guillett" wrote:
>
>> If you want a list enter this and copy down
>> =ROW(A1)&"!a44"
>> if you want to sum the cells
>> =sum(1:50!a44)
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "sstexas" <(E-Mail Removed)> wrote in message
>> news:8809E879-1F20-4F12-876C-(E-Mail Removed)...
>> >I have a spreadsheet with 50 tabs - each one numbered 1 to 50, so that
>> >the
>> > first tab is named '1', the second is named '2' and so on.
>> >
>> > On a summary spreadsheet, I have formulas pulling the same cell (A44)
>> > from
>> > each tab, so that:
>> > the formula in cell A1 looks like this ='1'!A$44 and
>> > the formula in cell B1 looks like this ='2'!A$44 and so on.
>> >
>> > Is there a way to get Excel to "feed" the tab names to the formula
>> > without
>> > the help of Visual Basic. For example, I would like to list the tab
>> > names
>> > in
>> > the spreadsheet (1 to 50) and have a formula in the cells that would
>> > incorporate the tab names.
>> > Tab Formula
>> > 1 ='1'!A$44
>> > 2 ='2'!A$44
>> > 3 ='3'!A$44
>> >
>> > Does that make sense? If so, is there any way to do this?
>>
>>