Populating Adjacent Cells from a Function

Hi Guys,

Trying to figure out how to populate adjacent cells from a function. I have been searching threads and general consensus seems to be that this is not possible. However I have seen a number of examples of this happening, but the code is always locked, most famously probably would be the Bloomberg BDS and BDH formula.

Can Anyone advise on how I would go about this? Every method I've tried doesn't work, my only thought is something like an event sub procedure, but I can't find an example of e.g. onfunctionrun event subprocedure, but can't find an example of this event code either.

Much Appreciated!

Can you upload a sample file showing what you'd ideally like to accomplish? As in, what data you'd have BEFORE and what results you'd like AFTER?

Thanks for the response! At the office so uploading the actual sheet might cause me compliance issues, so e.g. take

70 Mandy
67 Ken
90 Kathy
92 Bill
56 Wendy
98 Tom
65 Candy
60 Tim

=Ranking(datarange, 1, 4)

then have it populate

98 Tom
92 Bill
90 Kathy
70 Mandy

in A1:B4 assuming the above formula was in A1

Aware of the above mentioned option but its somewhat inelegant and I've seen formulas that get around this. Any thoughts?

Hmmm tried that but doesn't populate a grid for me? just populates the cell with function in it, with the rank 1 value?

Hi Holger,

I have changed it per your request thanks for letting me know!

Thanks I know about setting it as an array function thats what I was referring to with 'Aware of the above mentioned option but its somewhat inelegant and I've seen formulas that get around this. Any thoughts?'

The downside of this is the having to do it on the multiple cells, the function I've seen is far more elegant, it populates all the cells as described, with only the top left cell containing the formula. The primary benefit of it is that its easily used by low skill excel users, as this is the end goal of the function.