Use a calculated value as a worksheet reference

R

rachel

I want to use the value of the MONTH function within a formula to refer to a
sheet in another workbook eg ='May'! where the reference May comes from the
result of =MONTH(Serial_Number)

Any ideas please
 
D

David Biddulph

Remember that MONTH() will give the number 5. If you want "May" you may
prefer =TEXT(Serial_Number,"mmmm").

To use the result in your reference, I assume you realise that you'll need
the INDIRECT() function.
 
R

rachel

Hello

Thank you for your post, I had if functions to convert to text to get round
that one, I tried using the INDIRECT function with no joy, not one I was
aware of so I've learned something but may have made a mistake!

Thought if I were more specific I'd get more help, never done this
'discussion group' thing before! Basically, I have a workbook with multiple
sheets containing monthly summaries, I need to be able to use the
calculations within these sheets to provide information for another workbook,
I can get round the problem with nested if functions but as some of the
formulas are already nested to the hilt I wondered if there were a simpler
way to reference the appropriate cells. I have copied the simplest formula
below to better demonstrate what I mean.
=COUNTIF('V:\Folder\Sub Folder\[Team Issues
Log.xls]Sheet1'!$F$2:$F$500,"Closed")

Where I need the reference Sheet1 to be the result of a formula

Any ideas?
--
Many Thanks
Rachel


David Biddulph said:
Remember that MONTH() will give the number 5. If you want "May" you may
prefer =TEXT(Serial_Number,"mmmm").

To use the result in your reference, I assume you realise that you'll need
the INDIRECT() function.
Click to expand...
 
A

Arvi Laanemets

Hi

In original post you did say you want to get data from another sheet - now
it looks like you want to get them from another workbook! INDIRECT works
with source in another workbook only when this source workbook is open too.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



rachel said:
Hello

Thank you for your post, I had if functions to convert to text to get
round
that one, I tried using the INDIRECT function with no joy, not one I was
aware of so I've learned something but may have made a mistake!

Thought if I were more specific I'd get more help, never done this
'discussion group' thing before! Basically, I have a workbook with
multiple
sheets containing monthly summaries, I need to be able to use the
calculations within these sheets to provide information for another
workbook,
I can get round the problem with nested if functions but as some of the
formulas are already nested to the hilt I wondered if there were a simpler
way to reference the appropriate cells. I have copied the simplest formula
below to better demonstrate what I mean.
=COUNTIF('V:\Folder\Sub Folder\[Team Issues
Log.xls]Sheet1'!$F$2:$F$500,"Closed")

Where I need the reference Sheet1 to be the result of a formula

Any ideas?
Click to expand...
 
B

Balzyone

Have you tried a =CHOOSE((ref cell),value1,value2,value3,...) formula? I set
up my dashboard so that when my reference cell was the month value it would
refer to the correct column, but you could link to another worksheet or even
workbook like this.
 
Top