| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF, INDIRECT, & Wild cards
I have the following formula in a worksheet cell that captures data from
another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, INDIRECT, & Wild cards
Try the below instead
=IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" & "h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If this post helps click Yes --------------- Jacob Skaria "javablood" wrote: I have the following formula in a worksheet cell that captures data from another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, INDIRECT, & Wild cards
Jacob,
Brilliant! thanks. But if I have another letter, say "J" I tried: =IF(ISERROR(SEARCH("U",INDIRECT("'"&$T$1&"'!"&"d"& $N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), IF(ISERROR(SEARCH("J",INDIRECT("'"&$T$1&"'!"&"d"&$ N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), INDIRECT("'"&$T$1&"'!"&"d"&$N37))) but it did not work, I get #VALUE. It must be something about the ISERROR that I do not know. Instead of trying to account for whatever letters there may be, is there a way to search/detect for no letters or just a number to be able to pull the value from that? -- javablood "Jacob Skaria" wrote: Try the below instead =IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" & "h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If this post helps click Yes --------------- Jacob Skaria "javablood" wrote: I have the following formula in a worksheet cell that captures data from another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, INDIRECT, & Wild cards
I think I answered my own question with this:
=IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Thanks for your help! -- javablood "javablood" wrote: Jacob, Brilliant! thanks. But if I have another letter, say "J" I tried: =IF(ISERROR(SEARCH("U",INDIRECT("'"&$T$1&"'!"&"d"& $N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), IF(ISERROR(SEARCH("J",INDIRECT("'"&$T$1&"'!"&"d"&$ N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), INDIRECT("'"&$T$1&"'!"&"d"&$N37))) but it did not work, I get #VALUE. It must be something about the ISERROR that I do not know. Instead of trying to account for whatever letters there may be, is there a way to search/detect for no letters or just a number to be able to pull the value from that? -- javablood "Jacob Skaria" wrote: Try the below instead =IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" & "h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If this post helps click Yes --------------- Jacob Skaria "javablood" wrote: I have the following formula in a worksheet cell that captures data from another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, INDIRECT, & Wild cards
Not sure what you're trying to do here, but...
What is the result of: INDIRECT("'"&$T$1&"'!" & "h"&$N37) It looks like it might be a TEXT number since you're using the VALUE function: VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If that's the case you might be able use this and save a few keystrokes. =IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37)) -- Biff Microsoft Excel MVP "javablood" wrote in message ... I think I answered my own question with this: =IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Thanks for your help! -- javablood "javablood" wrote: Jacob, Brilliant! thanks. But if I have another letter, say "J" I tried: =IF(ISERROR(SEARCH("U",INDIRECT("'"&$T$1&"'!"&"d"& $N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), IF(ISERROR(SEARCH("J",INDIRECT("'"&$T$1&"'!"&"d"&$ N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), INDIRECT("'"&$T$1&"'!"&"d"&$N37))) but it did not work, I get #VALUE. It must be something about the ISERROR that I do not know. Instead of trying to account for whatever letters there may be, is there a way to search/detect for no letters or just a number to be able to pull the value from that? -- javablood "Jacob Skaria" wrote: Try the below instead =IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" & "h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If this post helps click Yes --------------- Jacob Skaria "javablood" wrote: I have the following formula in a worksheet cell that captures data from another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, INDIRECT, & Wild cards
Biff,
I have a spreadsheet in which data from Access is updated for graphing purposes. The data, which are analtyical results from environmental samples and are in concentration units, e.g., mg/l, can either be detections (values) or nondetections (contain a "U" signifying a less than condition). The INDIRECT("'"&$T$1&"'!" & "h"&$N37) is used to capture the data from another worksheet without a lot of hand entry. So, yes, what comes in from Access is a text field because I am combining a concentration field (value) and a flag field (U). I was trying to account for the "U" in order to ignore those cells and only get the value of a detected chemical. I have 12 chemicals from which I need to extract the detections and I just paste the data from Access into a new row, go to my 'calculation' worksheet to copy/paste the the formulas and input the new row number, e.g., now in $N38, and voila I have my data. So I do not think the count fn will work fro me and what I need. Thanks. -- javablood "T. Valko" wrote: Not sure what you're trying to do here, but... What is the result of: INDIRECT("'"&$T$1&"'!" & "h"&$N37) It looks like it might be a TEXT number since you're using the VALUE function: VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If that's the case you might be able use this and save a few keystrokes. =IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37)) -- Biff Microsoft Excel MVP "javablood" wrote in message ... I think I answered my own question with this: =IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Thanks for your help! -- javablood "javablood" wrote: Jacob, Brilliant! thanks. But if I have another letter, say "J" I tried: =IF(ISERROR(SEARCH("U",INDIRECT("'"&$T$1&"'!"&"d"& $N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), IF(ISERROR(SEARCH("J",INDIRECT("'"&$T$1&"'!"&"d"&$ N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), INDIRECT("'"&$T$1&"'!"&"d"&$N37))) but it did not work, I get #VALUE. It must be something about the ISERROR that I do not know. Instead of trying to account for whatever letters there may be, is there a way to search/detect for no letters or just a number to be able to pull the value from that? -- javablood "Jacob Skaria" wrote: Try the below instead =IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" & "h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If this post helps click Yes --------------- Jacob Skaria "javablood" wrote: I have the following formula in a worksheet cell that captures data from another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, INDIRECT, & Wild cards
Ok, let me explain what the formula is doing. Here's your formula:
=IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Here's my suggested replacement: =IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37)) In your formula, you're using VALUE(...) to convert a TEXT number to a numeric number. You're using the ISERROR function to handle when the cell contains a text character like the letter U. Basically, return whatever is in cell H? and if it's a text number convert it to a numeric number otherwise just return the text character. My formula is doing *exactly* that just in a different way. COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)) does the same thing as: ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) And --INDIRECT("'"&$T$1&"'!H"&$N37) does the same thing as VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)) In my formula: If cell H? contains just a letter: -INDIRECT("'"&$T$1&"'!H"&$N37) Will return a #VALUE! error and pass that error to COUNT. COUNT ignores errors so the count will be 0 and the IF logical test will evaluate to FALSE and process the value_if_false argument which is simple return whatever is in cell H?. If cell H? contains a TEXT number like 100: -INDIRECT("'"&$T$1&"'!H"&$N37) The minus sign will coerce the text number to be the *numeric* number -100 and pass this value to the COUNT function. Since -100 is a true numeric value the count will be 1 and the IF logical test will evaluate to TRUE and process the value_if_true argument: --INDIRECT("'"&$T$1&"'!H"&$N37). Double negation works like this: The first minus sign coerces the text number to a negative numeric number. The second minus sign coerces the negative numeric number to a positive numeric number: text 100 -100 = -100 as a numeric number --100 = 100 as a numeric number So, the value_if_true argument will return numeric 100. My formula is doing the same thing you're formula is doing just in a different way that saves a few keystrokes and uses a couple less function calls. -- Biff Microsoft Excel MVP "javablood" wrote in message ... Biff, I have a spreadsheet in which data from Access is updated for graphing purposes. The data, which are analtyical results from environmental samples and are in concentration units, e.g., mg/l, can either be detections (values) or nondetections (contain a "U" signifying a less than condition). The INDIRECT("'"&$T$1&"'!" & "h"&$N37) is used to capture the data from another worksheet without a lot of hand entry. So, yes, what comes in from Access is a text field because I am combining a concentration field (value) and a flag field (U). I was trying to account for the "U" in order to ignore those cells and only get the value of a detected chemical. I have 12 chemicals from which I need to extract the detections and I just paste the data from Access into a new row, go to my 'calculation' worksheet to copy/paste the the formulas and input the new row number, e.g., now in $N38, and voila I have my data. So I do not think the count fn will work fro me and what I need. Thanks. -- javablood "T. Valko" wrote: Not sure what you're trying to do here, but... What is the result of: INDIRECT("'"&$T$1&"'!" & "h"&$N37) It looks like it might be a TEXT number since you're using the VALUE function: VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If that's the case you might be able use this and save a few keystrokes. =IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37)) -- Biff Microsoft Excel MVP "javablood" wrote in message ... I think I answered my own question with this: =IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Thanks for your help! -- javablood "javablood" wrote: Jacob, Brilliant! thanks. But if I have another letter, say "J" I tried: =IF(ISERROR(SEARCH("U",INDIRECT("'"&$T$1&"'!"&"d"& $N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), IF(ISERROR(SEARCH("J",INDIRECT("'"&$T$1&"'!"&"d"&$ N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), INDIRECT("'"&$T$1&"'!"&"d"&$N37))) but it did not work, I get #VALUE. It must be something about the ISERROR that I do not know. Instead of trying to account for whatever letters there may be, is there a way to search/detect for no letters or just a number to be able to pull the value from that? -- javablood "Jacob Skaria" wrote: Try the below instead =IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" & "h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If this post helps click Yes --------------- Jacob Skaria "javablood" wrote: I have the following formula in a worksheet cell that captures data from another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, INDIRECT, & Wild cards
Ok, thanks. More than one way to direct a cell!
-- javablood "T. Valko" wrote: Ok, let me explain what the formula is doing. Here's your formula: =IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Here's my suggested replacement: =IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37)) In your formula, you're using VALUE(...) to convert a TEXT number to a numeric number. You're using the ISERROR function to handle when the cell contains a text character like the letter U. Basically, return whatever is in cell H? and if it's a text number convert it to a numeric number otherwise just return the text character. My formula is doing *exactly* that just in a different way. COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)) does the same thing as: ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) And --INDIRECT("'"&$T$1&"'!H"&$N37) does the same thing as VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)) In my formula: If cell H? contains just a letter: -INDIRECT("'"&$T$1&"'!H"&$N37) Will return a #VALUE! error and pass that error to COUNT. COUNT ignores errors so the count will be 0 and the IF logical test will evaluate to FALSE and process the value_if_false argument which is simple return whatever is in cell H?. If cell H? contains a TEXT number like 100: -INDIRECT("'"&$T$1&"'!H"&$N37) The minus sign will coerce the text number to be the *numeric* number -100 and pass this value to the COUNT function. Since -100 is a true numeric value the count will be 1 and the IF logical test will evaluate to TRUE and process the value_if_true argument: --INDIRECT("'"&$T$1&"'!H"&$N37). Double negation works like this: The first minus sign coerces the text number to a negative numeric number. The second minus sign coerces the negative numeric number to a positive numeric number: text 100 -100 = -100 as a numeric number --100 = 100 as a numeric number So, the value_if_true argument will return numeric 100. My formula is doing the same thing you're formula is doing just in a different way that saves a few keystrokes and uses a couple less function calls. -- Biff Microsoft Excel MVP "javablood" wrote in message ... Biff, I have a spreadsheet in which data from Access is updated for graphing purposes. The data, which are analtyical results from environmental samples and are in concentration units, e.g., mg/l, can either be detections (values) or nondetections (contain a "U" signifying a less than condition). The INDIRECT("'"&$T$1&"'!" & "h"&$N37) is used to capture the data from another worksheet without a lot of hand entry. So, yes, what comes in from Access is a text field because I am combining a concentration field (value) and a flag field (U). I was trying to account for the "U" in order to ignore those cells and only get the value of a detected chemical. I have 12 chemicals from which I need to extract the detections and I just paste the data from Access into a new row, go to my 'calculation' worksheet to copy/paste the the formulas and input the new row number, e.g., now in $N38, and voila I have my data. So I do not think the count fn will work fro me and what I need. Thanks. -- javablood "T. Valko" wrote: Not sure what you're trying to do here, but... What is the result of: INDIRECT("'"&$T$1&"'!" & "h"&$N37) It looks like it might be a TEXT number since you're using the VALUE function: VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If that's the case you might be able use this and save a few keystrokes. =IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37)) -- Biff Microsoft Excel MVP "javablood" wrote in message ... I think I answered my own question with this: =IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Thanks for your help! -- javablood "javablood" wrote: Jacob, Brilliant! thanks. But if I have another letter, say "J" I tried: =IF(ISERROR(SEARCH("U",INDIRECT("'"&$T$1&"'!"&"d"& $N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), IF(ISERROR(SEARCH("J",INDIRECT("'"&$T$1&"'!"&"d"&$ N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), INDIRECT("'"&$T$1&"'!"&"d"&$N37))) but it did not work, I get #VALUE. It must be something about the ISERROR that I do not know. Instead of trying to account for whatever letters there may be, is there a way to search/detect for no letters or just a number to be able to pull the value from that? -- javablood "Jacob Skaria" wrote: Try the below instead =IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" & "h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If this post helps click Yes --------------- Jacob Skaria "javablood" wrote: I have the following formula in a worksheet cell that captures data from another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, INDIRECT, & Wild cards
You're welcome!
-- Biff Microsoft Excel MVP "javablood" wrote in message ... Ok, thanks. More than one way to direct a cell! -- javablood "T. Valko" wrote: Ok, let me explain what the formula is doing. Here's your formula: =IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Here's my suggested replacement: =IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37)) In your formula, you're using VALUE(...) to convert a TEXT number to a numeric number. You're using the ISERROR function to handle when the cell contains a text character like the letter U. Basically, return whatever is in cell H? and if it's a text number convert it to a numeric number otherwise just return the text character. My formula is doing *exactly* that just in a different way. COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)) does the same thing as: ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) And --INDIRECT("'"&$T$1&"'!H"&$N37) does the same thing as VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)) In my formula: If cell H? contains just a letter: -INDIRECT("'"&$T$1&"'!H"&$N37) Will return a #VALUE! error and pass that error to COUNT. COUNT ignores errors so the count will be 0 and the IF logical test will evaluate to FALSE and process the value_if_false argument which is simple return whatever is in cell H?. If cell H? contains a TEXT number like 100: -INDIRECT("'"&$T$1&"'!H"&$N37) The minus sign will coerce the text number to be the *numeric* number -100 and pass this value to the COUNT function. Since -100 is a true numeric value the count will be 1 and the IF logical test will evaluate to TRUE and process the value_if_true argument: --INDIRECT("'"&$T$1&"'!H"&$N37). Double negation works like this: The first minus sign coerces the text number to a negative numeric number. The second minus sign coerces the negative numeric number to a positive numeric number: text 100 -100 = -100 as a numeric number --100 = 100 as a numeric number So, the value_if_true argument will return numeric 100. My formula is doing the same thing you're formula is doing just in a different way that saves a few keystrokes and uses a couple less function calls. -- Biff Microsoft Excel MVP "javablood" wrote in message ... Biff, I have a spreadsheet in which data from Access is updated for graphing purposes. The data, which are analtyical results from environmental samples and are in concentration units, e.g., mg/l, can either be detections (values) or nondetections (contain a "U" signifying a less than condition). The INDIRECT("'"&$T$1&"'!" & "h"&$N37) is used to capture the data from another worksheet without a lot of hand entry. So, yes, what comes in from Access is a text field because I am combining a concentration field (value) and a flag field (U). I was trying to account for the "U" in order to ignore those cells and only get the value of a detected chemical. I have 12 chemicals from which I need to extract the detections and I just paste the data from Access into a new row, go to my 'calculation' worksheet to copy/paste the the formulas and input the new row number, e.g., now in $N38, and voila I have my data. So I do not think the count fn will work fro me and what I need. Thanks. -- javablood "T. Valko" wrote: Not sure what you're trying to do here, but... What is the result of: INDIRECT("'"&$T$1&"'!" & "h"&$N37) It looks like it might be a TEXT number since you're using the VALUE function: VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If that's the case you might be able use this and save a few keystrokes. =IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37)) -- Biff Microsoft Excel MVP "javablood" wrote in message ... I think I answered my own question with this: =IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))), INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) Thanks for your help! -- javablood "javablood" wrote: Jacob, Brilliant! thanks. But if I have another letter, say "J" I tried: =IF(ISERROR(SEARCH("U",INDIRECT("'"&$T$1&"'!"&"d"& $N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), IF(ISERROR(SEARCH("J",INDIRECT("'"&$T$1&"'!"&"d"&$ N37))), VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)), INDIRECT("'"&$T$1&"'!"&"d"&$N37))) but it did not work, I get #VALUE. It must be something about the ISERROR that I do not know. Instead of trying to account for whatever letters there may be, is there a way to search/detect for no letters or just a number to be able to pull the value from that? -- javablood "Jacob Skaria" wrote: Try the below instead =IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" & "h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!" & "h"&$N37)) If this post helps click Yes --------------- Jacob Skaria "javablood" wrote: I have the following formula in a worksheet cell that captures data from another worksheet: =IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))) This works great if there is no "U" in the referred to cell because a number is returned but if there is a "U" in the referred to cell, #VALUE! is returned! If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether there is a "U" or not in the referred to cell, e.g., 10U will be returned but a 50 will be returned as text and I need a number if there is no "U". Hence, my need for the IF statement. The referred to cells come from an Access database query where I had to combine two fields to create one field for Excel. Does anyone have an idea of what I may be doing wrong? TIA -- javablood |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXCEL Wild Cards | New Users to Excel | |||
| Wild Cards with IF stmt | Excel Worksheet Functions | |||
| wild cards? | Excel Discussion (Misc queries) | |||
| wild cards in SUMIF | Excel Worksheet Functions | |||
| If Statement Using Wild Cards | Excel Worksheet Functions | |||
Linear Mode
