Dropdowns with Hyperlinks

[blucajun]

I'm revising an internal expense report and would like to make it a bit more
automated to force the employee to always fill in the fields and provide all
required info. I have a main summary sheet, mileage form, and
entertainment/meal form. I have the main summary sheet set up with a column
for Category and in each of the cells in this column is a dropdown box that
references a hidden list with specific expense categories as choices (office
supplies, phone, travel, mileage, entertainment/meals, etc.). What I want to
do is hyperlink two of the choices to their applicable form when selected
from the drop down box. In other words, when I choose either "Mileage" or
"Entertainment/Meals" from the drop down box, it automatically jumps to the
"Mileage" form or the "Entertainment/Meals" form. Does the list have to
contain the hyperlinks somehow or is it just not possible?

Also, please keep in mind that I am not familiar with VBA (I searched here
for help first and found some references to VBA but don't understand what
it's all about).

Thanks so much!

 

[Gord Dibben]

Get used to working with VBA............your life will become much easier.

Assuming the "forms" are separate worksheets...................

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value
Case "Entertainment"
Sheets("Entertainment").Select
Case "Meals"
Sheets("Meals").Select
Case "Mileage"
Sheets("Mileage").Select
End Select
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on your Summary sheet tab and "View Code"

Copy/paste the above into that sheet module.

Edit to suit.....I used A1 as DV dropdown cell.

Alt + q to return to the Excel window.

Select from the dropdown and jump to the sheetname.


Gord Dibben MS Excel MVP

 

[blucajun]

Worked like a charm! I did a little reading on VBA and it doesn't seem so
scary. I'll have to start playing with it more. My next challenge is to
attempt to automate a field in the subtotal column to pull the totals from
the other worksheets based on whether the dropdown is Mileage or
Entertainment/Meals. This is a good opportunity for me to try to learn VBA.
Thanks!!

 

[blucajun]

Okay, I was successful in my next automation task with VLOOKUP but then ran
into another issue that I can't figure out. On the expense report summary
sheet (the main working doc.) I have a column (H6:H34) that contains the
totals for each expense item (one per row). In Column J I have a VLOOKUP
that adds the account code for each expense. Now I need to subtotal all the
expenses by account code into an little summary table at the bottom of the
summary sheet. I can't figure out how to make it subtotal the amount column
by account code and then paste the total of each account code into the little
account summary table

For example:

H7 = $1 and J7 = 7010
H8 = $2 and J8 = 7030
H9 = $6 and J9 = 7035
H10 = $1 and J10 = 7150
H11 = $1 and J11 = 7030
H12 = $10 and J12 = 7010

In my summary table at the bottom (I can put this anywhere), I need it to
show:

Acct.Code Total
7010 $11
7030 $3
7035 $6
7150 $1

It's probably very simple, but I think I'm in overload and just can't see
it. Any help would be appreciated.