Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to get text which is inside the brackets in excel sheet arun Excel Worksheet Functions 2 April 23rd 09 10:18 AM
Use cell value as reference to sheet in formula? bluegnu Excel Worksheet Functions 4 July 20th 06 01:33 PM
How do I reference data from a sheet specified by text in a cell RedWolf Excel Worksheet Functions 1 January 20th 06 09:03 PM
can I reference a sheet name in a cell as text? Karl Barthel Excel Worksheet Functions 1 November 21st 05 11:15 PM
Need macro to insert text string while inside cell (formula) BrianB Excel Discussion (Misc queries) 0 May 31st 05 03:18 PM


All times are GMT +1. The time now is 01:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"