| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
if statement to return a list of values where answer is true
is it possible to have an if statement or something that could return true
values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 spreadsheet 2 desired result col1 col2 col1 col2 eric 20 eric 20 dave 0 clark 10 clark 10 mike 5 lori 0 stan 0 mike 5 any thoughts? -- Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statement to return a list of values where answer is true
On 5 Aug, 20:25, Eric D wrote:
is it possible to have an if statement or something that could return true values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. *Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 * * * * * * * * * * * * * * * spreadsheet 2 * * * * * * * * * * * * * * * * * * * * * * * * * * * desired result col1 * * * *col2 * * * * * * * * * * * * * * *col1 * * * * col2 eric * * * * *20 * * * * * * * * * * * * * * *eric * * * * * *20 dave * * * * 0 * * * * * * * * * * * * * * * clark * * * * * 10 clark * * * *10 * * * * * * * * * * * * * * * mike * * * * * *5 lori * * * * * *0 stan * * * * *0 mike * * * * *5 any thoughts? -- Eric seel vlookup in help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statement to return a list of values where answer is true
How many rows of data is there on Sheet1?
The numbers in column B, are they always positive numbers? -- Biff Microsoft Excel MVP "Eric D" wrote in message ... is it possible to have an if statement or something that could return true values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 spreadsheet 2 desired result col1 col2 col1 col2 eric 20 eric 20 dave 0 clark 10 clark 10 mike 5 lori 0 stan 0 mike 5 any thoughts? -- Eric |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statement to return a list of values where answer is true
there are 300+ rows and the numbers are not always positive
-- Eric "T. Valko" wrote: How many rows of data is there on Sheet1? The numbers in column B, are they always positive numbers? -- Biff Microsoft Excel MVP "Eric D" wrote in message ... is it possible to have an if statement or something that could return true values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 spreadsheet 2 desired result col1 col2 col1 col2 eric 20 eric 20 dave 0 clark 10 clark 10 mike 5 lori 0 stan 0 mike 5 any thoughts? -- Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statement to return a list of values where answer is true
Assuming every name is unique and there are no empty cells in the $$ column.
Assuming your data is on Sheet1A2:B300 In the formula... Name: refers to Sheet1!A$2:A$300 Amount: refers to Sheet1!B$2:B$300 On Sheet2 enter this formula in A1: =COUNTIF(Amount,"<0") This will return the number of records that meet the criteria. Enter this array formula** in A2 to get the names: =IF(ROWS(A$2:A2)A$1,"",INDEX(Name,SMALL(IF(Amount <0,ROW(Name)),ROWS(A$2:A2))-MIN(ROW(Name))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down until you get blanks. If you will need to do this often and the number of records will vary then you'll have to copy the formula to a number of cells that you know will be enough to get all the results. You're the only one that knows how many that will be! Enter this formula in B2 to get the amounts: =IF(A2="","",SUMIF(Name,A2,Amount)) Copy down to the same number of cells as the formula in column A. -- Biff Microsoft Excel MVP "Eric D" wrote in message ... there are 300+ rows and the numbers are not always positive -- Eric "T. Valko" wrote: How many rows of data is there on Sheet1? The numbers in column B, are they always positive numbers? -- Biff Microsoft Excel MVP "Eric D" wrote in message ... is it possible to have an if statement or something that could return true values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 spreadsheet 2 desired result col1 col2 col1 col2 eric 20 eric 20 dave 0 clark 10 clark 10 mike 5 lori 0 stan 0 mike 5 any thoughts? -- Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statement to return a list of values where answer is true
ok thank you so much ... i tried these formula's and they seem to work, but
the long formula returns a #VALUE but when i click on the "fx" beside the formula bar to look at it, it says its returning a value that would be correct but on the spreadsheet it is saying #VALUE. did i do something wrong? -- Eric "T. Valko" wrote: Assuming every name is unique and there are no empty cells in the $$ column. Assuming your data is on Sheet1A2:B300 In the formula... Name: refers to Sheet1!A$2:A$300 Amount: refers to Sheet1!B$2:B$300 On Sheet2 enter this formula in A1: =COUNTIF(Amount,"<0") This will return the number of records that meet the criteria. Enter this array formula** in A2 to get the names: =IF(ROWS(A$2:A2)A$1,"",INDEX(Name,SMALL(IF(Amount <0,ROW(Name)),ROWS(A$2:A2))-MIN(ROW(Name))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down until you get blanks. If you will need to do this often and the number of records will vary then you'll have to copy the formula to a number of cells that you know will be enough to get all the results. You're the only one that knows how many that will be! Enter this formula in B2 to get the amounts: =IF(A2="","",SUMIF(Name,A2,Amount)) Copy down to the same number of cells as the formula in column A. -- Biff Microsoft Excel MVP "Eric D" wrote in message ... there are 300+ rows and the numbers are not always positive -- Eric "T. Valko" wrote: How many rows of data is there on Sheet1? The numbers in column B, are they always positive numbers? -- Biff Microsoft Excel MVP "Eric D" wrote in message ... is it possible to have an if statement or something that could return true values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 spreadsheet 2 desired result col1 col2 col1 col2 eric 20 eric 20 dave 0 clark 10 clark 10 mike 5 lori 0 stan 0 mike 5 any thoughts? -- Eric |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statement to return a list of values where answer is true
Did you remember the Control Shift Enter?
-- David Biddulph "Eric D" wrote in message ... ok thank you so much ... i tried these formula's and they seem to work, but the long formula returns a #VALUE but when i click on the "fx" beside the formula bar to look at it, it says its returning a value that would be correct but on the spreadsheet it is saying #VALUE. did i do something wrong? -- Eric "T. Valko" wrote: Assuming every name is unique and there are no empty cells in the $$ column. Assuming your data is on Sheet1A2:B300 In the formula... Name: refers to Sheet1!A$2:A$300 Amount: refers to Sheet1!B$2:B$300 On Sheet2 enter this formula in A1: =COUNTIF(Amount,"<0") This will return the number of records that meet the criteria. Enter this array formula** in A2 to get the names: =IF(ROWS(A$2:A2)A$1,"",INDEX(Name,SMALL(IF(Amount <0,ROW(Name)),ROWS(A$2:A2))-MIN(ROW(Name))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down until you get blanks. If you will need to do this often and the number of records will vary then you'll have to copy the formula to a number of cells that you know will be enough to get all the results. You're the only one that knows how many that will be! Enter this formula in B2 to get the amounts: =IF(A2="","",SUMIF(Name,A2,Amount)) Copy down to the same number of cells as the formula in column A. -- Biff Microsoft Excel MVP "Eric D" wrote in message ... there are 300+ rows and the numbers are not always positive -- Eric "T. Valko" wrote: How many rows of data is there on Sheet1? The numbers in column B, are they always positive numbers? -- Biff Microsoft Excel MVP "Eric D" wrote in message ... is it possible to have an if statement or something that could return true values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 spreadsheet 2 desired result col1 col2 col1 col2 eric 20 eric 20 dave 0 clark 10 clark 10 mike 5 lori 0 stan 0 mike 5 any thoughts? -- Eric |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statement to return a list of values where answer is true
ok i got it to work ... whooo hooo ... thank you
now to make it even harder ... is this possible if i am pulling from 2 different spreadsheets. i.e. i have sheet 1 and sheet 2 both with names and amounts and i want the info to show up on sheet 3 ... is that possible? -- Eric "David Biddulph" wrote: Did you remember the Control Shift Enter? -- David Biddulph "Eric D" wrote in message ... ok thank you so much ... i tried these formula's and they seem to work, but the long formula returns a #VALUE but when i click on the "fx" beside the formula bar to look at it, it says its returning a value that would be correct but on the spreadsheet it is saying #VALUE. did i do something wrong? -- Eric "T. Valko" wrote: Assuming every name is unique and there are no empty cells in the $$ column. Assuming your data is on Sheet1A2:B300 In the formula... Name: refers to Sheet1!A$2:A$300 Amount: refers to Sheet1!B$2:B$300 On Sheet2 enter this formula in A1: =COUNTIF(Amount,"<0") This will return the number of records that meet the criteria. Enter this array formula** in A2 to get the names: =IF(ROWS(A$2:A2)A$1,"",INDEX(Name,SMALL(IF(Amount <0,ROW(Name)),ROWS(A$2:A2))-MIN(ROW(Name))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down until you get blanks. If you will need to do this often and the number of records will vary then you'll have to copy the formula to a number of cells that you know will be enough to get all the results. You're the only one that knows how many that will be! Enter this formula in B2 to get the amounts: =IF(A2="","",SUMIF(Name,A2,Amount)) Copy down to the same number of cells as the formula in column A. -- Biff Microsoft Excel MVP "Eric D" wrote in message ... there are 300+ rows and the numbers are not always positive -- Eric "T. Valko" wrote: How many rows of data is there on Sheet1? The numbers in column B, are they always positive numbers? -- Biff Microsoft Excel MVP "Eric D" wrote in message ... is it possible to have an if statement or something that could return true values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 spreadsheet 2 desired result col1 col2 col1 col2 eric 20 eric 20 dave 0 clark 10 clark 10 mike 5 lori 0 stan 0 mike 5 any thoughts? -- Eric |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statement to return a list of values where answer is true
I don't think you'll be able to pull from 2 sheets into one list.
You'll have to use side-by-side lists. One list for Sheet1 and the other list for Sheet2. -- Biff Microsoft Excel MVP "Eric D" wrote in message ... ok i got it to work ... whooo hooo ... thank you now to make it even harder ... is this possible if i am pulling from 2 different spreadsheets. i.e. i have sheet 1 and sheet 2 both with names and amounts and i want the info to show up on sheet 3 ... is that possible? -- Eric "David Biddulph" wrote: Did you remember the Control Shift Enter? -- David Biddulph "Eric D" wrote in message ... ok thank you so much ... i tried these formula's and they seem to work, but the long formula returns a #VALUE but when i click on the "fx" beside the formula bar to look at it, it says its returning a value that would be correct but on the spreadsheet it is saying #VALUE. did i do something wrong? -- Eric "T. Valko" wrote: Assuming every name is unique and there are no empty cells in the $$ column. Assuming your data is on Sheet1A2:B300 In the formula... Name: refers to Sheet1!A$2:A$300 Amount: refers to Sheet1!B$2:B$300 On Sheet2 enter this formula in A1: =COUNTIF(Amount,"<0") This will return the number of records that meet the criteria. Enter this array formula** in A2 to get the names: =IF(ROWS(A$2:A2)A$1,"",INDEX(Name,SMALL(IF(Amount <0,ROW(Name)),ROWS(A$2:A2))-MIN(ROW(Name))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down until you get blanks. If you will need to do this often and the number of records will vary then you'll have to copy the formula to a number of cells that you know will be enough to get all the results. You're the only one that knows how many that will be! Enter this formula in B2 to get the amounts: =IF(A2="","",SUMIF(Name,A2,Amount)) Copy down to the same number of cells as the formula in column A. -- Biff Microsoft Excel MVP "Eric D" wrote in message ... there are 300+ rows and the numbers are not always positive -- Eric "T. Valko" wrote: How many rows of data is there on Sheet1? The numbers in column B, are they always positive numbers? -- Biff Microsoft Excel MVP "Eric D" wrote in message ... is it possible to have an if statement or something that could return true values in a list? ex. on a separate tab i have a sheet that has 2 columns in column 1 is names and in column 2 is $$. Is there a formula i can enter on a separate sheet so that it gives the answer but without spaces. spreadsheet 1 spreadsheet 2 desired result col1 col2 col1 col2 eric 20 eric 20 dave 0 clark 10 clark 10 mike 5 lori 0 stan 0 mike 5 any thoughts? -- Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup to return true() values only and list | Excel Discussion (Misc queries) | |||
| only return true values | Excel Discussion (Misc queries) | |||
| if multiple values same/others different, return true | Excel Discussion (Misc queries) | |||
| Return blank cell if 'find' statement not true | Excel Worksheet Functions | |||
| How do I make the true return a drop down list in the IF function? | Excel Worksheet Functions | |||
Linear Mode
