Formula to refer to other worksheet...

[Guest]

will try to make this as brief as possible. Here's my example:

workbook has several 'sheets' in it, for now we'll say 4. Sheet 4 has been
renamed to Skeleton, and Sheet 3 is called Values. Sheets 1-2 are named
Alpha & Beta. The Skeleton sheet is a diagram of the rest of the sheets.
All the VLOOKUP codes are there, I'm assuming that Sheets Alpha & Beta 'pull'
the formulas onto their pages. However, I need a formula that will count the
number of entries on those two pages & calculate. Right now the Skeleton
sheet has a formula in cell C29 that reads: =COUNT(A5:A26)*120. (The cells
A5:A26 are numbered consecutively from 1-22) I thought the way to insert the
info on the Alpha sheet was to type =skeleton!c29...but I need it to count
only the *nonblank* rows on each sheet. So, say Alpha has info on 5 rows, I
need the calculation to do 5x120. If Beta has 10 rows of info, I need the
calc to do 10x120. Instead, it's counting the whole info from the Skeleton
sheet (22x120).

I inherited this project from someone who has left, and I'm just about at my
wits' end! Can anyone help!????

thanks

 

[RagDyer]

Let XL create the formula for you.

Click a cell on "Skeleton", and enter:
=COUNT(
NOW, navigate to your Alpha sheet and click in A5, and drag down to A26
Then, click in the formula bar and complete the formula
)*12
Then hit <Enter>.

You now have the formula you want, and you can either copy the syntax for
the others, or simply follow the same procedure to complete the rest of the
formulas.
--

HTH,

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


will try to make this as brief as possible. Here's my example:

workbook has several 'sheets' in it, for now we'll say 4. Sheet 4 has been
renamed to Skeleton, and Sheet 3 is called Values. Sheets 1-2 are named
Alpha & Beta. The Skeleton sheet is a diagram of the rest of the sheets.
All the VLOOKUP codes are there, I'm assuming that Sheets Alpha & Beta
'pull'
the formulas onto their pages. However, I need a formula that will count
the
number of entries on those two pages & calculate. Right now the Skeleton
sheet has a formula in cell C29 that reads: =COUNT(A5:A26)*120. (The cells
A5:A26 are numbered consecutively from 1-22) I thought the way to insert
the
info on the Alpha sheet was to type =skeleton!c29...but I need it to count
only the *nonblank* rows on each sheet. So, say Alpha has info on 5 rows, I
need the calculation to do 5x120. If Beta has 10 rows of info, I need the
calc to do 10x120. Instead, it's counting the whole info from the Skeleton
sheet (22x120).

I inherited this project from someone who has left, and I'm just about at my
wits' end! Can anyone help!????

thanks

 

[Frank Kabel]

Hi
you can't do it this way. That is you can't put your formulas on a
master sheet and let them apply automatically to each sheet. You have
to use this formula itself on each sheet

 

[Guest]

RD...THANK YOU...FROM THE DEPTHS OF MY HEART!!

worked like a charm! thanks sooooo much

 

[RagDyer]

Thanks for the feed-back.
--


Regards,

RD