# IF, SUM and INDIRECT Help

H

#### Harry Stevens

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

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

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

&nbsp; 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.&nbsp; Can someone spot something that I have overlooked?

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

Harry

R

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

Harry

D

#### 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.

H

#### Harry Stevens

Roger said:
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"))

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

Thanks

H

#### Harry Stevens

Dave said:
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,