Variable range based on data from a cell

VisualRealism

New Member
Joined
Jul 16, 2018
Messages
1
Hi,
I'm asking for your help, I'll try to explain as detailed as possible what I'm in need of(and couldn't figure out a way to do it yet).
So, I have a table that contains a number of products, for each product the yield is introduced each week, I would like to achieve to get the top 4 worst performing products out of the table for a specified week(like selecting a week from somewhere and based on that data(the value of the week) it selects the 4 worst for that week and enters them in 4 different cells) and to show the name of the product above the value that it extracted from the table.
The part with getting the worst performing ones is easy with =SMALL(range:range,nr in top) but this way I have to change the range each week, for example I'm on column Y this week so the formula is =SMALL(Y76:Y96,1) for the worst one but next week it should be =SMALL(Z76:Z96,1). Is there a way to select a week and after that the formulas range changes automatically? Let me know if something is unclear or more info is needed.

Thanks a lot in advance
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top