| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use cell contents to refer to a sheetname in a formula
I have the following formula, which looks up the following:
The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the month of February (Summary! E3): =INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal !$A$1:$A$3)*(Summary!$F$2=Legal!$B$1:$B$3),0)) The formula works great (entered as an array). Now, on the Summary tab, I would like to enter other sheet names for other departments (ie., HR, Marketing), and have the formula read it from the cell. On the Summary tab, Legal is listed in call A7. So instead of Legal!$D1:$D3, how do I make it read A7!$D1:$D3, so I can copy it down and have the information pick up for my other departments? I apologize if this has been asked/answered before; I haven't been able to find a spot-on matching problem, and nothing I've tried works. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use cell contents to refer to a sheetname in a formula
On Sep 29, 6:35*pm, cp wrote:
I have the following formula, which looks up the following: The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the month of February (Summary! E3): =INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal !$A$1:$A$3)*(Summary!$F$2 =Legal!$B$1:$B$3),0)) The formula works great (entered as an array). *Now, on the Summary tab, I would like to enter other sheet names for other departments (ie., HR, Marketing), and have the formula read it from the cell. *On the Summary tab, Legal is listed in call A7. *So instead of Legal!$D1:$D3, how do I make it read A7!$D1:$D3, so I can copy it down and have the information pick up for my other departments? I apologize if this has been asked/answered before; I haven't been able to find a spot-on matching problem, and nothing I've tried works. Your formula is a bit too complex for me, but you should have a look at the indirect-function. Per Erik |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use cell contents to refer to a sheetname in a formula
The indirect function may indeed be the answer, but I have not been able to
get it to work for me. "Per Erik Midtrød" wrote: On Sep 29, 6:35 pm, cp wrote: I have the following formula, which looks up the following: The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the month of February (Summary! E3): =INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal !$A$1:$A$3)*(Summary!$F$2 =Legal!$B$1:$B$3),0)) The formula works great (entered as an array). Now, on the Summary tab, I would like to enter other sheet names for other departments (ie., HR, Marketing), and have the formula read it from the cell. On the Summary tab, Legal is listed in call A7. So instead of Legal!$D1:$D3, how do I make it read A7!$D1:$D3, so I can copy it down and have the information pick up for my other departments? I apologize if this has been asked/answered before; I haven't been able to find a spot-on matching problem, and nothing I've tried works. Your formula is a bit too complex for me, but you should have a look at the indirect-function. Per Erik |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use cell contents to refer to a sheetname in a formula
This should work
=INDEX(INDIRECT("'"&A7&"'!$D$1:$D$3"),MATCH(1,(IND IRECT("'"&A7&"'!$A$1:$A$3")=Summary!$E$2)*(INDIREC T("'"&A7&"'!$B$1:$B$3")=Summary!$F$2),0)) also entered with ctrl + shift & enter -- Regards, Peo Sjoblom "cp" wrote in message ... The indirect function may indeed be the answer, but I have not been able to get it to work for me. "Per Erik Midtrød" wrote: On Sep 29, 6:35 pm, cp wrote: I have the following formula, which looks up the following: The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the month of February (Summary! E3): =INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal !$A$1:$A$3)*(Summary!$F$2 =Legal!$B$1:$B$3),0)) The formula works great (entered as an array). Now, on the Summary tab, I would like to enter other sheet names for other departments (ie., HR, Marketing), and have the formula read it from the cell. On the Summary tab, Legal is listed in call A7. So instead of Legal!$D1:$D3, how do I make it read A7!$D1:$D3, so I can copy it down and have the information pick up for my other departments? I apologize if this has been asked/answered before; I haven't been able to find a spot-on matching problem, and nothing I've tried works. Your formula is a bit too complex for me, but you should have a look at the indirect-function. Per Erik |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use cell contents to refer to a sheetname in a formula
Hi,
Use a formula of the form: =INDEX(INDIRECT(E4&"!$D$1:$D$3"),MATCH(1,(Summary! $E$2=INDIRECT(E4&"!$A$1:$A$3"))*(Summary!$F$2=INDI RECT(E4&"!$B$1:$B$3")),0)) Where E4 contains the sheet name. -- Thanks, Shane Devenshire "cp" wrote: I have the following formula, which looks up the following: The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the month of February (Summary! E3): =INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal !$A$1:$A$3)*(Summary!$F$2=Legal!$B$1:$B$3),0)) The formula works great (entered as an array). Now, on the Summary tab, I would like to enter other sheet names for other departments (ie., HR, Marketing), and have the formula read it from the cell. On the Summary tab, Legal is listed in call A7. So instead of Legal!$D1:$D3, how do I make it read A7!$D1:$D3, so I can copy it down and have the information pick up for my other departments? I apologize if this has been asked/answered before; I haven't been able to find a spot-on matching problem, and nothing I've tried works. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use cell contents to refer to a sheetname in a formula
Won't work if the sheet name has a space
-- Regards, Peo Sjoblom "ShaneDevenshire" wrote in message ... Hi, Use a formula of the form: =INDEX(INDIRECT(E4&"!$D$1:$D$3"),MATCH(1,(Summary! $E$2=INDIRECT(E4&"!$A$1:$A$3"))*(Summary!$F$2=INDI RECT(E4&"!$B$1:$B$3")),0)) Where E4 contains the sheet name. -- Thanks, Shane Devenshire "cp" wrote: I have the following formula, which looks up the following: The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the month of February (Summary! E3): =INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal !$A$1:$A$3)*(Summary!$F$2=Legal!$B$1:$B$3),0)) The formula works great (entered as an array). Now, on the Summary tab, I would like to enter other sheet names for other departments (ie., HR, Marketing), and have the formula read it from the cell. On the Summary tab, Legal is listed in call A7. So instead of Legal!$D1:$D3, how do I make it read A7!$D1:$D3, so I can copy it down and have the information pick up for my other departments? I apologize if this has been asked/answered before; I haven't been able to find a spot-on matching problem, and nothing I've tried works. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use cell contents to refer to a sheetname in a formula
This works, since some of my sheet names DO have a space. I did try the
indirect function before, but obviously missed something (id addition to the space). Thank you SO much for your time! -- cp "Peo Sjoblom" wrote: This should work =INDEX(INDIRECT("'"&A7&"'!$D$1:$D$3"),MATCH(1,(IND IRECT("'"&A7&"'!$A$1:$A$3")=Summary!$E$2)*(INDIREC T("'"&A7&"'!$B$1:$B$3")=Summary!$F$2),0)) also entered with ctrl + shift & enter -- Regards, Peo Sjoblom "cp" wrote in message ... The indirect function may indeed be the answer, but I have not been able to get it to work for me. "Per Erik Midtrød" wrote: On Sep 29, 6:35 pm, cp wrote: I have the following formula, which looks up the following: The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the month of February (Summary! E3): =INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal !$A$1:$A$3)*(Summary!$F$2 =Legal!$B$1:$B$3),0)) The formula works great (entered as an array). Now, on the Summary tab, I would like to enter other sheet names for other departments (ie., HR, Marketing), and have the formula read it from the cell. On the Summary tab, Legal is listed in call A7. So instead of Legal!$D1:$D3, how do I make it read A7!$D1:$D3, so I can copy it down and have the information pick up for my other departments? I apologize if this has been asked/answered before; I haven't been able to find a spot-on matching problem, and nothing I've tried works. Your formula is a bit too complex for me, but you should have a look at the indirect-function. Per Erik |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Refer to a worksheet/name using cell contents? | Excel Worksheet Functions | |||
| Request for formula to refer some letters from Cell | Excel Worksheet Functions | |||
| Automatically update SheetName in workbook sub if SheetName changes | Excel Discussion (Misc queries) | |||
| How do I refer to the tab name in a cell formula in Excel? | Excel Discussion (Misc queries) | |||
| can formula to read sheetname from a cell? | Excel Worksheet Functions | |||
Linear Mode
