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