Help with Functions (IF)

[Melissa]

I want to create a worksheet in Excel 2003 that does something like...

=IF('Underground & Kits'!D4 > 0, 'Underground & Kits'!B4:F4', 0

Where if a quantitiy is entered for a line item, then it will return the
line item, price, and quantity. -which are about 4 cells in a row that I want
to carry over into this worksheet. I need this to work not just to carry over
data from one page, but many. I also need to hide cells if blank or Zero once
i have this put together.
When I use the above formula, it keeps returning an error message and I am
not sure how to get what I want. Any help will be greatly appreciated!

 

[StumpedAgain]

You can't fit more than one cell into another cell so in your example you
can't fit B4:F4 into a single cell. You can do something like:

=IF('Underground & Kits'!$D4 > 0, 'Underground & Kits'!B4', 0)

and drag it (copy/paste) to multiple locations.

As for hiding rows, you can either right click on the row (on the far left
on the number) or you can run a quick macro that will check if the row has
anything in it and if it doesn't, it can hide it for you. If you are doing
this on a regular basis, I would consider looking in to writing a macro (or
getting one from these forums). I hope this helps! Let me know if this
doesn't answer your question.

 

[Spiky]

You can't fit more than one cell into another cell so in your example you
can't fit B4:F4 into a single cell. You can do something like:

=IF('Underground & Kits'!$D4 > 0, 'Underground & Kits'!B4', 0)

and drag it (copy/paste) to multiple locations.

As for hiding rows, you can either right click on the row (on the far left
on the number) or you can run a quick macro that will check if the row has
anything in it and if it doesn't, it can hide it for you. If you are doing
this on a regular basis, I would consider looking in to writing a macro (or
getting one from these forums). I hope this helps! Let me know if this
doesn't answer your question.

Perhaps this instead:
=IF(OR('Underground & Kits'!$D4="",'Underground & Kits'!
$D4=0),"",'Underground & Kits'!B4')

 

[TWhizTom]

Melissa:

if(cellref>0,"Line Item = " &
vlookup(itemnumber,rangeofitems,column_num_LineItem)&" Price =
$"&vlookup(itemnumber,rangeofitems,column_num_price)&" etc.... ",0)

or, you could put each if, vlookup, combination into a seperate cell, rather
than concatenating the answer together....