Automatically Ordering an Irregular Table

problem.jpg

I want to order these sections depends on percentage values on D2 columns. The idea is "Biggest goes top". But the group of cells include pictures and irregular rows columns. How can i do that?

Try entering this formula into A3, then fill down to include the first empty row below the last section (so in the picture for 4 groups, you would fill down to A30).

Formula:

Please Login or Register  to view this content.

Then select A3:D3 and sort by Column A values, largest to smallest.

For aesthetic purposes, you could hide the formula in column A by using a white font.

Thanks for answering but it didnt work. I have to say that when put new datas on the table right side, left side section has to be change automatically. Actually, my left side section is not a table. There is no relation between pics and values. How can i make group them? Is it more convenient to work on another sheet? If you have any idea please answer me!

It worked fine for me in a quick test. There may be things in your file that cannot be seen from a picture which are causing it to behave differently for you.

Please attach a copy of your workbook with any personal / confidential data fictionalised, or removed if it is not relevant to the question. To do this, click the 'Go Advanced' button below the quick reply window, then scroll down and look for the 'Manage Attachments' link.

If the percentages in column D are the result of formulas, then you would need to leave the formulas and the data that they are generated from in the file. I suspect that this could be the reason why it is not working for you.

Because you have pictures, you will not be able to sort with formulas alone, you will need a formula like the one I have already suggested to index the pictures and the rows that they relate to. You will need to sort manually or use vba.

Please find attached xlsx file.

One thing that I missed in your picture was the merged cells in various columns.

In order to be able to sort anything all cells in the range to be sorted must be of equal size. To the best of my knowledge, there is no workaround for this.

I'm going to look at a couple of things in more detail to see if there is an alternative method which can be used. If not you might need to make some slight changes to the sheet design.

thanks, i'm trying to it on new sheet. I am also wondering auto ordering VBA Code. Do you know anything about for this?

I think that the merged cells would still be a problem there.

I think that I have found a solution with formulas, just need to finish putting it together and test it.

Ok, this has proven to be a bit of a complex task, to start with I had to remove the existing pictures.

Next I changed the formulas in columns C and D so that the highest value is always at the top, also I added another formula into the merged cells in columns C&D which finds the correct name to match the associated values.

Next I added the pictures back in to row 70, so that each is in the column with the correct name at the top.

Finally, I used a lot of named ranges to link thise picture back to column B so that everything appears where it should. Rather than me explaining the whole process, this link details the steps that I followed, https://excel.tips.net/T003128_Displ..._a_Result.html with the exceptions being:-

Step 1, used same sheet, not new sheet.
Step 6, different formula, the formula in C3, C10, etc replaces this.
Step 8 onwards, where the guide only details a single 'Picture' name, I had to use a new name for each picture to be displayed.

Had to zip the file, for some reason it has increased significantly in size. Possible due to trial and error making the pictures fit resulting in multiple deleted images leaving some bloat in the file. Will have another look tomorrow, see if I can clean it up a bit.

This should be enough for you to see if it works as expected.

It works amazing! I very appreciate to you. I'm trying to getting understand everything. And last questions, maybe I need to ask in other Header, but i want to learn from you, Is there any option to fix this cells which on B:D columns as always visible?

Sorry, I don't understand what you're asking for

If you can create what you want manually, a before and after example would help.

Sorry for being so late. I uploaded an image. I want to keep always visible left side and i need to be able to scroll right side. I hope this will be clear.
Attachment 624413

Thank you!

You mean you want this?

https://support.office.com/en-gb/art...1-67dd25f2508f

No, its not working for me because I want to fix left side and first row too

You can see here:
GWoVeTg.jpg

You can lock the top row(s) while scrolling up and down.

You can lock the left column(s) while scrolling left and right.

You can not stop the left column(s) scrolling up and down with the right column(s).