Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I'm Trying to Construct a Smart Worksheet
Hi,
Im trying to construct a smart worksheet so when I enter a number or any value it will automatically give me a specific result if it meets the requirements from a first worksheet. This is an example of what Im trying to accomplish, please if anyone can assist I will be very grateful. Sheet 1 Sheet 2 A A A 1 20 1 704080000112 1 2 30 2 2 3 7 3 3 IF(LEFT(A1,2)=Sheet1! A1:A5,"CHARD"," ") Basically I want to use a formula that when the first to digits of a number is equal to any of the numbers in sheet 1 the result would be any word I want (example€¯Chard€¯) if not then I want the cell to be blank. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I'm Trying to Construct a Smart Worksheet
Try something like this:
=IF(ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$1:A$5,0)),"", "your_word") Copy this down to suit. Assumes your have proper numbers in Sheet1, rather than text values. Hope this helps. Pete On May 12, 3:03*pm, Santi wrote: Hi, I’m trying to construct a smart worksheet so when I enter a number or any value it will automatically give me a specific result if it meets the requirements from a first worksheet. This is an example of what I’m trying to accomplish, please if anyone can assist I will be very grateful. Sheet 1 * * * * * * * *Sheet 2 * * * * * * * * * * * *A * * * * * * * * * * *A * * * * * * * * * *A * * * * * * * * * * * * * * * * * * * * * 1 * 20 * * * * * * * * * * * * * * * * * *1 704080000112 * * * *1 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 2 * 30 * * * * * * * * * * *2 * * * * * * * * * 2 * * * * * * * * * * * * * * * * * * * 3 * 7 * * * * * * * * * * * * * * * * * * *3 * * * * * * * * * *3 *IF(LEFT(A1,2)=Sheet1! * A1:A5,"CHARD"," ") * Basically I want to use a formula that when the first to digits of a number is equal to any of the numbers in sheet 1 the result would be any word I want (example”Chard”) if not then I want the cell to be blank. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I'm Trying to Construct a Smart Worksheet
Pete,
That worked perfectly! But do you know if I can use your formula for multiple answers where lets say the numbers in range A$1:A$5 in sheet 1 does not contain any of the numbers specified in Left(A1,2)? But maybe the number is located in a second range which is A$7:A$12. "Pete_UK" wrote: Try something like this: =IF(ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$1:A$5,0)),"", "your_word") Copy this down to suit. Assumes your have proper numbers in Sheet1, rather than text values. Hope this helps. Pete On May 12, 3:03 pm, Santi wrote: Hi, Im trying to construct a smart worksheet so when I enter a number or any value it will automatically give me a specific result if it meets the requirements from a first worksheet. This is an example of what Im trying to accomplish, please if anyone can assist I will be very grateful. Sheet 1 Sheet 2 A A A 1 20 1 704080000112 1 2 30 2 2 3 7 3 3 IF(LEFT(A1,2)=Sheet1! A1:A5,"CHARD"," ") Basically I want to use a formula that when the first to digits of a number is equal to any of the numbers in sheet 1 the result would be any word I want (example€¯Chard€¯) if not then I want the cell to be blank. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I'm Trying to Construct a Smart Worksheet
On May 13, 4:30 am, Santi wrote:
Pete, That worked perfectly! But do you know if I can use your formula for multiple answers where lets say the numbers in range A$1:A$5 in sheet 1 does not contain any of the numbers specified in Left(A1,2)? But maybe the number is located in a second range which is A$7:A$12. "Pete_UK" wrote: Try something like this: =IF(ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$1:A$5,0)),"", "your_word") Copy this down to suit. Assumes your have proper numbers in Sheet1, rather than text values. Hope this helps. Pete On May 12, 3:03 pm, Santi wrote: Hi, I’m trying to construct a smart worksheet so when I enter a number or any value it will automatically give me a specific result if it meets the requirements from a first worksheet. This is an example of what I’m trying to accomplish, please if anyone can assist I will be very grateful. Sheet 1 Sheet 2 A A A 1 20 1 704080000112 1 2 30 2 2 3 7 3 3 IF(LEFT(A1,2)=Sheet1! A1:A5,"CHARD"," ") Basically I want to use a formula that when the first to digits of a number is equal to any of the numbers in sheet 1 the result would be any word I want (example”Chard”) if not then I want the cell to be blank. You could nest an AND function inside Pete's formula to test each separate area in Sheet1! column A... =IF(AND(ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$1:A $5,0)),ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$7:A$12,0)) ),"","your_word") inside the AND function there will need to be a ISNA(MATCH(LEFT(A1,2)*1,Range_Address,0)) for each separate range being tested for the presence of LEFT(A1,2)*1. I have assumed that the values in Sheet1! column A separating the areas to be searched (A6 in the example) contain values that would interfere with the search carried out by the MATCH function. If this is not the case then all you would have to do is extend the lookup_array's address to include as much of Sheet1! column A as required, eg... =IF(ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$1:A$12,0)),"" ,"your_word") Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop smart tags obscuring the cells on my worksheet? | Excel Discussion (Misc queries) | |||
how do i construct a bar in microsoft word? | Excel Discussion (Misc queries) | |||
Smart use of .Activate; .Select; .Copy with Sheets(1) va Worksheet | Excel Discussion (Misc queries) | |||
if else construct | Excel Worksheet Functions | |||
Construct a range in VB | Excel Discussion (Misc queries) |