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.

Hi again JLatham,

No it didn't solved my problem. I named the range as "myList" and could
be able to do

=SUM(MyList)

=LARGE(MyList,2)

etc. but I want to read what is in "myList" from my summary sheet.

could this be possible?

Thanks again


Shail



JLatham wrote:
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.

I don't know of a way to extract the individual components with worksheet
functions. I'm looking at ways to do it with two user Defined Functions.

I imagine that you'll be wanting to put information from a given sheet
between First/Last in a particular position on the summary sheet, and until I
can guarantee accurate results, I don't want to get hopes up.

"shail" wrote:

Hi again JLatham,

No it didn't solved my problem. I named the range as "myList" and could
be able to do

=SUM(MyList)

=LARGE(MyList,2)

etc. but I want to read what is in "myList" from my summary sheet.

could this be possible?

Thanks again


Shail



JLatham wrote:
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.

I think I have it. Created 2 UDFs to be used.
One, GatherValues(), pulls the values from A1 on sheets between First and
Last sheets with a space between each value found. If A1 on a sheet was
empty, it returns 0 for that sheet to hold a place in the list of values
returned.

Then, so you can pull the individual pieces out of that mess and place them
around on the Summary sheet I wrote ExtractValue(Cell,WhichSheet) which is
currently set up to return a numeric value. Cell is the cell to get the list
of numbers from, and WhichSheet is the position number of the sheet that you
want info from relative to First. That is, if you wanted value in A1 on 1st
sheet after First, use 1, if you want value for 4th sheet after First, use 4.
Negative or 0 used for this will return 0, and if you ask for 10 and there
are only 9, then it will return 0.

Code segments:
Public Function GatherValues() As String
Dim anySheet As Worksheet
Dim FoundFirstFlag As Boolean

Application.Volatile
For Each anySheet In ThisWorkbook.Worksheets
'find sheet named "First"
If UCase(Trim(anySheet.Name)) = "LAST" Then
Exit Function 'all done
End If
If FoundFirstFlag Then
'prevent creating circular reference
'if Summary sheet got stuck between
'First and Last sheets
If UCase(Trim(anySheet.Name)) < "SUMMARY" Then
If IsEmpty(anySheet.Range("A1")) Then
GatherValues = GatherValues & " 0"
Else
GatherValues = GatherValues & " " & anySheet.Range("A1")
End If
End If
End If
If UCase(Trim(anySheet.Name)) = "FIRST" Then
'keep working thru sheets, but now
'we save values in A1 on any sheet until
'sheet "Last" is encountered earlier
'once found, flag stays set and
'If ahead of this will be catching
'A1 value on sheets until sheet Last is found
FoundFirstFlag = True
End If
Next
End Function

Public Function ExtractValue(SourceData As Range, WhichGroup As Integer)
Dim RawData As String
Dim NumberOfGroups As Integer
Dim GroupToReturn As Integer
Dim LoopCounter As Integer
Dim EndBlankPosition As Integer
'will be one space for each group

ExtractValue = 0 ' set default for invalid conditions
If Len(SourceData.Text) = 0 Or WhichGroup < 1 Then
Exit Function ' returns 0
Else
RawData = SourceData.Text
End If
For LoopCounter = 1 To Len(RawData)
If Mid(RawData, LoopCounter, 1) = " " Then
NumberOfGroups = NumberOfGroups + 1
End If
Next
If NumberOfGroups < WhichGroup Then
Exit Function ' returns 0
End If
Do Until GroupToReturn = WhichGroup
For LoopCounter = 1 To Len(RawData)
If Mid(RawData, LoopCounter, 1) = " " Then
GroupToReturn = GroupToReturn + 1
If GroupToReturn = WhichGroup Then
Exit Do
End If
End If
Next
Loop
'GroupToReturn points to the space in front of group we need
'if not last group, then we get everything from here to next
'space, otherwise we get what's left of the SourceData
EndBlankPosition = InStr(LoopCounter + 1, RawData, " ")
If EndBlankPosition = 0 Then
'last group
ExtractValue = Right(RawData, Len(RawData) - LoopCounter)
Else
ExtractValue = Mid(RawData, LoopCounter + 1, _
EndBlankPosition - LoopCounter)
End If
'if you want to return numeric info vs text as it is now then
ExtractValue = Val(ExtractValue)

End Function

Working demo of this at:
http://www.jlathamsite.com/uploads/for_shail.xls


"shail" wrote:

Hi again JLatham,

No it didn't solved my problem. I named the range as "myList" and could
be able to do

=SUM(MyList)

=LARGE(MyList,2)

etc. but I want to read what is in "myList" from my summary sheet.

could this be possible?

Thanks again


Shail



JLatham wrote:
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.

Hi again JLatham,

Thanks a lot for the UDFs, this must work for me now.


Thanks again

Shail


JLatham wrote:
I think I have it. Created 2 UDFs to be used.
One, GatherValues(), pulls the values from A1 on sheets between First and
Last sheets with a space between each value found. If A1 on a sheet was
empty, it returns 0 for that sheet to hold a place in the list of values
returned.

Then, so you can pull the individual pieces out of that mess and place them
around on the Summary sheet I wrote ExtractValue(Cell,WhichSheet) which is
currently set up to return a numeric value. Cell is the cell to get the list
of numbers from, and WhichSheet is the position number of the sheet that you
want info from relative to First. That is, if you wanted value in A1 on 1st
sheet after First, use 1, if you want value for 4th sheet after First, use 4.
Negative or 0 used for this will return 0, and if you ask for 10 and there
are only 9, then it will return 0.

Code segments:
Public Function GatherValues() As String
Dim anySheet As Worksheet
Dim FoundFirstFlag As Boolean

Application.Volatile
For Each anySheet In ThisWorkbook.Worksheets
'find sheet named "First"
If UCase(Trim(anySheet.Name)) = "LAST" Then
Exit Function 'all done
End If
If FoundFirstFlag Then
'prevent creating circular reference
'if Summary sheet got stuck between
'First and Last sheets
If UCase(Trim(anySheet.Name)) < "SUMMARY" Then
If IsEmpty(anySheet.Range("A1")) Then
GatherValues = GatherValues & " 0"
Else
GatherValues = GatherValues & " " & anySheet.Range("A1")
End If
End If
End If
If UCase(Trim(anySheet.Name)) = "FIRST" Then
'keep working thru sheets, but now
'we save values in A1 on any sheet until
'sheet "Last" is encountered earlier
'once found, flag stays set and
'If ahead of this will be catching
'A1 value on sheets until sheet Last is found
FoundFirstFlag = True
End If
Next
End Function

Public Function ExtractValue(SourceData As Range, WhichGroup As Integer)
Dim RawData As String
Dim NumberOfGroups As Integer
Dim GroupToReturn As Integer
Dim LoopCounter As Integer
Dim EndBlankPosition As Integer
'will be one space for each group

ExtractValue = 0 ' set default for invalid conditions
If Len(SourceData.Text) = 0 Or WhichGroup < 1 Then
Exit Function ' returns 0
Else
RawData = SourceData.Text
End If
For LoopCounter = 1 To Len(RawData)
If Mid(RawData, LoopCounter, 1) = " " Then
NumberOfGroups = NumberOfGroups + 1
End If
Next
If NumberOfGroups < WhichGroup Then
Exit Function ' returns 0
End If
Do Until GroupToReturn = WhichGroup
For LoopCounter = 1 To Len(RawData)
If Mid(RawData, LoopCounter, 1) = " " Then
GroupToReturn = GroupToReturn + 1
If GroupToReturn = WhichGroup Then
Exit Do
End If
End If
Next
Loop
'GroupToReturn points to the space in front of group we need
'if not last group, then we get everything from here to next
'space, otherwise we get what's left of the SourceData
EndBlankPosition = InStr(LoopCounter + 1, RawData, " ")
If EndBlankPosition = 0 Then
'last group
ExtractValue = Right(RawData, Len(RawData) - LoopCounter)
Else
ExtractValue = Mid(RawData, LoopCounter + 1, _
EndBlankPosition - LoopCounter)
End If
'if you want to return numeric info vs text as it is now then
ExtractValue = Val(ExtractValue)

End Function

Working demo of this at:
http://www.jlathamsite.com/uploads/for_shail.xls


"shail" wrote:

Hi again JLatham,

No it didn't solved my problem. I named the range as "myList" and could
be able to do

=SUM(MyList)

=LARGE(MyList,2)

etc. but I want to read what is in "myList" from my summary sheet.

could this be possible?

Thanks again


Shail



JLatham wrote:
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.