Data exctraction from multiple sheets
 

Hi,

I wanted know whether it is possible to extract data from multiple excel
sheets (which have a common format) to one single work sheet automatically?
What are the alternatives ways of doing the same?

Data exctraction from multiple sheets
 

in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27 when dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now this
becomes a problem bcs u have hundreds of sheets to manage..u remove some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a formula that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"wunder boy" wrote in message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27 when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now this
becomes a problem bcs u have hundreds of sheets to manage..u remove some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111
------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Hi,
thx for your reply. I just want to display the text. However When I try the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking values from
another sheet ie when i tried to enter a value from another sheet in "Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want to
consolidate the data.
3. I want to extract data from all these sheets to this master sheet
automatically (ie as i add or delete sheets the master list gets update
automatically).
4. ijust want the data to be displayed there is no need of computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a formula that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"wunder boy" wrote in message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27 when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now this
becomes a problem bcs u have hundreds of sheets to manage..u remove some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111
------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"wunder boy" wrote in message
...

Hi,
thx for your reply. I just want to display the text. However When I try the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking values
from
another sheet ie when i tried to enter a value from another sheet in "Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want to
consolidate the data.
3. I want to extract data from all these sheets to this master sheet
automatically (ie as i add or delete sheets the master list gets update
automatically).
4. ijust want the data to be displayed there is no need of computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"wunder boy" wrote in message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27 when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now this
becomes a problem bcs u have hundreds of sheets to manage..u remove some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111
------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have tried the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"wunder boy" wrote in message
...

Hi,
thx for your reply. I just want to display the text. However When I try the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking values
from
another sheet ie when i tried to enter a value from another sheet in "Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want to
consolidate the data.
3. I want to extract data from all these sheets to this master sheet
automatically (ie as i add or delete sheets the master list gets update
automatically).
4. ijust want the data to be displayed there is no need of computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"wunder boy" wrote in message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27 when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now this
becomes a problem bcs u have hundreds of sheets to manage..u remove some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111
------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wunder boy" wrote in message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have tried the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD

--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may benefit !

--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in message
...

Hi,
thx for your reply. I just want to display the text. However When I try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking values
from
another sheet ie when i tried to enter a value from another sheet in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want to
consolidate the data.
3. I want to extract data from all these sheets to this master sheet
automatically (ie as i add or delete sheets the master list gets update
automatically).
4. ijust want the data to be displayed there is no need of computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"wunder boy" wrote in message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27 when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now
this
becomes a problem bcs u have hundreds of sheets to manage..u remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111

------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wunder boy" wrote in message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have tried the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD

--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may benefit !

--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in message
...

Hi,
thx for your reply. I just want to display the text. However When I try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking values
from
another sheet ie when i tried to enter a value from another sheet in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want to
consolidate the data.
3. I want to extract data from all these sheets to this master sheet
automatically (ie as i add or delete sheets the master list gets update
automatically).
4. ijust want the data to be displayed there is no need of computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"wunder boy" wrote in message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27 when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now
this
becomes a problem bcs u have hundreds of sheets to manage..u remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111

------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Fine!
That means that the formula is working.

Type a number, say 100, in B1 (the next cell), and you should see the 100
displays in A1.

Now drag the formula down a few rows to copy it.
You should have 0's in those cells too.

Enter data in the Column B cells and you should see them displayed in Column
A.

NOW, the formula says look in Sheet1.
You're *in* Sheet1 now, so it's doing what it's supposed to do!

Just enter the *exact* same formula in Sheet2 (or whatever sheet you wish),
and you should see that same 100 displayed in whatever cell you entered it
into.

Drag down to copy as you did in Sheet1, and you should see the Sheet1 -
Column B data displayed in *this* sheet.

You now have your links between these sheets.

Is everything working now?

If it is, why didn't this happen the first time you used this same formula?


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wunder boy" wrote in message
...
hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wunder boy" wrote in message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have tried
the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD

--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may benefit !

--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in message
...

Hi,
thx for your reply. I just want to display the text. However When I
try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking
values
from
another sheet ie when i tried to enter a value from another sheet in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want to
consolidate the data.
3. I want to extract data from all these sheets to this master sheet
automatically (ie as i add or delete sheets the master list gets
update
automatically).
4. ijust want the data to be displayed there is no need of
computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"wunder boy" wrote in message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag
the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27
when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now
this
becomes a problem bcs u have hundreds of sheets to manage..u remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111

------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Hey,

got that.. actually i was modifying the sheet name and then trying it!
Thanks a lot that was really helpful.
can i modify the formula to use it for named sheets? aslo how can i make the
make same formula work while dragging it horizontally.

thx once again..
tols

"Ragdyer" wrote:

Fine!
That means that the formula is working.

Type a number, say 100, in B1 (the next cell), and you should see the 100
displays in A1.

Now drag the formula down a few rows to copy it.
You should have 0's in those cells too.

Enter data in the Column B cells and you should see them displayed in Column
A.

NOW, the formula says look in Sheet1.
You're *in* Sheet1 now, so it's doing what it's supposed to do!

Just enter the *exact* same formula in Sheet2 (or whatever sheet you wish),
and you should see that same 100 displayed in whatever cell you entered it
into.

Drag down to copy as you did in Sheet1, and you should see the Sheet1 -
Column B data displayed in *this* sheet.

You now have your links between these sheets.

Is everything working now?

If it is, why didn't this happen the first time you used this same formula?


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wunder boy" wrote in message
...
hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wunder boy" wrote in message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have tried
the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD

--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may benefit !

--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in message
...

Hi,
thx for your reply. I just want to display the text. However When I
try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking
values
from
another sheet ie when i tried to enter a value from another sheet in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want to
consolidate the data.
3. I want to extract data from all these sheets to this master sheet
automatically (ie as i add or delete sheets the master list gets
update
automatically).
4. ijust want the data to be displayed there is no need of
computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"wunder boy" wrote in message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag
the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27
when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27. Now
this
becomes a problem bcs u have hundreds of sheets to manage..u remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111

------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

To drag the *original* formula (using XL's default sheet names), along a
row, across columns, you could try this:

=INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")


To use *other* then the XL default sheet names,
Make a list of these names in an out-of-the-way location of your sheet, say
Column Z.

Make sure that the list in Column Z matches *exactly* with the names on the
sheet tabs,
Then try this formula to copy *down*:

=INDIRECT("'"&Z1&"'!b1")

And try this formula to copy *across*:

=INDIRECT("'"&INDEX($Z:$Z,COLUMNS($A:A))&"'!b1")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"wunder boy" wrote in message
...
Hey,

got that.. actually i was modifying the sheet name and then trying it!
Thanks a lot that was really helpful.
can i modify the formula to use it for named sheets? aslo how can i make the
make same formula work while dragging it horizontally.

thx once again..
tols

"Ragdyer" wrote:

Fine!
That means that the formula is working.

Type a number, say 100, in B1 (the next cell), and you should see the 100
displays in A1.

Now drag the formula down a few rows to copy it.
You should have 0's in those cells too.

Enter data in the Column B cells and you should see them displayed in
Column
A.

NOW, the formula says look in Sheet1.
You're *in* Sheet1 now, so it's doing what it's supposed to do!

Just enter the *exact* same formula in Sheet2 (or whatever sheet you
wish),
and you should see that same 100 displayed in whatever cell you entered it
into.

Drag down to copy as you did in Sheet1, and you should see the Sheet1 -
Column B data displayed in *this* sheet.

You now have your links between these sheets.

Is everything working now?

If it is, why didn't this happen the first time you used this same
formula?


--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"wunder boy" wrote in message
...
hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD


-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may benefit
!

-------------------------------------------------------------------------
--
"wunder boy" wrote in message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have tried
the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD


--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may benefit
!


--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in message
...

Hi,
thx for your reply. I just want to display the text. However When I
try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking
values
from
another sheet ie when i tried to enter a value from another sheet
in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want
to
consolidate the data.
3. I want to extract data from all these sheets to this master
sheet
automatically (ie as i add or delete sheets the master list gets
update
automatically).
4. ijust want the data to be displayed there is no need of
computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"wunder boy" wrote in
message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag
the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27
when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27.
Now
this
becomes a problem bcs u have hundreds of sheets to manage..u
remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111


------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Hey,

thx for that..... u r really good!!!

best wishes,
tols

"RagDyeR" wrote:

To drag the *original* formula (using XL's default sheet names), along a
row, across columns, you could try this:

=INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")


To use *other* then the XL default sheet names,
Make a list of these names in an out-of-the-way location of your sheet, say
Column Z.

Make sure that the list in Column Z matches *exactly* with the names on the
sheet tabs,
Then try this formula to copy *down*:

=INDIRECT("'"&Z1&"'!b1")

And try this formula to copy *across*:

=INDIRECT("'"&INDEX($Z:$Z,COLUMNS($A:A))&"'!b1")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"wunder boy" wrote in message
...
Hey,

got that.. actually i was modifying the sheet name and then trying it!
Thanks a lot that was really helpful.
can i modify the formula to use it for named sheets? aslo how can i make the
make same formula work while dragging it horizontally.

thx once again..
tols

"Ragdyer" wrote:

Fine!
That means that the formula is working.

Type a number, say 100, in B1 (the next cell), and you should see the 100
displays in A1.

Now drag the formula down a few rows to copy it.
You should have 0's in those cells too.

Enter data in the Column B cells and you should see them displayed in
Column
A.

NOW, the formula says look in Sheet1.
You're *in* Sheet1 now, so it's doing what it's supposed to do!

Just enter the *exact* same formula in Sheet2 (or whatever sheet you
wish),
and you should see that same 100 displayed in whatever cell you entered it
into.

Drag down to copy as you did in Sheet1, and you should see the Sheet1 -
Column B data displayed in *this* sheet.

You now have your links between these sheets.

Is everything working now?

If it is, why didn't this happen the first time you used this same
formula?


--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"wunder boy" wrote in message
...
hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD


-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may benefit
!

-------------------------------------------------------------------------
--
"wunder boy" wrote in message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have tried
the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD


--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may benefit
!


--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in message
...

Hi,
thx for your reply. I just want to display the text. However When I
try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking
values
from
another sheet ie when i tried to enter a value from another sheet
in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want
to
consolidate the data.
3. I want to extract data from all these sheets to this master
sheet
automatically (ie as i add or delete sheets the master list gets
update
automatically).
4. ijust want the data to be displayed there is no need of
computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"wunder boy" wrote in
message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u drag
the
formula the sheet numbers dont change eg : Sheet1!D27+Sheet2!D27
when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27.
Now
this
becomes a problem bcs u have hundreds of sheets to manage..u
remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111


------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

You're welcome, and thank you for the feed-back.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"wunder boy" wrote in message
...
Hey,

thx for that..... u r really good!!!

best wishes,
tols

"RagDyeR" wrote:

To drag the *original* formula (using XL's default sheet names), along a
row, across columns, you could try this:

=INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")


To use *other* then the XL default sheet names,
Make a list of these names in an out-of-the-way location of your sheet,
say
Column Z.

Make sure that the list in Column Z matches *exactly* with the names on
the
sheet tabs,
Then try this formula to copy *down*:

=INDIRECT("'"&Z1&"'!b1")

And try this formula to copy *across*:

=INDIRECT("'"&INDEX($Z:$Z,COLUMNS($A:A))&"'!b1")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"wunder boy" wrote in message
...
Hey,

got that.. actually i was modifying the sheet name and then trying it!
Thanks a lot that was really helpful.
can i modify the formula to use it for named sheets? aslo how can i make
the
make same formula work while dragging it horizontally.

thx once again..
tols

"Ragdyer" wrote:

Fine!
That means that the formula is working.

Type a number, say 100, in B1 (the next cell), and you should see the
100
displays in A1.

Now drag the formula down a few rows to copy it.
You should have 0's in those cells too.

Enter data in the Column B cells and you should see them displayed in
Column
A.

NOW, the formula says look in Sheet1.
You're *in* Sheet1 now, so it's doing what it's supposed to do!

Just enter the *exact* same formula in Sheet2 (or whatever sheet you
wish),
and you should see that same 100 displayed in whatever cell you entered
it
into.

Drag down to copy as you did in Sheet1, and you should see the Sheet1 -
Column B data displayed in *this* sheet.

You now have your links between these sheets.

Is everything working now?

If it is, why didn't this happen the first time you used this same
formula?


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"wunder boy" wrote in message
...
hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


-------------------------------------------------------------------------
--
"wunder boy" wrote in message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have
tried
the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD



--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may
benefit
!



--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in
message
...

Hi,
thx for your reply. I just want to display the text. However When
I
try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking
values
from
another sheet ie when i tried to enter a value from another sheet
in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want
to
consolidate the data.
3. I want to extract data from all these sheets to this master
sheet
automatically (ie as i add or delete sheets the master list gets
update
automatically).
4. ijust want the data to be displayed there is no need of
computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"wunder boy" wrote in
message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u
drag
the
formula the sheet numbers dont change eg :
Sheet1!D27+Sheet2!D27
when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27.
Now
this
becomes a problem bcs u have hundreds of sheets to manage..u
remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111


------------------------------------------------------------------------
robert111's Profile:

http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Hi,

I am back with a query regarding the default sheet names. Though you did
clarify it in your last post, I am facing some problems

1. First I created a column in z1 having all the excel sheet names.
2. Then I enter this formula =INDIRECT("'"&Z1&"'!b1"), but a ref error is
shown.
3. I try another method and enter the formula inside the orginal formula
like this
INDIRECT("sheet"&AM5&"ROWS($1:4)&""!E19")... again a ref error is showing.
can u pls help me out?
thx


"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"wunder boy" wrote in message
...
Hey,

thx for that..... u r really good!!!

best wishes,
tols

"RagDyeR" wrote:

To drag the *original* formula (using XL's default sheet names), along a
row, across columns, you could try this:

=INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")


To use *other* then the XL default sheet names,
Make a list of these names in an out-of-the-way location of your sheet,
say
Column Z.

Make sure that the list in Column Z matches *exactly* with the names on
the
sheet tabs,
Then try this formula to copy *down*:

=INDIRECT("'"&Z1&"'!b1")

And try this formula to copy *across*:

=INDIRECT("'"&INDEX($Z:$Z,COLUMNS($A:A))&"'!b1")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"wunder boy" wrote in message
...
Hey,

got that.. actually i was modifying the sheet name and then trying it!
Thanks a lot that was really helpful.
can i modify the formula to use it for named sheets? aslo how can i make
the
make same formula work while dragging it horizontally.

thx once again..
tols

"Ragdyer" wrote:

Fine!
That means that the formula is working.

Type a number, say 100, in B1 (the next cell), and you should see the
100
displays in A1.

Now drag the formula down a few rows to copy it.
You should have 0's in those cells too.

Enter data in the Column B cells and you should see them displayed in
Column
A.

NOW, the formula says look in Sheet1.
You're *in* Sheet1 now, so it's doing what it's supposed to do!

Just enter the *exact* same formula in Sheet2 (or whatever sheet you
wish),
and you should see that same 100 displayed in whatever cell you entered
it
into.

Drag down to copy as you did in Sheet1, and you should see the Sheet1 -
Column B data displayed in *this* sheet.

You now have your links between these sheets.

Is everything working now?

If it is, why didn't this happen the first time you used this same
formula?


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"wunder boy" wrote in message
...
hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


-------------------------------------------------------------------------
--
"wunder boy" wrote in message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have
tried
the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD



--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may
benefit
!



--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in
message
...

Hi,
thx for your reply. I just want to display the text. However When
I
try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not taking
values
from
another sheet ie when i tried to enter a value from another sheet
in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i want
to
consolidate the data.
3. I want to extract data from all these sheets to this master
sheet
automatically (ie as i add or delete sheets the master list gets
update
automatically).
4. ijust want the data to be displayed there is no need of
computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* ) with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"wunder boy" wrote in
message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u
drag
the
formula the sheet numbers dont change eg :
Sheet1!D27+Sheet2!D27
when
dragged
will change to Sheet1!D28+Sheet2!D28 or Sheet1!E27+Sheet2!E27.
Now
this
becomes a problem bcs u have hundreds of sheets to manage..u
remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111


------------------------------------------------------------------------
robert111's Profile:

http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

The #REF! error is probably because the name on the sheet tab and the name
in Column Z is *not exactly* the same.

Check spelling and also <spaces!
Sheet1
and
Sheet 1
are *not* equal.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wunder boy" wrote in message
...
Hi,

I am back with a query regarding the default sheet names. Though you did
clarify it in your last post, I am facing some problems

1. First I created a column in z1 having all the excel sheet names.
2. Then I enter this formula =INDIRECT("'"&Z1&"'!b1"), but a ref error is
shown.
3. I try another method and enter the formula inside the orginal formula
like this
INDIRECT("sheet"&AM5&"ROWS($1:4)&""!E19")... again a ref error is showing.
can u pls help me out?
thx


"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"wunder boy" wrote in message
...
Hey,

thx for that..... u r really good!!!

best wishes,
tols

"RagDyeR" wrote:

To drag the *original* formula (using XL's default sheet names), along
a
row, across columns, you could try this:

=INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")


To use *other* then the XL default sheet names,
Make a list of these names in an out-of-the-way location of your sheet,
say
Column Z.

Make sure that the list in Column Z matches *exactly* with the names on
the
sheet tabs,
Then try this formula to copy *down*:

=INDIRECT("'"&Z1&"'!b1")

And try this formula to copy *across*:

=INDIRECT("'"&INDEX($Z:$Z,COLUMNS($A:A))&"'!b1")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"wunder boy" wrote in message
...
Hey,

got that.. actually i was modifying the sheet name and then trying it!
Thanks a lot that was really helpful.
can i modify the formula to use it for named sheets? aslo how can i
make
the
make same formula work while dragging it horizontally.

thx once again..
tols

"Ragdyer" wrote:

Fine!
That means that the formula is working.

Type a number, say 100, in B1 (the next cell), and you should see the
100
displays in A1.

Now drag the formula down a few rows to copy it.
You should have 0's in those cells too.

Enter data in the Column B cells and you should see them displayed in
Column
A.

NOW, the formula says look in Sheet1.
You're *in* Sheet1 now, so it's doing what it's supposed to do!

Just enter the *exact* same formula in Sheet2 (or whatever sheet you
wish),
and you should see that same 100 displayed in whatever cell you
entered
it
into.

Drag down to copy as you did in Sheet1, and you should see the
Sheet1 -
Column B data displayed in *this* sheet.

You now have your links between these sheets.

Is everything working now?

If it is, why didn't this happen the first time you used this same
formula?


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-
"wunder boy" wrote in message
...
hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


-------------------------------------------------------------------------
--
"wunder boy" wrote in
message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have
tried
the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD



--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may
benefit
!



--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in
message
...

Hi,
thx for your reply. I just want to display the text. However
When
I
try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not
taking
values
from
another sheet ie when i tried to enter a value from another
sheet
in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and
R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i
want
to
consolidate the data.
3. I want to extract data from all these sheets to this master
sheet
automatically (ie as i add or delete sheets the master list
gets
update
automatically).
4. ijust want the data to be displayed there is no need of
computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each
sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* )
with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"wunder boy" wrote in
message
...
hi robert,

thx for your response.
I have tried that before but the problem with that is when u
drag
the
formula the sheet numbers dont change eg :
Sheet1!D27+Sheet2!D27
when
dragged
will change to Sheet1!D28+Sheet2!D28 or
Sheet1!E27+Sheet2!E27.
Now
this
becomes a problem bcs u have hundreds of sheets to manage..u
remove
some u
add some. so its kinda painful. Is there anyway out of this?

"robert111" wrote:


in cell A1 of your master sheet enter

=sheet1!A1 + sheet2!A1 + Sheet3!A1 etc etc


--
robert111


------------------------------------------------------------------------
robert111's Profile:

http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=564639

Data exctraction from multiple sheets
 

Hi Ragdyer
Hope you can help. Been through everything u gave to wunder boy and i am
still battling. I have various sheets, named as per staff names: David,
james, Kevin etc
I would like one summary sheet.
Firstly in column A1- A20, i would like it to read the staff name, as it is
on the sheet tab. Any formula?
Secondly column B1-B20: it needs to read the individual overtime for each
staff member, found in cell H10 on each of their sheets.
Other columns all need to read respective values from each individuals sheet.
I am messing up somewhere and just cannot get this to work without manually
changing all formuals to read of the sheet once I have changed the staff
name. can you help? Thanks
Ali


"Ragdyer" wrote:

The #REF! error is probably because the name on the sheet tab and the name
in Column Z is *not exactly* the same.

Check spelling and also <spaces!
Sheet1
and
Sheet 1
are *not* equal.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wunder boy" wrote in message
...
Hi,

I am back with a query regarding the default sheet names. Though you did
clarify it in your last post, I am facing some problems

1. First I created a column in z1 having all the excel sheet names.
2. Then I enter this formula =INDIRECT("'"&Z1&"'!b1"), but a ref error is
shown.
3. I try another method and enter the formula inside the orginal formula
like this
INDIRECT("sheet"&AM5&"ROWS($1:4)&""!E19")... again a ref error is showing.
can u pls help me out?
thx


"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"wunder boy" wrote in message
...
Hey,

thx for that..... u r really good!!!

best wishes,
tols

"RagDyeR" wrote:

To drag the *original* formula (using XL's default sheet names), along
a
row, across columns, you could try this:

=INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")


To use *other* then the XL default sheet names,
Make a list of these names in an out-of-the-way location of your sheet,
say
Column Z.

Make sure that the list in Column Z matches *exactly* with the names on
the
sheet tabs,
Then try this formula to copy *down*:

=INDIRECT("'"&Z1&"'!b1")

And try this formula to copy *across*:

=INDIRECT("'"&INDEX($Z:$Z,COLUMNS($A:A))&"'!b1")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"wunder boy" wrote in message
...
Hey,

got that.. actually i was modifying the sheet name and then trying it!
Thanks a lot that was really helpful.
can i modify the formula to use it for named sheets? aslo how can i
make
the
make same formula work while dragging it horizontally.

thx once again..
tols

"Ragdyer" wrote:

Fine!
That means that the formula is working.

Type a number, say 100, in B1 (the next cell), and you should see the
100
displays in A1.

Now drag the formula down a few rows to copy it.
You should have 0's in those cells too.

Enter data in the Column B cells and you should see them displayed in
Column
A.

NOW, the formula says look in Sheet1.
You're *in* Sheet1 now, so it's doing what it's supposed to do!

Just enter the *exact* same formula in Sheet2 (or whatever sheet you
wish),
and you should see that same 100 displayed in whatever cell you
entered
it
into.

Drag down to copy as you did in Sheet1, and you should see the
Sheet1 -
Column B data displayed in *this* sheet.

You now have your links between these sheets.

Is everything working now?

If it is, why didn't this happen the first time you used this same
formula?


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-
"wunder boy" wrote in message
...
hi,
i see a 0

"Ragdyer" wrote:

Open a brand new workbook.

Enter the formula I posted into cell A1 of Sheet1:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")

Post back with what you see in cell A1.

--
Regards,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


-------------------------------------------------------------------------
--
"wunder boy" wrote in
message
...
Hi,
thx for ur response.
well my sheet names at the moment are sheet1 sheet2 etc. i have
tried
the
formula but it doesnt seem to be working!

regards,
tols

"RagDyeR" wrote:

That formula works for the *default* XL sheet names.

What are the *actual* names of your sheets?
--

Regards,

RD



--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may
benefit
!



--------------------------------------------------------------------------
--
-------------------

"wunder boy" wrote in
message
...

Hi,
thx for your reply. I just want to display the text. However
When
I
try
the
formula " =INDIRECT("Sheet"&ROWS($1:1)&"!B1")" it was not
taking
values
from
another sheet ie when i tried to enter a value from another
sheet
in
"Sheet"
there was a error.
could u also tell me what an A1-style reference and
R1C1-style
reference
is? can u pls help me out here.

let me try ann explain my problem more clearly.

1.sheet1, sheet 2..to sheetn all have a common format.
2. Now i have a master sheet in the same workbook in which i
want
to
consolidate the data.
3. I want to extract data from all these sheets to this master
sheet
automatically (ie as i add or delete sheets the master list
gets
update
automatically).
4. ijust want the data to be displayed there is no need of
computations.


"RagDyeR" wrote:

Are you talking about totaling a specific cell on each
sheet?

=SUM(Sheet1:Sheet3!D1)

Or, totaling a range:

=SUM(Sheet1:Sheet3!A1:B5)

OR, simply displaying data from a specific cell ( *B1* )
with a
formula
that
can be incremented by copying down:

=INDIRECT("Sheet"&ROWS($1:1)&"!B1")


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"wunder boy" wrote in