I have 6 tabs namely Summary, First, 1,2,3 and Last. In the tabs 1 to
3, at A1 there are some numbers. In the Summary tab I want them to
appear in a single cell. And later using that cell I want them to
display separately over the summary tab itself. First and Last tabs are
just dummy tabs.

Yes it is. And I'm going to show you how to do it all the way you've asked,
then I'm going to ask a question and suggest a different method:

In A1 on Summary enter this formula
='1'!A1 & " " & '2'!A1 & " " & '3'!A1

those " " have a single space between them. So if you had 12, 23, and 34 on
those other sheets it would display as
12 23 34
but it would no longer be a number, it's been coerced into being text
To show the number from '1'!A1 separately on this sheet *as a number*, use
this:
=VALUE(LEFT(A1,FIND(" ",A1)-1))
that would get you the 12
for the second number in the group, this will work:
=VALUE(MID(A1,FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)))
that would return the 23
finally, for the 3rd group, this formula will work:
=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))
returning the 34 portion of the text in A1.

But now for the question - why go to all the trouble of pulling the numbers
out of the string you built up? Why not go back to the source for them just
like you did to build up the group in A1?

Anyplace you want '1'!A1 (12) to show up on your Summary sheet, just enter
'1'!A1 and it will appear as a number. Same for the other 2 values:
'2'!A1 will give you 23 and
'3'!A1 will give you 34

Try it: type this in cell D2 (or anywhere else) on the summary sheet:
='2'!A1
and you should see the value that's in cell A1 on sheet '2'

certainly easier than those long text function formulas. And makes no
difference to Excel at all as to where you got the number from.


"shail" wrote:

I have 6 tabs namely Summary, First, 1,2,3 and Last. In the tabs 1 to
3, at A1 there are some numbers. In the Summary tab I want them to
appear in a single cell. And later using that cell I want them to
display separately over the summary tab itself. First and Last tabs are
just dummy tabs.

Hi JLatham,

Thanks for your valuable time and suggestion. Yes, it is not a good
idea to have all the values from different cells in a single cell. But
I am looking it in this way only. The tabs between First and Last will
be dynamic.... means there will be any number of tabs between these two
sheets.

And that is why concatenation will not work here. I have to go to
summary tab again and again to change the formulae.

What i wanted to do is arrange the numbers in a single cell (might be
as an array), and then I will read them in different cells and do
something with it.

In order to do it, I have experimented it with lots of functions, but
sum seems to be the only function working as

=SUM(First:Last!A1)

I also have tried TRANSPOSE but this too is not working. Then I
thought I would name the range and try to pull the values through the
NAME. But here I don't know how to get the values from a NAME.


Thanks again for your help.

Shail


JLatham wrote:
Yes it is. And I'm going to show you how to do it all the way you've asked,
then I'm going to ask a question and suggest a different method:

In A1 on Summary enter this formula
='1'!A1 & " " & '2'!A1 & " " & '3'!A1

those " " have a single space between them. So if you had 12, 23, and 34 on
those other sheets it would display as
12 23 34
but it would no longer be a number, it's been coerced into being text
To show the number from '1'!A1 separately on this sheet *as a number*, use
this:
=VALUE(LEFT(A1,FIND(" ",A1)-1))
that would get you the 12
for the second number in the group, this will work:
=VALUE(MID(A1,FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)))
that would return the 23
finally, for the 3rd group, this formula will work:
=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))
returning the 34 portion of the text in A1.

But now for the question - why go to all the trouble of pulling the numbers
out of the string you built up? Why not go back to the source for them just
like you did to build up the group in A1?

Anyplace you want '1'!A1 (12) to show up on your Summary sheet, just enter
'1'!A1 and it will appear as a number. Same for the other 2 values:
'2'!A1 will give you 23 and
'3'!A1 will give you 34

Try it: type this in cell D2 (or anywhere else) on the summary sheet:
='2'!A1
and you should see the value that's in cell A1 on sheet '2'

certainly easier than those long text function formulas. And makes no
difference to Excel at all as to where you got the number from.


"shail" wrote:

I have 6 tabs namely Summary, First, 1,2,3 and Last. In the tabs 1 to
3, at A1 there are some numbers. In the Summary tab I want them to
appear in a single cell. And later using that cell I want them to
display separately over the summary tab itself. First and Last tabs are
just dummy tabs.

I didn't realize that the number of sheets between First and Last would be
variable. That puts a different twist on things. I'm thinking a little VBA
coding to create a UDF (User Defined Function) might just fit the bill here.
Let me think on it some and I'll get back with you on it.
For what I have in mind at the moment, a standard, distinct way of naming
the sheets between First and Last would be a start. Would they always be
given a tab name that is just a number? Or would they be any sheet NOT named
"Summary", "First" and/or "Last"?

"shail" wrote:

Hi JLatham,

Thanks for your valuable time and suggestion. Yes, it is not a good
idea to have all the values from different cells in a single cell. But
I am looking it in this way only. The tabs between First and Last will
be dynamic.... means there will be any number of tabs between these two
sheets.

And that is why concatenation will not work here. I have to go to
summary tab again and again to change the formulae.

What i wanted to do is arrange the numbers in a single cell (might be
as an array), and then I will read them in different cells and do
something with it.

In order to do it, I have experimented it with lots of functions, but
sum seems to be the only function working as

=SUM(First:Last!A1)

I also have tried TRANSPOSE but this too is not working. Then I
thought I would name the range and try to pull the values through the
NAME. But here I don't know how to get the values from a NAME.


Thanks again for your help.

Shail


JLatham wrote:
Yes it is. And I'm going to show you how to do it all the way you've asked,
then I'm going to ask a question and suggest a different method:

In A1 on Summary enter this formula
='1'!A1 & " " & '2'!A1 & " " & '3'!A1

those " " have a single space between them. So if you had 12, 23, and 34 on
those other sheets it would display as
12 23 34
but it would no longer be a number, it's been coerced into being text
To show the number from '1'!A1 separately on this sheet *as a number*, use
this:
=VALUE(LEFT(A1,FIND(" ",A1)-1))
that would get you the 12
for the second number in the group, this will work:
=VALUE(MID(A1,FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)))
that would return the 23
finally, for the 3rd group, this formula will work:
=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))
returning the 34 portion of the text in A1.

But now for the question - why go to all the trouble of pulling the numbers
out of the string you built up? Why not go back to the source for them just
like you did to build up the group in A1?

Anyplace you want '1'!A1 (12) to show up on your Summary sheet, just enter
'1'!A1 and it will appear as a number. Same for the other 2 values:
'2'!A1 will give you 23 and
'3'!A1 will give you 34

Try it: type this in cell D2 (or anywhere else) on the summary sheet:
='2'!A1
and you should see the value that's in cell A1 on sheet '2'

certainly easier than those long text function formulas. And makes no
difference to Excel at all as to where you got the number from.


"shail" wrote:

I have 6 tabs namely Summary, First, 1,2,3 and Last. In the tabs 1 to
3, at A1 there are some numbers. In the Summary tab I want them to
appear in a single cell. And later using that cell I want them to
display separately over the summary tab itself. First and Last tabs are
just dummy tabs.

Hi JLatham,

The sheet name could be anything, but they should be between the tabs
First and Last because when I use them between these two tabs, i can
add as many as tabs without changing the formula at the summary tab.
Say, for sum, I can use

=SUM(First:Last!A1)

I can add/remove the tabs between First and Last, and I don't have to
make any changes in the formula.

Thanks again for your valuable time.

Shail



JLatham wrote:
I didn't realize that the number of sheets between First and Last would be
variable. That puts a different twist on things. I'm thinking a little VBA
coding to create a UDF (User Defined Function) might just fit the bill here.
Let me think on it some and I'll get back with you on it.
For what I have in mind at the moment, a standard, distinct way of naming
the sheets between First and Last would be a start. Would they always be
given a tab name that is just a number? Or would they be any sheet NOT named
"Summary", "First" and/or "Last"?

"shail" wrote:

Hi JLatham,

Thanks for your valuable time and suggestion. Yes, it is not a good
idea to have all the values from different cells in a single cell. But
I am looking it in this way only. The tabs between First and Last will
be dynamic.... means there will be any number of tabs between these two
sheets.

And that is why concatenation will not work here. I have to go to
summary tab again and again to change the formulae.

What i wanted to do is arrange the numbers in a single cell (might be
as an array), and then I will read them in different cells and do
something with it.

In order to do it, I have experimented it with lots of functions, but
sum seems to be the only function working as

=SUM(First:Last!A1)

I also have tried TRANSPOSE but this too is not working. Then I
thought I would name the range and try to pull the values through the
NAME. But here I don't know how to get the values from a NAME.


Thanks again for your help.

Shail


JLatham wrote:
Yes it is. And I'm going to show you how to do it all the way you've asked,
then I'm going to ask a question and suggest a different method:

In A1 on Summary enter this formula
='1'!A1 & " " & '2'!A1 & " " & '3'!A1

those " " have a single space between them. So if you had 12, 23, and 34 on
those other sheets it would display as
12 23 34
but it would no longer be a number, it's been coerced into being text
To show the number from '1'!A1 separately on this sheet *as a number*, use
this:
=VALUE(LEFT(A1,FIND(" ",A1)-1))
that would get you the 12
for the second number in the group, this will work:
=VALUE(MID(A1,FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)))
that would return the 23
finally, for the 3rd group, this formula will work:
=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))
returning the 34 portion of the text in A1.

But now for the question - why go to all the trouble of pulling the numbers
out of the string you built up? Why not go back to the source for them just
like you did to build up the group in A1?

Anyplace you want '1'!A1 (12) to show up on your Summary sheet, just enter
'1'!A1 and it will appear as a number. Same for the other 2 values:
'2'!A1 will give you 23 and
'3'!A1 will give you 34

Try it: type this in cell D2 (or anywhere else) on the summary sheet:
='2'!A1
and you should see the value that's in cell A1 on sheet '2'

certainly easier than those long text function formulas. And makes no
difference to Excel at all as to where you got the number from.


"shail" wrote:

I have 6 tabs namely Summary, First, 1,2,3 and Last. In the tabs 1 to
3, at A1 there are some numbers. In the Summary tab I want them to
appear in a single cell. And later using that cell I want them to
display separately over the summary tab itself. First and Last tabs are
just dummy tabs.

Does that solve your original problem or not? I thought you needed to be
able to use the individual values from those sheets between First/Last
elsewhere individually on the Summary sheet?

"shail" wrote:

Hi JLatham,

The sheet name could be anything, but they should be between the tabs
First and Last because when I use them between these two tabs, i can
add as many as tabs without changing the formula at the summary tab.
Say, for sum, I can use

=SUM(First:Last!A1)

I can add/remove the tabs between First and Last, and I don't have to
make any changes in the formula.

Thanks again for your valuable time.

Shail



JLatham wrote:
I didn't realize that the number of sheets between First and Last would be
variable. That puts a different twist on things. I'm thinking a little VBA
coding to create a UDF (User Defined Function) might just fit the bill here.
Let me think on it some and I'll get back with you on it.
For what I have in mind at the moment, a standard, distinct way of naming
the sheets between First and Last would be a start. Would they always be
given a tab name that is just a number? Or would they be any sheet NOT named
"Summary", "First" and/or "Last"?

"shail" wrote:

Hi JLatham,

Thanks for your valuable time and suggestion. Yes, it is not a good
idea to have all the values from different cells in a single cell. But
I am looking it in this way only. The tabs between First and Last will
be dynamic.... means there will be any number of tabs between these two
sheets.

And that is why concatenation will not work here. I have to go to
summary tab again and again to change the formulae.

What i wanted to do is arrange the numbers in a single cell (might be
as an array), and then I will read them in different cells and do
something with it.

In order to do it, I have experimented it with lots of functions, but
sum seems to be the only function working as

=SUM(First:Last!A1)

I also have tried TRANSPOSE but this too is not working. Then I
thought I would name the range and try to pull the values through the
NAME. But here I don't know how to get the values from a NAME.


Thanks again for your help.

Shail


JLatham wrote:
Yes it is. And I'm going to show you how to do it all the way you've asked,
then I'm going to ask a question and suggest a different method:

In A1 on Summary enter this formula
='1'!A1 & " " & '2'!A1 & " " & '3'!A1

those " " have a single space between them. So if you had 12, 23, and 34 on
those other sheets it would display as
12 23 34
but it would no longer be a number, it's been coerced into being text
To show the number from '1'!A1 separately on this sheet *as a number*, use
this:
=VALUE(LEFT(A1,FIND(" ",A1)-1))
that would get you the 12
for the second number in the group, this will work:
=VALUE(MID(A1,FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)))
that would return the 23
finally, for the 3rd group, this formula will work:
=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))
returning the 34 portion of the text in A1.

But now for the question - why go to all the trouble of pulling the numbers
out of the string you built up? Why not go back to the source for them just
like you did to build up the group in A1?

Anyplace you want '1'!A1 (12) to show up on your Summary sheet, just enter
'1'!A1 and it will appear as a number. Same for the other 2 values:
'2'!A1 will give you 23 and
'3'!A1 will give you 34

Try it: type this in cell D2 (or anywhere else) on the summary sheet:
='2'!A1
and you should see the value that's in cell A1 on sheet '2'

certainly easier than those long text function formulas. And makes no
difference to Excel at all as to where you got the number from.


"shail" wrote:

I have 6 tabs namely Summary, First, 1,2,3 and Last. In the tabs 1 to
3, at A1 there are some numbers. In the Summary tab I want them to
appear in a single cell. And later using that cell I want them to
display separately over the summary tab itself. First and Last tabs are
just dummy tabs.