Dropdowns with Hyperlinks
 

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!

Dropdowns with Hyperlinks
 

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!!

"Gord Dibben" wrote:

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

On Tue, 24 Jun 2008 14:20:01 -0700, blucajun
wrote:

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!

Dropdowns with Hyperlinks
 

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.

"blucajun" wrote:

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!!

"Gord Dibben" wrote:

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

On Tue, 24 Jun 2008 14:20:01 -0700, blucajun
wrote:

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!