how to have existing formula change by entering numbers into two separate cells

[drooperman]

I was wondering if someone could please help me. I have a sheet with numbers data that I am calculating through various formulas such as sums and ranges and averages and sort functions etc. My problem at the moment is that I have for instance in Column Y a set of numbers that is the main Numbers references that I entered in manually such as 1 to 10 or 4 to 19 or 19 to 29 etc. this list varies on different sheets and will never change after I have entered them the first time, as they are the main numbers. then I have the data in column Z that will change as I enter new figures into the chosen cells on other sheets. lets say for instance on sheet one these main numbers in column Y are 1 to 10. Next to each of these 1 to 10 numbers in column Z is the results of my sorting or range or xlookup formulas that I have asked for that is delivered to me. Now out of this data in column Z (next to the numbers 1 to 10), I currently have a sort formula that sorts this data into top 3 numbers for me. this sort formula is looking throughout all the 1 to 10 numbers and their accompanying data in column Z to find the top 3 numbers and then shows me the top three numbers out of 1 to 10 with its accompanying data next to it. [=SORT(FILTER(V197:W205,W197:W205>=LARGE(W197:W205, 5)), 2, -1)] The Problem I face is that sometimes this sort formula gotta be between main numbers 2 and 8 or 1 and 7 or between 3 and 9, it doesn't stay always just 1 to 10, which then requires me to manually edit the formula every time which is a lengthy process since there are over 40 columns of such data. Is there a way that I can make this formula work by me entering the start number (2) and end number (8) into two different cells which will tell this Sort formula where to start and end the top 3 numbers search? thanks in advance for all your efforts.

 

[drooperman]

I was wondering if someone could please help me. I have a sheet with numbers data that I am calculating through various formulas such as sums and ranges and averages and sort functions etc. My problem at the moment is that I have for instance in Column Y a set of numbers that is the main Numbers references that I entered in manually such as 1 to 10 or 4 to 19 or 19 to 29 etc. this list varies on different sheets and will never change after I have entered them the first time, as they are the main numbers. then I have the data in column Z that will change as I enter new figures into the chosen cells on other sheets. lets say for instance on sheet one these main numbers in column Y are 1 to 10. Next to each of these 1 to 10 numbers in column Z is the results of my sorting or range or xlookup formulas that I have asked for that is delivered to me. Now out of this data in column Z (next to the numbers 1 to 10), I currently have a sort formula that sorts this data into top 3 numbers for me. this sort formula is looking throughout all the 1 to 10 numbers and their accompanying data in column Z to find the top 3 numbers and then shows me the top three numbers out of 1 to 10 with its accompanying data next to it. [=SORT(FILTER(V197:W205,W197:W205>=LARGE(W197:W205, 5)), 2, -1)] The Problem I face is that sometimes this sort formula gotta be between main numbers 2 and 8 or 1 and 7 or between 3 and 9, it doesn't stay always just 1 to 10, which then requires me to manually edit the formula every time which is a lengthy process since there are over 40 columns of such data. Is there a way that I can make this formula work by me entering the start number (2) and end number (8) into two different cells which will tell this Sort formula where to start and end the top 3 numbers search? thanks in advance for all your efforts.

So just to clarify..

I sometimes only need the sort top 3 results between numbers 2 to 8 I dont always need the top 3 out of all 10 numbers hence why I want to specify out of which numbers I want the top 3 of. I hope this clarifies.

 

[Peter_SSs]

Welcome to the MrExcel board!

Hard to follow your written description since you mention a set of numbers in column Y but your current formula does not refer to column Y.

Could you post a small set of dummy data with XL2BB and include you current formula and describe again in relation to the sample data just what you want different?

 

[drooperman]

Welcome to the MrExcel board!

Hard to follow your written description since you mention a set of numbers in column Y but your current formula does not refer to column Y.

Could you post a small set of dummy data with XL2BB and include you current formula and describe again in relation to the sample data just what you want different?

Hi Peter,

Apologies for that.

was using the Y and Z as an example and then gave the exact formula on one of the sheets. That probably wont help you to help me.

here is the exact Y / Z column sort formula.

=SORT(FILTER(Y26:Z35,Z26:Z35>=LARGE(Z26:Z35, 3)), 2, -1)

Does this help?

 

[Peter_SSs]

here is the exact Y / Z column sort formula.

=SORT(FILTER(Y26:Z35,Z26:Z35>=LARGE(Z26:Z35, 3)), 2, -1)

That is what you have, not what you want.

You talked about entering a start number and an end number. I can't see how that relates to the formula that you have provided.
I thought that some sample data & expected results with XL2BB and description explaining those two inputs in relation to the sample would help.

 

[drooperman]

Thanks peter,

I am trying to get the XL2BB to work but I am new to this so I am not sure how to do this.. I installed the xl2bb and can see the tab on my excel but from here I have no idea yet
how to get the sheet over to this platform now?

 

[drooperman]

That is what you have, not what you want.

You talked about entering a start number and an end number. I can't see how that relates to the formula that you have provided.
I thought that some sample data & expected results with XL2BB and description explaining those two inputs in relation to the sample would help.

 

[Peter_SSs]

. I installed the xl2bb and can see the tab on my excel but from here I have no idea yet
how to get the sheet over to this platform now?

• Select the range on your sheet that you want to show here.
• Click 'Mini sheet' on the xl2bb toolbar,
• Come to your post and Paste.
• It will be a lot of code, but you can click 'Preview' at the top right of the Reply window to see what it will look like when you ..
• .. finally click 'Post reply'

 

[Peter_SSs]

See if this does what you want.

22 02 27.xlsm
	Y	Z	AA	AB	AC
21			From	2
22			To	8
23			Top No	3
24
25
26	1	3		2	9
27	2	9		7	8
28	3	5		6	7
29	4	4
30	5	6
31	6	7
32	7	8
33	8	2
34	9	1
35	10	10
36
Sort

Cell Formulas
Range	Formula
AB26:AC28	AB26	=INDEX(SORT(FILTER(Y26:Z35,(Y26:Y35>=AB21)*(Y26:Y35<=AB22),""),2,-1),SEQUENCE(AB23),{1,2})
Dynamic array formulas.

 

[drooperman]

See if this does what you want.

22 02 27.xlsm
	Y	Z	AA	AB	AC
21			From	2
22			To	8
23			Top No	3
24
25
26	1	3		2	9
27	2	9		7	8
28	3	5		6	7
29	4	4
30	5	6
31	6	7
32	7	8
33	8	2
34	9	1
35	10	10
36
Sort

Cell Formulas
Range	Formula
AB26:AC28	AB26	=INDEX(SORT(FILTER(Y26:Z35,(Y26:Y35>=AB21)*(Y26:Y35<=AB22),""),2,-1),SEQUENCE(AB23),{1,2})
Dynamic array formulas.

YES SIR.. that did the job...

THANK YOU THANK YOU THANK YOU.....