Hello so I have to be fairly brief (time crunch) but this is what's going on.

On sheet1 (factors&rankings) I have some things that have some values associated. To the right of those things and their associated values I have the rankings they have against the other things on the same date.

Now in the end I'll have several factors that each thing will have values for and I have to combine all the rankings to create an overall ranking that is based on the ranking of all the individual factors.

This overall ranking will be on the next sheet. To get an idea the code I started on looks something like this (the syntax for the general formula is horrible and that's really where I'm having this issue -- the particular line is "activecell.value = "=Rwt * R!Range("B2").offset(0,n+1) + Vwt * V!B2 + Cwt * C!B2" -- note that with this line I only started work on my issue with the first factor where you see the "offset" thing going on, I will need to do that with factors "V" and "C" as well)

So, the problem is that the rankings for each factor (on their respective sheets) wont' always be in the same spot because the number of things we're looking at can change so while the rankings might start on cell G2 with one test it could start on X2 on another test that is comparing more things.

In my code (for the overall project) I will have the number of things I'm comparing as a variable that will be used across many macros, so I intend to use that variable to tell this particular code shown below where to start. If you look at it you see "B2" that's where the values of the things start, it needs to look n+1 cells to the right (which is where the ranking of those things based on their real values start).

I have created a small mock-up of what I'm trying to accomplish as an excel file.

ALSO: Ideally I want this to be a formula I can put in the first cell and paste across all other cells which need to be ranked etc -- that's the way I have it set up now, except for the fact that I don't know proper syntax to select a cell on another sheet with a variable offset.

If the attachment doesn't work let me know? I've never attached anything in this forum before.

Thanks for the help, I can give you any additional information you need.
VariableDistanceRanking.xlsm

Please Login or Register  to view this content.