Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a calculated value as a worksheet reference
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 -- Many Thanks Rachel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a calculated value as a worksheet reference
Hi
Something like =INDIRECT(TEXT(TODAY(),"mmmm") & "!A1") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "rachel" wrote in message ... 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 -- Many Thanks Rachel |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a calculated value as a worksheet reference
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. -- David Biddulph "rachel" wrote in message ... 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 -- Many Thanks Rachel |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a calculated value as a worksheet reference
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" wrote: 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. -- David Biddulph "rachel" wrote in message ... 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 -- Many Thanks Rachel |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a calculated value as a worksheet reference
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<attarkon.ee ) "rachel" wrote in message ... 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" wrote: 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. -- David Biddulph "rachel" wrote in message ... 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 -- Many Thanks Rachel |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a calculated value as a worksheet reference
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. "rachel" wrote: 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 -- Many Thanks Rachel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
PivotTable:Using a calculated field result in another calculated f | Excel Worksheet Functions | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
can i get a letter to be calculated in a worksheet | Excel Worksheet Functions |