Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Links to tabsheets
Hi!
How can I make a link to another sheet be dependable on one cell value (without ifs)? I give you an example: I have a worksheet with three sheets ('Sheet1','Sheet2', and 'Sheet3') In 'Sheet1' I have two cells: A1 - Its value can be 'Sheet2' or 'Sheet3' A2 - It has a formula (for example: 'Sheet2'!B1) I would like to make a formula like this one for cell A2: *A1*!B1 and the Excel to "substitute" the * for Sheet2 or Sheet3 depending on the value in A1. I hope i explained it ok. Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Links to tabsheets
How about using a simple if statement If (A1 ="Sheet2",'Sheet2'!A1*'Sheet2'B1,'Sheet3'!A1*'Shee t3'!B1) -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=487830 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Links to tabsheets
Think you're looking for INDIRECT ..
Put in A1: Sheet2 Put in A2: =INDIRECT("'"& A1 &"'!B1") A2 returns the same as : =Sheet2!B1 Changing the sheetname in A1 to: Sheet3 will then return the value in Sheet3's B1 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Alberto Pinto" wrote in message ... Hi! How can I make a link to another sheet be dependable on one cell value (without ifs)? I give you an example: I have a worksheet with three sheets ('Sheet1','Sheet2', and 'Sheet3') In 'Sheet1' I have two cells: A1 - Its value can be 'Sheet2' or 'Sheet3' A2 - It has a formula (for example: 'Sheet2'!B1) I would like to make a formula like this one for cell A2: *A1*!B1 and the Excel to "substitute" the * for Sheet2 or Sheet3 depending on the value in A1. I hope i explained it ok. Thanks in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Links to tabsheets
Please, what does the ' as in ("'" do in this formula
Thanks Max wrote: Think you're looking for INDIRECT .. Put in A1: Sheet2 Put in A2: =INDIRECT("'"& A1 &"'!B1") A2 returns the same as : =Sheet2!B1 Changing the sheetname in A1 to: Sheet3 will then return the value in Sheet3's B1 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Alberto Pinto" wrote in message ... Hi! How can I make a link to another sheet be dependable on one cell value (without ifs)? I give you an example: I have a worksheet with three sheets ('Sheet1','Sheet2', and 'Sheet3') In 'Sheet1' I have two cells: A1 - Its value can be 'Sheet2' or 'Sheet3' A2 - It has a formula (for example: 'Sheet2'!B1) I would like to make a formula like this one for cell A2: *A1*!B1 and the Excel to "substitute" the * for Sheet2 or Sheet3 depending on the value in A1. I hope i explained it ok. Thanks in advance |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Links to tabsheets
"noyb" wrote
.. what does the ' as in ("'" do in this formula .. The single quotes / apostrophes (there's actually a pair of them) allows any worksheet name to be accommodated in A1 Best that I quote this advisory from a post by Harlan: ".. *ALWAYS* (and I *DO* mean **ALWAYS**) include single quotes around worksheet names when constructing textrefs inside INDIRECT calls. The result is far more robust since without them worksheet names that contain spaces or exclamation points (along with a few others that require single quote delimiters) will choke INDIRECT so that it returns #REF!. Even if the worksheet name doesn't require single quote delimiters, no harm done adding them. .. " -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Links to tabsheets
Thanks
Max wrote: "noyb" wrote .. what does the ' as in ("'" do in this formula .. The single quotes / apostrophes (there's actually a pair of them) allows any worksheet name to be accommodated in A1 Best that I quote this advisory from a post by Harlan: ".. *ALWAYS* (and I *DO* mean **ALWAYS**) include single quotes around worksheet names when constructing textrefs inside INDIRECT calls. The result is far more robust since without them worksheet names that contain spaces or exclamation points (along with a few others that require single quote delimiters) will choke INDIRECT so that it returns #REF!. Even if the worksheet name doesn't require single quote delimiters, no harm done adding them. .. " -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Links to tabsheets
Delighted !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "noyb" wrote in message ... Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Links to Tabsheets | Links and Linking in Excel | |||
options to update automatic links | Excel Worksheet Functions | |||
Prompt to update links | Links and Linking in Excel | |||
Deleting links to other spreadsheets | Excel Worksheet Functions | |||
Using variable in external links | Excel Discussion (Misc queries) |