IF, SUM and INDIRECT Help

[Harry Stevens]

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"))


Thanks in Advance.

Harry

 

[Roger Govier]

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


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"))


Thanks in Advance.

Harry

 

[Dave Peterson]

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]

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" <[email protected]
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"))


Thanks in Advance.

Harry

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

Thanks

 

[Harry Stevens]

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.

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