LinkBack URL
About LinkBacks
Hello,
Is there a way to have the drop down list on I5 based on name range 'NumberList" with formula OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A$1:$A$999)-1,1)
in the attached file to show only items with the value on E =0 ?
Hello,
Is there a way to have the drop down list on I5 based on name range 'NumberList" with formula OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A$1:$A$999)-1,1)
in the attached file to show only items with the value on E =0 ?
Last edited by Borntobebad; 06-02-2021 at 06:46 PM.
Create "Not Delivered" list
in J1
=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,ROW($A$2:$A$8)-1/($E$2:$E$8=""),ROWS($1:1))),"")
Copy down
Change "Numberlist" to reference the above
=OFFSET(Sheet1!$J$1,0,,COUNTA(Sheet1!$J$1:$J$994)-1,1)
Thank you very much!! It does exactly what I need. You are the best.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
John,
I ran into a problem here.
I am trying to place the formula you provided into the real spreadsheet, but I can not make it to work.
The parameters changed a little in the real spreadsheet. The place to look for empty is I instead E and I am placing your formula on Column AD.
Unfortunately I get blank cells when I do so.
Please help.
Attached is the real spreadsheet.
=IFERROR(INDEX($A$2:$A$50,AGGREGATE(15,6,ROW($A$2:$A$50)-1/($I$2:$I$50=0),ROWS($1:2))),"")
Original file had blanks not zero
Yeay!!
It is working.
John,
Everything is working great with the code, but I have another question.
Is there a way to skip duplicate entries?
For example:
If Item 123456 is entered twice by mistake on column a can it be skipped on the formula to only show one?
I was going to suggest setting up another column, say AD, with a formula like =IF(COUNTIFS(A$2:A2,A2)>1,"",A2) so that duplicate values would not be shown.
The index formula could then be modified to read: =IFERROR(INDEX($AD$2:$AD$50,AGGREGATE(15,6,ROW($A$2:$A$50)-1/($I$2:$I$50=0),ROWS($1:2))),"")
But it occurs to me that the first instance of the duplicated number might correspond to a value of 1 in column I while the second, which would not be shown, corresponds to zero.
It might be better to apply conditional formatting to column A that highlights duplicates so that mistakes can be corrected.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you!
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks