list items where quantity > 0
i've got a list of procedures in my office. during treatment planning i enter the number how many times procedure will be done and the cost is estimated. now i'd like to list those procedures where quantity is > 0 so procedures that will be done. then i'll make a sortable table to order them. i'd like to achieve it without VBA.
my.jpg
Hi ninanoki,
you can use =C2>0 you will get true false value sort them
i hope this is what you want to achieve
Thanks,
A
2 things...
Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
2nd, please describe/show some samples of what your expected outcome would look like?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this...
Let's assume you want the results to appear starting in cell F2 and downwards.
Enter this array formula** in F2:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(C$2:C$5>0,ROW(C$2:C$5)),ROWS(F$2:F2)))))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
that's my excel file with data at the top and table of procedures to do (treatment planning). table allows me to sort items. i manually change numbers in 'order' column. so table displays only item with quantity > 0
Book1.xlsx
Sorry, I don't understand what you're asking here.
in the first tab i'd like to choose procedures to be done and quantity.
and i'd like procedures where quantity > 1 to be displayed on the second tab line by line.
plan.xlsx
Sorry, still don't understand.
Maybe someone else will be able to help.
Hoping that i understand your goal, try this ARRAY formula.
=IFERROR(INDEX(planning!A$5:A$99;SMALL(IF(planning!$C$5:$C$99>0;ROW(planning!A$5:A$99)-4);ROW(planning!A1)));"")
UNMERGE the headers cells...
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
works like a charm.thank you.
You are welcome and thanks for the feed back.
As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks