Excel Spreadsheet Formulas

robert_harper_2000

Hello

I have a spreadsheet showing products for my shop.

I have the following
-Code
-Desc
-Price; Trade & Retail

Now what I want is for the next tab to show the code and desc with qty; but only if there is a qty. For the fields with nothing in I want the spreadsheet to ignore it on the next tab but for the qtys with at least one or more I want to appear showing the above details.

I think it would have something to do with the what IF commands but I can't get round these formulas.... HELP!
Thanks

stevemcol

Try

=IF((A1=""),"",CONCATENATE(B1," ",C1," ",D1," ",E1))

I've done this quickly and it's just a general idea. A1 would be the quantity, then B1, C1, D1, E1 would be code, desc, etc.

Copy and past and give it a try.

robert_harper_2000

wow quite impressive! However... although it does work the problem is that the text stays even if there is a zero.... I want the text to dissappear if no qty is there...


Thanks though

stevemcol

or

=IF((A1<1),"",CONCATENATE(B1," ",C1," ",D1," ",E1))

stevemcol

oh well. I thought it was ok and it does seem to work!

robert_harper_2000

Perfect!!!
Thank you
Thank you
Thank you

robert_harper_2000

OK almost there.... lol Lastly ( I Promise ) I would like to link it to another worksheet or tab. Also would it be possible to have it so that for every order that has a zero or is left clear it would dissappear ( as it does now (thanks) ) but could I have it so that it automatically moves all visable text up so that when it is e-mail it will e-mail neatly...

If you know what I mean?
Thanks

GuidonTheBee

If you want the rows to disappear completely it I'd either set up some VBA (if you know a bit) to strip down & hide all the unneeded rows in your sheet or use an array formula. The VBA macro would look something like:

Sub HideRows()

Range("A1").Select
Counter = 0
Do While Range("A1").Offset(Counter, 0).Value <> ""
With Range("A1").Offset(Counter, 3)
If .Value = 0 Then
.Rows.Hidden = True
End If
End With
Counter = Counter + 1
Loop
End Sub

and the array formula would look something like

=IF(ROW()-ROW(Sheet1!$D$2:$D$9)+1>ROWS(Sheet1!$B$2:$B$9)-COUNTIF(Sheet1!$B$2:$B$9,"=0"),"",INDIRECT(ADDRESS(SMALL((IF(Sheet1!$B$2:$B$9<>0,ROW(Sheet1!$B$2:$B$9),ROW()+ROWS(Sheet1!$B$2:$B$9))),ROW()-ROW(Sheet1!$D$2:$D$9)+1),COLUMN(Sheet1!$B$2:$B$9)-1,4)))

to return assuming you had Item Codes in column A & Counts in Column B & you were putting your 'filtered list' in column D (Rows 1 - 9). Obviously you'd need to change your references for the second sheet. I've got a working example if thats easier as its a pretty nasty formula

robert_harper_2000

OK... I have little well no experience of VB but I will spend time going over this and try and get my head round this. I would appreciate the working example you mentioned. If you pm me your e-mail I'll reply.

Again many thanks, I'll keep you posted.


Or//

How do I link the
=IF((A1<1),"",CONCATENATE(B1," ",C1," ",D1," ",E1))

To the next worksheet??

(I'm still working on the VB thanks)

stevemcol

Not quite sure what you're after so I'll hazard a guess that you want to interrogate sheet 1 and display the reuslt in sheet 2. In sheet 2, try:

=IF((sheet1!A1<1),"",CONCATENATE(sheet1!B1," ",sheet1!C1," ",sheet1!D1," ",sheet1!E1))

robert_harper_2000

Ey that works a treat! Thanks everyone. To solve the problem of the huge gaps between the orders I have done what I think someone else suggested and filtered it... it's not automatic but it works!

Is it possible to e-mail the page directly from the worksheet?



Actually I think I'm being lazy, I think I could make a macro for this sort of thing

THANK YOU EVERYONE