Data exctraction from multiple sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
 
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!
=====================================================


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?
 
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.
 
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 !
----------------------------------------------------------------------------
-------------------


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.
 
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
 
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.
 
hi,
i see a 0

Ragdyer said:
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
Click to expand...
 
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?
 
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
 
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!
=====================================================

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
 
You're welcome, and thank you for the feed-back.

--

Regards,

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

Hey,

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

best wishes,
tols

RagDyeR said:
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!
=====================================================

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 said:
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?
Click to expand...
!
--------------------------------------------------------------------------
-


--
Click to expand...
benefit
!
-------------------------------------------------------------------------
Click to expand...
--
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

:

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

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

Regards,

RD
Click to expand...
--------------------------------------------------------------------------
Click to expand...
benefit
!
Click to expand...
Click to expand...
 
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
 
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.
 
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
 
Back
Top