• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting problem while using Indirect & data validation

P

paradise

Member
Hi,
I have a data in sheet 'pgwise' and in sheet2 with data validation in D1, result is displaying in D2:D.But I want to display without leaving spaces in D2,D3,D4 and should not display the result in between which is currently displaying in D12 & D13 while selecting E in D1 of Sheet2.What I want is to sort and arrange data which I have shown in Sheet2!I3:I4 as manual typing.

Can anyone can redefine my existing formula ?

With Best Rgds,
Suresh
 

Attachments

Hi paradise,

Try this in E2 and drag down, press ctrl+shift+enter to execute. Hope that helps.

Code: 
=INDEX(pgwise!$C$2:$C$17,SMALL(IF(pgwise!$D$2:$D$17=Sheet2!$D$1,ROW(pgwise!$D$2:$D$17)),ROW(A1))-1,0)
 
Thanks for the reply.
You formula is correct if I do not use Indirect function.
The formula you suggested should be inserted in my below formula but should be arranged without leaving any spaces.
=IF(INDIRECT("pgwise"&"!"&"D"&ROW($D2:$D2))=$D$1,INDIRECT("pgwise"&"!"&"C"&ROW($C2:$C2)),"")

so that instead of displaying blank it display the result right starting from D2 onwards rather than in middle which is currently existing in my workbook.

This is so becoz,I have number of sheets & by the help of this indirect function I am able to get the desired result but not arranged or sorted orderly which you have seen in above uploaded workbook.

I will be eagerly waiting for the reply.
With Best Rgds,
Suresh
 
Hi,
Thanks for the reply.
I could not understand what you did.I have seen in Sheet2 in Column D2 there is no any modification in the formula.When I select "A" from D1 of Sheet2,it display from D2 onwards which I actually need & nice without leaving spaces.But when I select B,it display from D6 onwards,I want this to be displayed on D2 onwards rather than from D6 of Sheet2.This things I need to be added to my existing formula which is in D2 onwards.But how this I do not know.
You should have to simply work on it.I would be very much thankful in this regard.

With Best Rgds,
Suresh
 
Hello Suresh,

In D2 use this Array Formula, with CTRL+SHIFT+ENTER

Code: 
=IFERROR(INDEX(INDIRECT("'pgwise'!C:C"),SMALL(IF(INDIRECT("'pgwise'!D1:D1000")=D$1,ROW(D$1:D$1000)),ROWS(D$2:D2))),"")

Then copy down.
 
Hi Haseeb,
Let me first of all tell you, very very much thanks to you.

I have used the formula that you gave and modified accordingly.I got the exact result what I wanted.
Very much thanks to you again.You did a splendid work.

I have slightly changed the format of data and used your formula here in Sheet2.There was previously only one data validation applied,now I have applied 3 more data validation which is in C3:F3 of Sheet2.Currently the formula you supplied has been applied in B5 linked with data validation of C3 of sheet2.Here,I want to link with all C3,D3,E3 and F3.Now the formula in B5 should further be modified in such a way that there should not be any duplicates in B5,B6,B7,B8,........ when the data validation in all C3,D3,E3,F3 of sheet2 is A,B,C,D.

I have enclosed a workbook for your kind reference.
With Best Rgds,
Suresh

P.S:I also thanks to Faseeh that has also helped me in other way.
 

Attachments

Dear paradise,

I might be wrong, but i am unable to find how the follwing formula in my second last post gives a different result then that of Haseeb:

=INDEX(pgwise!$C$2:$C$17,SMALL(IF(pgwise!$D$2:$D$17=Sheet2!$D$1,ROW(pgwise!$D$2:$D$17)),ROW(A1))-1,0)
Click to expand...

..and my uploaded file takes care of multiple sheets as you were interested in using INDIRECT() so... I can't pick your point exactly. :)

Edit: Can you give the desired output for the last uploaded file by you? It will be really helpful to understand what you want.
 
Suresh, as Faseeh said, not sure about your output. Assuming you like to get an unique names, if matching selected drop downs.

If so, this should work.

Array Formula in B5, then copy down.

Code: 
=IFERROR(INDEX(INDIRECT(C$1&"!B1:B1000"),MATCH(2,ISNUMBER(MATCH(INDIRECT(C$1&"!C1:C1000"),C$3:F$3,0))+ISNA(MATCH(INDIRECT(C$1&"!B1:B1000"),B$4:B4,0)),0)),"")
 
Hi Faseeh,

No,you did not understand what I mean to say.The formula you suggested worked very fine in E2.The workbook is enclosed for your kind reference.Since,I want the same formula to be clubbed in the formula which I built using Indirect function and not in separate column.And for this Haseeb helped.I have also enclosed next workbook with the formula that Haseeb A has provided in post #8.Since this is the sample worksheet and a part of the formula only.I have to go a long way.And you both of you has helped me a lot.


Lastly,the formula works well of Haseeb in workbook "Sorting_modified.xlsx", can it further be arranged alphabetically.If it does,then do let me know.

Thanks for the help.

With Best Rgds,
Suresh
 

Attachments

Hi Haseeb,

Thanks again,you formula worked what I wanted.In the foregoing post ,I have enclosed the workbook 'Sorting_modified.xlsx'.

Lastly,the formula works well, can it further be arranged alphabetically.If it does,then do let me know.

However for this time my problem is solved even if it not arranged alphabetically.I have a lot to learn from you and Faseeh.

With Best Rgds,
Suresh
 
Hi paradise,

I was not complaining actually, :) just unable to understand the exact requirement. This might not be the best solution but still you can get the results, add a helper column to your sheet2 and enter this formula press ctrl+shift+enter to execute. This will sort the list generated by Haseeb's formula:

Code: 
=INDEX($B$5:$B$16,MATCH(ROWS($C$4:C4),COUNTIF($B$5:$B$16,"<="&$B$5:$B$16),0))

Refrence: http://www.mrexcel.com/forum/excel-questions/721866-formula-alphabetically-sort-text.html
 
Hi Faseeh,

Thanks for the reply.Indeed your formula too have helped me in other cases & this one too.Becoz of you and other forum contributor support,I and other people could have been able to solve their problems.

I shall require your help whenever I face any problems in excel & will continue posting in that case.

With Best Rgds,
Suresh
 
You must log in or register to reply here.
Back
Top