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

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

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

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

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

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

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

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

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