Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference text in a cell inside a formula to specify a sheet name
I would like to set up a formula that uses the text from an adjacent cell to
determine the sheet and cell location that is used in the formula. i.e. instead of =IF(sheet1!P90,sheet2!A4,"") I would like the sheet1 part of the formula to be the text from a cell beside the formula cell. How do I do tell the formula to use that text rather than havin gto specify the sheet each time? Thx. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference text in a cell inside a formula to specify a sheet name
Try it like this...
A1 = Sheet1 =IF(INDIRECT("'"&A1&"'!P9")0,Sheet2!A4,"") -- Biff Microsoft Excel MVP "navel151" wrote in message ... I would like to set up a formula that uses the text from an adjacent cell to determine the sheet and cell location that is used in the formula. i.e. instead of =IF(sheet1!P90,sheet2!A4,"") I would like the sheet1 part of the formula to be the text from a cell beside the formula cell. How do I do tell the formula to use that text rather than havin gto specify the sheet each time? Thx. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference text in a cell inside a formula to specify a sheet name
The function you need to look up in Excel help is INDIRECT.
-- David Biddulph navel151 wrote: I would like to set up a formula that uses the text from an adjacent cell to determine the sheet and cell location that is used in the formula. i.e. instead of =IF(sheet1!P90,sheet2!A4,"") I would like the sheet1 part of the formula to be the text from a cell beside the formula cell. How do I do tell the formula to use that text rather than havin gto specify the sheet each time? Thx. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference text in a cell inside a formula to specify a sheet name
Try
with sheetname in cell C1 =IF(INDIRECT("'" & C1 & "'!P9")0,Sheet2!A4,"") -- Jacob "navel151" wrote: I would like to set up a formula that uses the text from an adjacent cell to determine the sheet and cell location that is used in the formula. i.e. instead of =IF(sheet1!P90,sheet2!A4,"") I would like the sheet1 part of the formula to be the text from a cell beside the formula cell. How do I do tell the formula to use that text rather than havin gto specify the sheet each time? Thx. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference text in a cell inside a formula to specify a sheet name
What about using RAnge Names. In Excel 2003 these are file specific in scope in 2007 you can set them Globablly or by sheet. When entering formulae or referring to any area on the spreadsheet, it is usual to refer to a "range". For
example, B6 is a range reference; B6:B10 is also a range reference. A problem with this sort of reference is that it is not always easy to remember what cells to reference. It may be necessary to write down the range, or select it, which often means wasting time scrolling around the spreadsheet. Instead, Excel offers the chance to name ranges on the spreadsheet, and to use these names to select cells, refer to them in formulae or use them in Database, Chart or Macro commands. http://www.mousetraining.co.uk/train...cel2007Adv.pdf page 5 begins a secion on Range Names --- frmsrcurl: http://msgroups.net/microsoft.public...ula-to-specify |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference text in a cell inside a formula to specify a sheet name
TY all - INDIRECT is the function I needed but am having some errors on some
cells so may be looking for more input later. "navel151" wrote: I would like to set up a formula that uses the text from an adjacent cell to determine the sheet and cell location that is used in the formula. i.e. instead of =IF(sheet1!P90,sheet2!A4,"") I would like the sheet1 part of the formula to be the text from a cell beside the formula cell. How do I do tell the formula to use that text rather than havin gto specify the sheet each time? Thx. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get text which is inside the brackets in excel sheet | Excel Worksheet Functions | |||
Use cell value as reference to sheet in formula? | Excel Worksheet Functions | |||
How do I reference data from a sheet specified by text in a cell | Excel Worksheet Functions | |||
can I reference a sheet name in a cell as text? | Excel Worksheet Functions | |||
Need macro to insert text string while inside cell (formula) | Excel Discussion (Misc queries) |