Is there a Macro Button to swap a lookup in a formula?

[RockandGrohl]

I have this badboy of a formula:

=INDEX(Dashboard!$F$4:$F$5069,MATCH(1,INDEX((LARGE(IF(Dashboard!$A$4:$A$5069=A28,Dashboard!$T$4:$T$5069),1)=Dashboard!$T$4:$T$5069)*(Dashboard!$A$4:$A$5069=A28),),0))


Ignoring the ins-and-outs of it, it does an Index:Match lookup on the Largest value of a range, IF that range matches a criteria.


You see that bit I bolded+underlined? That tells it to grab the 1st largest (or the best rank value)

I would like a button to press that swaps that integer for the next highest, and another button that swaps it for the next lowest. 1 becomes 2, or if pressed again it becomes 3, or 4... And vice versa.


Is this possible?

Because of the large range of cells this formula is in (300 or so) I can't change the RIGHT(64) character, for instance, as the lookup cell is in either A4 or A54 or A305 so it'll push the right cell out.

Any ideas guys? Thanks.

 

[James006]

Hello,

You can insert a Spin Button ... which will have a cell link ... which can be inserted in your formula ... :wink:

Hope this will help

 

[RockandGrohl]

That's pretty much exactly what I wanted, thanks. Do you know if there's an easy way to insert it onto 300 or so rows? Or would it be manual? Thanks.

 

[James006]

That's pretty much exactly what I wanted, thanks. Do you know if there's an easy way to insert it onto 300 or so rows? Or would it be manual? Thanks.

Great ...

Once you have created your Spin Button and selected the related cell link ...

Within your formula ... you will replace your 1 ... by this cell ... with $ signs ...

And then a simple copy paste of your formula over all your rows will do he job ....

Hope this will help

 

[RockandGrohl]

Hi James,

I have 300 rows and on each row is a different holiday tour, I would like to manually adjust some rows on a row-by-row basis, to pick the 2nd best rank or the 3rd best rank.

I think your solution above would change all the tours, what I'm asking is can I copy and paste the macro button, and have the reference inside the macro button update? Thanks.

 

[James006]

Sorry ... I misunderstood your objective ...

Well ... you are in for 300 independent Spin Buttons ...!!!

 

[Eric W]

You could get a bit fancier if you want, with the Spin Button idea.

I don't know what your top row looks like, typically it's headings. Try this on a copy of your workbook. Increase the row height of row 1 so it's large enough to hold a spin button. Then go to the View tab > Freeze Panes > Freeze Top Row.

Now each row is a tour. Find an empty column, say M, and put a 1 in every row. Change your formula so the 1 now is M2, then copy to all the rows.

Now add the Spin Button. If you don't have the Developer tab showing, go to File > Options > Customize Ribbon > on the right side click the Developer box > OK. Then go to the Developer tab > Insert > ActiveX Controls > Spin Button (bottom left). Use the mouse to paint it where you want on row 1. Now right click on it, select Properties. Change the LinkedCell to M2, and change Min to 1. If this is the first Spin Button you've added, it should be named SpinButton1 - check the name on the top line. Close the properties box.

Right click on the sheet tab on the bottom and select View Code. In the window that opens, paste this code:

Private Sub Worksheet_Selectionchange(ByVal target As Range)
    SpinButton1.LinkedCell = "M" & target.Row
End Sub

Make sure the spin button name is correct, and the column for the counters. Then close the VBA window (Alt-Q or the top-right X).

OK, after all that!, you have a spin button that stays on the screen as you scroll. When you select a row with the mouse, the macro automatically switches the spin button to point to the counter on that row.

 

[RockandGrohl]

You could get a bit fancier if you want, with the Spin Button idea.

I don't know what your top row looks like, typically it's headings. Try this on a copy of your workbook. Increase the row height of row 1 so it's large enough to hold a spin button. Then go to the View tab > Freeze Panes > Freeze Top Row.

Now each row is a tour. Find an empty column, say M, and put a 1 in every row. Change your formula so the 1 now is M2, then copy to all the rows.

Now add the Spin Button. If you don't have the Developer tab showing, go to File > Options > Customize Ribbon > on the right side click the Developer box > OK. Then go to the Developer tab > Insert > ActiveX Controls > Spin Button (bottom left). Use the mouse to paint it where you want on row 1. Now right click on it, select Properties. Change the LinkedCell to M2, and change Min to 1. If this is the first Spin Button you've added, it should be named SpinButton1 - check the name on the top line. Close the properties box.

Right click on the sheet tab on the bottom and select View Code. In the window that opens, paste this code:

Private Sub Worksheet_Selectionchange(ByVal target As Range)
    SpinButton1.LinkedCell = "M" & target.Row
End Sub

Make sure the spin button name is correct, and the column for the counters. Then close the VBA window (Alt-Q or the top-right X).

OK, after all that!, you have a spin button that stays on the screen as you scroll. When you select a row with the mouse, the macro automatically switches the spin button to point to the counter on that row.

Haha! That's genius! And the funny thing is, I already included an index column with a 1 and changed the formula to reference the cell, as I had resigned to making a few changes manually each week instead of creating 300x4 separate spin buttons.

Much appreciated, I'll look in to implementing this next week, as of right now however I'm in a real jam and need some help with this:

https://www.mrexcel.com/forum/excel-questions/1069851-stop-returning-results-after-first-match.html

 

[James006]

Hello again,

If you need to dig into multiple Spin Buttons ... from Chris Mortimer

https://www.youtube.com/watch?v=Pmya_vSIFvU

HTH