automatically change the range of cells that are to be ranked

[clovelly]

Is there a way of automatically changing the range of cells that are to be ranked. Every time I use this program the range is different. I manually have to change the range to suit the number of participants.

=IF(M5="","-",(IF(OR(M5="e",M5="s",M5="r",M5="w",N5="e",N5="s",N5="r",N5="w",O5="s",O5="r",O5="w",O5="e",P5="w",P5="r",P5="e",P5="w",Q5="r",Q5="r",Q5="w",Q5="E",Z5="E",Z5="w",Z5="ret",),"-",RANK(AE5,$AE$5:$AE$23,1))))

Any help would be appreciated.

[jeffreybrown]

Are you saying you need the AE5:AE23 to be dynamic?

[clovelly]

Hi Jeffery
I'm unsure of what you mean by dynamic. If it means that the next time I use my program the range could be from AE7:AE35, yep I'm saying dynamic would be the right way to go.

[jeffreybrown]

Ok then, the rank part you can update to...

=RANK(AE5,$AE$5:INDEX($AE:$AE,MATCH(9.99999999999999E+307,AE:AE)),1)

Note: Sorry just noted you want both the first half and last half of the range to change.

Is this right?

[Haseeb Avarakkan]

Hello clovelly,

What is the condition to check Start & End cells? you said, "next time I use my program the range could be from AE7:AE35" So how are you defining STart cell must be AE7 : End must be AE37? What is the condition? based on certain values or number of non blank cells etc...

[clovelly]

hello again Jeffrey
Yes I would need both start and finish ranges to alter. If you have a solution that would be great. I don't understand just how your solution works.
Thanks for your prompt reply
Greg

[jeffreybrown]

Hi Greg,

Can you give an answer for post #5 which will help in the crafting of a solution?

[clovelly]

hi Guys
Each time we run a competition we have no idea on the number of entries in each section, therefore definite start and finish cells are not possible.
At an event we can have anything up to 360 + competitors in anyone of 13 different sections. To make things run quicker and easier for the scorers I have created a data entry page with all of the competitors listed in numerical order. Each section follows on from the previous section, with 3 empty rows between sections.

[Haseeb Avarakkan]

Could you please attach a dummy file with desired output? Remove all confidential data, if you have. Just use some sample data with your data layout.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[clovelly]

hello Haseeb
I just tried to attach a copy for you, but for some reason it didn't work. I will try again.
It should be attached now.
The column in question is AJ

I just thought - Each section has a maximum that we can cater for. If I allow for 50 competitors in each section, this would give us a definite starting and finishing point for each section.
Can a formula be developed that will ignore enpty cells. ie if only 40 competitors enter we have 10 empty cells, what do we do to remove these from the range to be ranked?

Thank you for the time you are giving me. I appreciate it greatly.

[Haseeb Avarakkan]

Try this in Aj5,

Please Login or Register  to view this content.

Then copy down in each group. Must have heading Score in each group's AK column.

[clovelly]

Haseeb
Thank you very much. i don't understand the full working, but will give it a go within the next couple of days. I will let you know if it works after that.
Greg