IF, SUM and INDIRECT Help
 

To All,
  I have searched the help system and googled the net and I still cannot figure out what is happening.  I have to formula that pulls data from other worksheets depending on the date entered on my "Income Stmt" worksheet.

  The first formula works if I drop the LEFT test, but I need it because the cell may be blank or contain text.  I need to use the trim because I have a couple items that have leading spaces.  As it is, it currently returns blank not matter what the cell contains on my Budget worksheet.  Can anyone spot the problem?

=IF(TRIM(LEFT(INDIRECT("'Budget"&RIGHT(YEAR($I $1),2)&"'!A36"),1))<9, INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'! A36"), "")


  This formula is dependent on what is returned in the above formula and I need to trap for a blank cell, but what I have now does not work.  Can someone spot something that I have overlooked?

=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)& amp;"'!A:A"),$B36 <> "",INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)& "'!D:D"))


Thanks in Advance.

Harry

IF, SUM and INDIRECT Help
 

Hi Harry

Because you are taking LEFT( ,1) of the value, it is returning a text value, so you need to have the comparison as being <"9"

--
Regards

Roger Govier


"Harry Stevens" wrote in message ...
To All,
I have searched the help system and googled the net and I still cannot figure out what is happening. I have to formula that pulls data from other worksheets depending on the date entered on my "Income Stmt" worksheet.

The first formula works if I drop the LEFT test, but I need it because the cell may be blank or contain text. I need to use the trim because I have a couple items that have leading spaces. As it is, it currently returns blank not matter what the cell contains on my Budget worksheet. Can anyone spot the problem?

=IF(TRIM(LEFT(INDIRECT("'Budget"&RIGHT(YEAR($I$1), 2)&"'!A36"),1))<9, INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A36"), "")


This formula is dependent on what is returned in the above formula and I need to trap for a blank cell, but what I have now does not work. Can someone spot something that I have overlooked?

=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A :A"),$B36 < "",INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!D:D") )


Thanks in Advance.

Harry

IF, SUM and INDIRECT Help
 

First, this is a text only newsgroup--not attachments and no html. (That light
blue font makes it difficult for me to read your post--I imagine it's difficult
for others, too.)

One problem is that =trim() and =left() both return text. So I would think that
you would want to use <"9".

But there may be better ways. It looks like you want to bring back only
numbers--if it's not a number (text or empty), then show "".

Is that correct?

=IF(ISNUMBER(-INDIRECT("'Budget"&TEXT($I$1,"yy")&"'!A36")),
--INDIRECT("'Budget"&TEXT($I$1,"yy")&"'!A36"),"")

If you really meant to just hide the 0's that are returned when the cell is
empty:

=IF(INDIRECT("'Budget"&TEXT($I$1,"yy")&"'!A36")="" ,"",
INDIRECT("'Budget"&TEXT($I$1,"yy")&"'!A36"))

(I changed the way to get the last two digits of the year. It made it easier
for me.)

I'm not sure what you're doing in the second formula.



Harry Stevens wrote:

To All,
I have searched the help system and googled the net and I still cannot
figure out what is happening. I have to formula that pulls data from other
worksheets depending on the date entered on my "Income Stmt" worksheet.

The first formula works if I drop the LEFT test, but I need it because the
cell may be blank or contain text. I need to use the trim because I have a
couple items that have leading spaces. As it is, it currently returns blank
not matter what the cell contains on my Budget worksheet. Can anyone spot the
problem?

=IF(TRIM(LEFT(INDIRECT("'Budget"&RIGHT(YEAR($I$1), 2)&"'!A36"),1))<9,
INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A36"), "")

This formula is dependent on what is returned in the above formula and I
need to trap for a blank cell, but what I have now does not work. Can someone
spot something that I have overlooked?

=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A :A"),$B36 <
"",INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!D:D") )

Thanks in Advance.

Harry

--

Dave Peterson

IF, SUM and INDIRECT Help
 

Roger Govier wrote:
Hi Harry

Because you are taking LEFT( ,1) of the value, it is returning a text
value, so you need to have the comparison as being <"9"

--
Regards

Roger Govier



"Harry Stevens"
wrote in message
...
To All,
I have searched the help system and googled the net and I still
cannot figure out what is happening. I have to formula that pulls
data from other worksheets depending on the date entered on my
"Income Stmt" worksheet.

The first formula works if I drop the LEFT test, but I need it
because the cell may be blank or contain text. I need to use the
trim because I have a couple items that have leading spaces. As it
is, it currently returns blank not matter what the cell contains on
my Budget worksheet. Can anyone spot the problem?

=IF(TRIM(LEFT(INDIRECT("'Budget"&RIGHT(YEAR($I$1), 2)&"'!A36"),1))<9,
INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A36"), "")


This formula is dependent on what is returned in the above formula
and I need to trap for a blank cell, but what I have now does not
work. Can someone spot something that I have overlooked?

=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A :A"),$B36 <
"",INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!D:D") )


Thanks in Advance.

Harry
Roger,
That was the ticket. I knew it had to be something simple.

Thanks

IF, SUM and INDIRECT Help
 

Dave Peterson wrote:
First, this is a text only newsgroup--not attachments and no html. (That light
blue font makes it difficult for me to read your post--I imagine it's difficult
for others, too.)

One problem is that =trim() and =left() both return text. So I would think that
you would want to use <"9".

But there may be better ways. It looks like you want to bring back only
numbers--if it's not a number (text or empty), then show "".

Is that correct?

=IF(ISNUMBER(-INDIRECT("'Budget"&TEXT($I$1,"yy")&"'!A36")),
--INDIRECT("'Budget"&TEXT($I$1,"yy")&"'!A36"),"")

If you really meant to just hide the 0's that are returned when the cell is
empty:

=IF(INDIRECT("'Budget"&TEXT($I$1,"yy")&"'!A36")="" ,"",
INDIRECT("'Budget"&TEXT($I$1,"yy")&"'!A36"))

(I changed the way to get the last two digits of the year. It made it easier
for me.)

I'm not sure what you're doing in the second formula.



Harry Stevens wrote:

To All,
I have searched the help system and googled the net and I still cannot
figure out what is happening. I have to formula that pulls data from other
worksheets depending on the date entered on my "Income Stmt" worksheet.

The first formula works if I drop the LEFT test, but I need it because the
cell may be blank or contain text. I need to use the trim because I have a
couple items that have leading spaces. As it is, it currently returns blank
not matter what the cell contains on my Budget worksheet. Can anyone spot the
problem?

=IF(TRIM(LEFT(INDIRECT("'Budget"&RIGHT(YEAR($I$1), 2)&"'!A36"),1))<9,
INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A36"), "")

This formula is dependent on what is returned in the above formula and I
need to trap for a blank cell, but what I have now does not work. Can someone
spot something that I have overlooked?

=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A :A"),$B36 <
"",INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!D:D") )

Thanks in Advance.

Harry

Dave,
Thanks...sorry about the html post. Anyway your explanation wes spot on and your and
Roger's suggestion made the second question a moot point.

Thanks