INDIRECT AND IF STATEMENT
 

I have a cell on worksheet A that includes a data validation list in cell c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4 to go to a
cell within that range and if that cell = 0 (it has a formula in it), to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell on the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the dependent arrows
are pointing to cell I9 on the worksheet I am putting the formula into. What
am I doing wrong or is there a better way to accomplish this. Thanks!

INDIRECT AND IF STATEMENT
 

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom




"Pam M" wrote in message
...
I have a cell on worksheet A that includes a data validation list in cell
c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4 to go to a
cell within that range and if that cell = 0 (it has a formula in it), to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell on the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the dependent arrows
are pointing to cell I9 on the worksheet I am putting the formula into.
What
am I doing wrong or is there a better way to accomplish this. Thanks!

INDIRECT AND IF STATEMENT
 

C4 is a list of named ranges from other worksheets within the workbook.
Depending on which range is selected by the user of the spreadsheet, I want
it to go to that range, see if the value of I9 is zero and return my yes or
no results.

"Peo Sjoblom" wrote:

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom




"Pam M" wrote in message
...
I have a cell on worksheet A that includes a data validation list in cell
c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4 to go to a
cell within that range and if that cell = 0 (it has a formula in it), to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell on the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the dependent arrows
are pointing to cell I9 on the worksheet I am putting the formula into.
What
am I doing wrong or is there a better way to accomplish this. Thanks!

INDIRECT AND IF STATEMENT
 

Do you have more than one named range in C4. I will assume that you don't
because that would be foolish and that you probably have a validation in C4?
However I fail to see what it has anything to do with I9 if the name in C4
is a named range, do you always look what's in I9 in all those named ranges
if so it would be better if you had the different sheet names in C4. If it
is always the ninth cell in the named range that you want to test you could
use

=IF(INDEX(INDIRECT(C4),9)=0,"","Special")


or if you had the sheet names in C4


=IF(INDIRECT("'"&C4&"'!I9")=0,"","Special")



--


Regards,


Peo Sjoblom








"Pam M" wrote in message
...
C4 is a list of named ranges from other worksheets within the workbook.
Depending on which range is selected by the user of the spreadsheet, I
want
it to go to that range, see if the value of I9 is zero and return my yes
or
no results.

"Peo Sjoblom" wrote:

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom




"Pam M" wrote in message
...
I have a cell on worksheet A that includes a data validation list in
cell
c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4 to go
to a
cell within that range and if that cell = 0 (it has a formula in it),
to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell on
the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the dependent
arrows
are pointing to cell I9 on the worksheet I am putting the formula into.
What
am I doing wrong or is there a better way to accomplish this. Thanks!

INDIRECT AND IF STATEMENT
 

yes, I am always looking at I9 in all of those ranges for this formula, but I
cannot use the sheetnames, because I am using the names of the ranges for
other dependent cell formulas for which I need the names of the ranges. So
is there a way to write the formula to reference a specific cell within a
range? In looking at your index formula, I would rewrite it to read
=(indirect(c4),l9 = 0, "", "special")
Does that make sense? But I am still getting an error.

"Peo Sjoblom" wrote:

Do you have more than one named range in C4. I will assume that you don't
because that would be foolish and that you probably have a validation in C4?
However I fail to see what it has anything to do with I9 if the name in C4
is a named range, do you always look what's in I9 in all those named ranges
if so it would be better if you had the different sheet names in C4. If it
is always the ninth cell in the named range that you want to test you could
use

=IF(INDEX(INDIRECT(C4),9)=0,"","Special")


or if you had the sheet names in C4


=IF(INDIRECT("'"&C4&"'!I9")=0,"","Special")



--


Regards,


Peo Sjoblom








"Pam M" wrote in message
...
C4 is a list of named ranges from other worksheets within the workbook.
Depending on which range is selected by the user of the spreadsheet, I
want
it to go to that range, see if the value of I9 is zero and return my yes
or
no results.

"Peo Sjoblom" wrote:

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom




"Pam M" wrote in message
...
I have a cell on worksheet A that includes a data validation list in
cell
c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4 to go
to a
cell within that range and if that cell = 0 (it has a formula in it),
to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell on
the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the dependent
arrows
are pointing to cell I9 on the worksheet I am putting the formula into.
What
am I doing wrong or is there a better way to accomplish this. Thanks!

INDIRECT AND IF STATEMENT
 

Are the named ranges of equal size and if they are in what order is I9? If
they are the same size starting in the same cell but in different sheets
just give us the range and I will give a formula that will work. Meaning
that if the named range for instance is always I1:I15 then you can use the
INDEX formula I gave you. Or if it is A2: K50 then it could be done as well.
There is no way of using it the way you are trying to do but if they are of
equal size then yes it is possible


--


Regards,


Peo Sjoblom


"Pam M" wrote in message
...
yes, I am always looking at I9 in all of those ranges for this formula,
but I
cannot use the sheetnames, because I am using the names of the ranges for
other dependent cell formulas for which I need the names of the ranges.
So
is there a way to write the formula to reference a specific cell within a
range? In looking at your index formula, I would rewrite it to read
=(indirect(c4),l9 = 0, "", "special")
Does that make sense? But I am still getting an error.

"Peo Sjoblom" wrote:

Do you have more than one named range in C4. I will assume that you don't
because that would be foolish and that you probably have a validation in
C4?
However I fail to see what it has anything to do with I9 if the name in
C4
is a named range, do you always look what's in I9 in all those named
ranges
if so it would be better if you had the different sheet names in C4. If
it
is always the ninth cell in the named range that you want to test you
could
use

=IF(INDEX(INDIRECT(C4),9)=0,"","Special")


or if you had the sheet names in C4


=IF(INDIRECT("'"&C4&"'!I9")=0,"","Special")



--


Regards,


Peo Sjoblom








"Pam M" wrote in message
...
C4 is a list of named ranges from other worksheets within the workbook.
Depending on which range is selected by the user of the spreadsheet, I
want
it to go to that range, see if the value of I9 is zero and return my
yes
or
no results.

"Peo Sjoblom" wrote:

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom




"Pam M" wrote in message
...
I have a cell on worksheet A that includes a data validation list in
cell
c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4 to
go
to a
cell within that range and if that cell = 0 (it has a formula in
it),
to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell
on
the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the dependent
arrows
are pointing to cell I9 on the worksheet I am putting the formula
into.
What
am I doing wrong or is there a better way to accomplish this.
Thanks!

INDIRECT AND IF STATEMENT
 

I assumed that since the indirect reference works with my Vlookup formulas
going to those ranges on different sheets that it would work with an IF
statement.

Yes, my named ranges are all the same size. The size on each sheet is
A1:K357 and the cell is always I9, so a formula for that would be
appreciated. Thank you for all of your time. Pam

"Peo Sjoblom" wrote:

Are the named ranges of equal size and if they are in what order is I9? If
they are the same size starting in the same cell but in different sheets
just give us the range and I will give a formula that will work. Meaning
that if the named range for instance is always I1:I15 then you can use the
INDEX formula I gave you. Or if it is A2: K50 then it could be done as well.
There is no way of using it the way you are trying to do but if they are of
equal size then yes it is possible


--


Regards,


Peo Sjoblom


"Pam M" wrote in message
...
yes, I am always looking at I9 in all of those ranges for this formula,
but I
cannot use the sheetnames, because I am using the names of the ranges for
other dependent cell formulas for which I need the names of the ranges.
So
is there a way to write the formula to reference a specific cell within a
range? In looking at your index formula, I would rewrite it to read
=(indirect(c4),l9 = 0, "", "special")
Does that make sense? But I am still getting an error.

"Peo Sjoblom" wrote:

Do you have more than one named range in C4. I will assume that you don't
because that would be foolish and that you probably have a validation in
C4?
However I fail to see what it has anything to do with I9 if the name in
C4
is a named range, do you always look what's in I9 in all those named
ranges
if so it would be better if you had the different sheet names in C4. If
it
is always the ninth cell in the named range that you want to test you
could
use

=IF(INDEX(INDIRECT(C4),9)=0,"","Special")


or if you had the sheet names in C4


=IF(INDIRECT("'"&C4&"'!I9")=0,"","Special")



--


Regards,


Peo Sjoblom








"Pam M" wrote in message
...
C4 is a list of named ranges from other worksheets within the workbook.
Depending on which range is selected by the user of the spreadsheet, I
want
it to go to that range, see if the value of I9 is zero and return my
yes
or
no results.

"Peo Sjoblom" wrote:

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom




"Pam M" wrote in message
...
I have a cell on worksheet A that includes a data validation list in
cell
c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4 to
go
to a
cell within that range and if that cell = 0 (it has a formula in
it),
to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell
on
the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the dependent
arrows
are pointing to cell I9 on the worksheet I am putting the formula
into.
What
am I doing wrong or is there a better way to accomplish this.
Thanks!

INDIRECT AND IF STATEMENT
 

This should work then

=IF(INDEX(INDIRECT(C4),9,9)=0,"","Special")


--


Regards,


Peo Sjoblom


"Pam M" wrote in message
...
I assumed that since the indirect reference works with my Vlookup formulas
going to those ranges on different sheets that it would work with an IF
statement.

Yes, my named ranges are all the same size. The size on each sheet is
A1:K357 and the cell is always I9, so a formula for that would be
appreciated. Thank you for all of your time. Pam

"Peo Sjoblom" wrote:

Are the named ranges of equal size and if they are in what order is I9?
If
they are the same size starting in the same cell but in different sheets
just give us the range and I will give a formula that will work. Meaning
that if the named range for instance is always I1:I15 then you can use
the
INDEX formula I gave you. Or if it is A2: K50 then it could be done as
well.
There is no way of using it the way you are trying to do but if they are
of
equal size then yes it is possible


--


Regards,


Peo Sjoblom


"Pam M" wrote in message
...
yes, I am always looking at I9 in all of those ranges for this formula,
but I
cannot use the sheetnames, because I am using the names of the ranges
for
other dependent cell formulas for which I need the names of the ranges.
So
is there a way to write the formula to reference a specific cell within
a
range? In looking at your index formula, I would rewrite it to read
=(indirect(c4),l9 = 0, "", "special")
Does that make sense? But I am still getting an error.

"Peo Sjoblom" wrote:

Do you have more than one named range in C4. I will assume that you
don't
because that would be foolish and that you probably have a validation
in
C4?
However I fail to see what it has anything to do with I9 if the name
in
C4
is a named range, do you always look what's in I9 in all those named
ranges
if so it would be better if you had the different sheet names in C4.
If
it
is always the ninth cell in the named range that you want to test you
could
use

=IF(INDEX(INDIRECT(C4),9)=0,"","Special")


or if you had the sheet names in C4


=IF(INDIRECT("'"&C4&"'!I9")=0,"","Special")



--


Regards,


Peo Sjoblom








"Pam M" wrote in message
...
C4 is a list of named ranges from other worksheets within the
workbook.
Depending on which range is selected by the user of the spreadsheet,
I
want
it to go to that range, see if the value of I9 is zero and return my
yes
or
no results.

"Peo Sjoblom" wrote:

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom




"Pam M" wrote in message
...
I have a cell on worksheet A that includes a data validation list
in
cell
c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4
to
go
to a
cell within that range and if that cell = 0 (it has a formula in
it),
to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the
cell
on
the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the
dependent
arrows
are pointing to cell I9 on the worksheet I am putting the formula
into.
What
am I doing wrong or is there a better way to accomplish this.
Thanks!

INDIRECT AND IF STATEMENT
 

Peo, that worked perfectly. thank you so much for your help. Pam

"Peo Sjoblom" wrote:

This should work then

=IF(INDEX(INDIRECT(C4),9,9)=0,"","Special")


--


Regards,


Peo Sjoblom


"Pam M" wrote in message
...
I assumed that since the indirect reference works with my Vlookup formulas
going to those ranges on different sheets that it would work with an IF
statement.

Yes, my named ranges are all the same size. The size on each sheet is
A1:K357 and the cell is always I9, so a formula for that would be
appreciated. Thank you for all of your time. Pam

"Peo Sjoblom" wrote:

Are the named ranges of equal size and if they are in what order is I9?
If
they are the same size starting in the same cell but in different sheets
just give us the range and I will give a formula that will work. Meaning
that if the named range for instance is always I1:I15 then you can use
the
INDEX formula I gave you. Or if it is A2: K50 then it could be done as
well.
There is no way of using it the way you are trying to do but if they are
of
equal size then yes it is possible


--


Regards,


Peo Sjoblom


"Pam M" wrote in message
...
yes, I am always looking at I9 in all of those ranges for this formula,
but I
cannot use the sheetnames, because I am using the names of the ranges
for
other dependent cell formulas for which I need the names of the ranges.
So
is there a way to write the formula to reference a specific cell within
a
range? In looking at your index formula, I would rewrite it to read
=(indirect(c4),l9 = 0, "", "special")
Does that make sense? But I am still getting an error.

"Peo Sjoblom" wrote:

Do you have more than one named range in C4. I will assume that you
don't
because that would be foolish and that you probably have a validation
in
C4?
However I fail to see what it has anything to do with I9 if the name
in
C4
is a named range, do you always look what's in I9 in all those named
ranges
if so it would be better if you had the different sheet names in C4.
If
it
is always the ninth cell in the named range that you want to test you
could
use

=IF(INDEX(INDIRECT(C4),9)=0,"","Special")


or if you had the sheet names in C4


=IF(INDIRECT("'"&C4&"'!I9")=0,"","Special")



--


Regards,


Peo Sjoblom








"Pam M" wrote in message
...
C4 is a list of named ranges from other worksheets within the
workbook.
Depending on which range is selected by the user of the spreadsheet,
I
want
it to go to that range, see if the value of I9 is zero and return my
yes
or
no results.

"Peo Sjoblom" wrote:

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom




"Pam M" wrote in message
...
I have a cell on worksheet A that includes a data validation list
in
cell
c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4
to
go
to a
cell within that range and if that cell = 0 (it has a formula in
it),
to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the
cell
on
the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the
dependent
arrows
are pointing to cell I9 on the worksheet I am putting the formula
into.
What
am I doing wrong or is there a better way to accomplish this.
Thanks!