|
Hyperlink
I have the following formula in a cell, sometimes my answer will be "
exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks |
Hyperlink
Where do you get "exception"? Is that a value returned in your VLOOKUP?
If so, then you would do something like =IF(VLOOKUP([arguments])="exception",[Do what you want to do in the exception tab],[Enter your ELSE clause here]). Hope that helps. Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks |
Hyperlink
Dave Thanks for your prompt reply
True Brevity is the soul of wit but not today . Yes, "exception" is the value returned Not sure what I have to add to my existing formula to make it work Please clarify again thanks "Dave F" wrote: Where do you get "exception"? Is that a value returned in your VLOOKUP? If so, then you would do something like =IF(VLOOKUP([arguments])="exception",[Do what you want to do in the exception tab],[Enter your ELSE clause here]). Hope that helps. Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks |
Hyperlink
Well what are you looking to do in the exception tab? That would determine
what you would enter for it. -- Brevity is the soul of wit. "Wanna Learn" wrote: Dave Thanks for your prompt reply True Brevity is the soul of wit but not today . Yes, "exception" is the value returned Not sure what I have to add to my existing formula to make it work Please clarify again thanks "Dave F" wrote: Where do you get "exception"? Is that a value returned in your VLOOKUP? If so, then you would do something like =IF(VLOOKUP([arguments])="exception",[Do what you want to do in the exception tab],[Enter your ELSE clause here]). Hope that helps. Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks |
Hyperlink
Dave
If the valued returned in the cell is "exception" I want it to automatically go to Cell A1! in the worksheet entitled " exceptions" thanks for your patience "Dave F" wrote: Well what are you looking to do in the exception tab? That would determine what you would enter for it. -- Brevity is the soul of wit. "Wanna Learn" wrote: Dave Thanks for your prompt reply True Brevity is the soul of wit but not today . Yes, "exception" is the value returned Not sure what I have to add to my existing formula to make it work Please clarify again thanks "Dave F" wrote: Where do you get "exception"? Is that a value returned in your VLOOKUP? If so, then you would do something like =IF(VLOOKUP([arguments])="exception",[Do what you want to do in the exception tab],[Enter your ELSE clause here]). Hope that helps. Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks |
Hyperlink
The only thing I can think of is that you should have a macro which
automatically runs whenever the value returned from your formula is "exception," which instructs Excel to move you to cell A1 of your sheet "exception." Unfortunately, I don't know how to do that. Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: Dave If the valued returned in the cell is "exception" I want it to automatically go to Cell A1! in the worksheet entitled " exceptions" thanks for your patience "Dave F" wrote: Well what are you looking to do in the exception tab? That would determine what you would enter for it. -- Brevity is the soul of wit. "Wanna Learn" wrote: Dave Thanks for your prompt reply True Brevity is the soul of wit but not today . Yes, "exception" is the value returned Not sure what I have to add to my existing formula to make it work Please clarify again thanks "Dave F" wrote: Where do you get "exception"? Is that a value returned in your VLOOKUP? If so, then you would do something like =IF(VLOOKUP([arguments])="exception",[Do what you want to do in the exception tab],[Enter your ELSE clause here]). Hope that helps. Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks |
Hyperlink
I'd use a second cell adjacent to the cell with the lookup formula and put a
formula like: =HYPERLINK(IF(A2=" exception ","#' exception '!A1","#"&ADDRESS(ROW(),COLUMN())), "Click me") Wanna Learn wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks -- Dave Peterson |
Hyperlink
Thanks Dave
This will work except that I keep getting "Reference not valid" what goes in &ADDRESS(ROW(),COLUMN())), "Dave Peterson" wrote: I'd use a second cell adjacent to the cell with the lookup formula and put a formula like: =HYPERLINK(IF(A2=" exception ","#' exception '!A1","#"&ADDRESS(ROW(),COLUMN())), "Click me") Wanna Learn wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks -- Dave Peterson |
Hyperlink
Nothing goes in that stuff.
The formula tells excel to jump to the exception page using that hyperlink--or jump to the same cell that contains the formula (stay put, in other words). If it didn't work, then share what you used if you changed the formula. Wanna Learn wrote: Thanks Dave This will work except that I keep getting "Reference not valid" what goes in &ADDRESS(ROW(),COLUMN())), "Dave Peterson" wrote: I'd use a second cell adjacent to the cell with the lookup formula and put a formula like: =HYPERLINK(IF(A2=" exception ","#' exception '!A1","#"&ADDRESS(ROW(),COLUMN())), "Click me") Wanna Learn wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks -- Dave Peterson -- Dave Peterson |
Hyperlink
THanks Dave . Excellent
"Dave Peterson" wrote: Nothing goes in that stuff. The formula tells excel to jump to the exception page using that hyperlink--or jump to the same cell that contains the formula (stay put, in other words). If it didn't work, then share what you used if you changed the formula. Wanna Learn wrote: Thanks Dave This will work except that I keep getting "Reference not valid" what goes in &ADDRESS(ROW(),COLUMN())), "Dave Peterson" wrote: I'd use a second cell adjacent to the cell with the lookup formula and put a formula like: =HYPERLINK(IF(A2=" exception ","#' exception '!A1","#"&ADDRESS(ROW(),COLUMN())), "Click me") Wanna Learn wrote: I have the following formula in a cell, sometimes my answer will be " exception " =IF(ISNA(VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)),"",VLOOKUP(J38,'2006 Monthly Sales'!$E$2:$Q$386,10,FALSE)) If the answer is "exception" I want it to automatically go to the exception worksheet - How about a hyperlink to that sheet or is there another way? thanks -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 10:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com