• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

MS Excel - GoTo Command buttons consolidated into a validation list

I have in excess of ten GoTo command buttons that I would like to consolidate into a single button using a validation list or similar approach.

Am currently using
Application.Goto Reference:=Range("V99:AB112"), Scroll:=True

The data for the GoTo is below:

Methodology                             CZ3000
Coverage Summary      DD3103
Classification Summary      DF3204
Public Holidays      DS3408
Payrate Chronology       DV3511
Comments                             ED3615
Penalty Summary      EH3718
Allowances                             FF4718
Other Conditions      FS5772
Annual Leave      FV5926
Personal Leave      GK6155
Parental Leave      HA3695
Long Service Leave      HU6622
PILN                             IN6841
Redundancy                             JC6940
Disagreggation of Super from Base Rate      JR7039
Disagreggation of Annual and Personal Leave from Base Rate      KQ7120
Calculation of Commission      LQ7200
Calculation of Superannuation      LW7240

Any ideas would be appreciated.

Andross9
0
Andross9
Asked:
Andross9
  • 4
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Your Goto Reference range contains a range of several columns and rows.

What is the data below that? Are these the goto reference ranges for each choice? I.e. when
"Methodology" is selected,, should the active cell change to cell CZ3000? Just a single cell?

please advise.

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If the above codes are meant to be single cell references, you could use data validation to select a value as listed in column A, then use a lookup to determine the cell address as listed in column B and use a macro to jump to the cell address.

See attached file.

cheers, teylyn
Book1.xlsm
0
 
Andross9Author Commented:
Hi Teylyn

Yes the data is below and to the right of the cell reference.

Wanting to have the cell reference as the active cell and displayed in the top left corner of the display.

I can give broader ranges but in some cases the ranges are too wide or long to fit to a screen.

Thanks for your prompt reponse.

Andross9
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
In that cased, check out my previous post. It must have crossed over with yours.

There are other ways to skin this cat, if the method suggested does not suit.

cheers, teylyn
0
 
Andross9Author Commented:
Yes we crossed over.

The solution works just as needed.

I really appreciate your ongoing efforts to help others.

Have recently completed some Excel and VBA courses but still struggling at context of some of the basic frameworks.

Great stuff.

Thanks again

Andross9
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Thanks for the grade. Getting your head around it all can take a while. We're all still learning every day.

cheers, teylyn
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now