Excel function for tables

[Guest]

I have a table that has been created in excel that lists mileage from one place to the other. The rows all have headers for starting points, and the columns all have headers for destinations. I want to know if there is an excel function that will enter the data from the row headers and from the column headers as well as the amount of mileage if I were to click on the intersection of the two (which would be the numerical amount of mileage). Any help that you could offer would be greatly appreciated.

 

[Jonathan Rynd]

I have a table that has been created in excel that lists mileage from
one place to the other. The rows all have headers for starting
points, and the columns all have headers for destinations. I want to
know if there is an excel function that will enter the data from the
row headers and from the column headers as well as the amount of
mileage if I were to click on the intersection of the two (which would
be the numerical amount of mileage). Any help that you could offer
would be greatly appreciated.

If you want Excel to take some action based on a click of the mouse, you
need to use OLE objects or VB. Probably VB is better.

If you want more help, could you say what you mean by "enter the data"?
What data? Where do you want it entered?

 

[Jonathan Rynd]

I have a Mileage chart (I will attach it) and what
I wanted to be able to do (if possible) is to be able to click on the
amount of mileage (such as in cell F5) and that number be entered into
one cell (cell F11 on the Travel Form). I also would like for the
appropriate row heading (in this case Dewar Elementary) to appear in
another (cell B11 on travel form), and the column header (in this case
Hahira Elementary) to appear in cell c11 on the travel form.

Open the mileage chart. Along the bottom of the Excel window are "tabs"
representing the worksheets in each workbook. Since you haven't done
anything to change them, they're the default: Sheet1, Sheet2, Sheet3.
The mileage chart is in sheet1.

Right-click on the Sheet1 tab and choose View Code. This opens the
Visual Basic (VB) editor window. VB is a programming language built into
Excel (and other Microsoft programs). The window should be titled
"Microsoft Visual Basic - Mileage Chart.xls - [Sheet1 (Code)]. "Code"
means the instructions that tell the computer what to do.

In the main area of the window, which is blank, type the following:

Option Explicit

Const travelform As String = "Travel form (2).xls"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Integer
Dim r As Long
If Target.Count > 1 Then Exit Sub
c = Target.Column
r = Target.Row
If c < 2 Or c > 13 Or r < 3 Or r > 14 Then Exit Sub
Workbooks(travelform).ActiveSheet.Range("F11") = Target.Value
Workbooks(travelform).ActiveSheet.Range("C11") = Cells(2, c).Value
Workbooks(travelform).ActiveSheet.Range("B11") = Cells(r, 1).Value

End Sub

This subroutine is executed each time you click on a cell in the mileage
chart -- that's what the "SelectionChange" means. Target is a
representation of the cell you clicked on. After some initial processing
to determine whether you actually clicked on a mileage, it copies the
information to the other workbook.

See if you can understand what is going on. It should be simple to
modify the script if you want it to do something different.

Thanks for your response to my question. I know just enough about
excel functions to really confuse myself. I don't know what you
mean by OLE or VB?

OLE stands for "Object Linking and Embedding". It is a way of putting
objects (menus, buttons, pictures, etc.) into a document such as an Excel
spreadsheet or a Word file. My thought was to create buttons with all
the mileage amounts on them, and clicking the button updates the
necessary cells.

VB stands for Visual Basic. It is the programming language built into
Excel that can control all kinds of aspects of Excel.

 

[Jonathan Rynd]

In the main area of the window, which is blank, type the following:

I got an email reply from the original poster, which said:


: Basically, when I click on a cell in the mileage chart, nothing happens
: on the travel form, nor does anything happen on the mileage chart.
:
: I did close Excel, and shut down my computer for the weekend. When I
: opened the document in Excel this morning, I get an error regarding
: Disabled macros.

I forgot to mention that about Excel. Starting with Excel 2000, in order
to use scripting, either the scripts have to be signed, or you have to
set security to medium. There are some inconveniences with having
security set to medium but I think they are worth it.

Look for a toolbar that has buttons on it. The first button is a right-
pointing triangle.

|> O Security...

If you see it, click on the Security... button. If you don't see it,
right click on a toolbar and choose Visual Basic from the popup menu,
then click the Security... button.

A dialog box will appear. Set the Security Level to medium. Now,
whenever you open a workbook that contains a script or VB macro, it will
ask you what you want to do. You should always choose "Disable macros"
unless you know exactly what the macros are.

In this case, you wrote the macro, so when you open the spreadsheet
containing the macro, choose Enable Macros.

Let me know if it works.

 

[Guest]

OK - I have set the security to Medium, and still nothing happens. I am thinking that I am doing something wrong. I have two sheets (Sheet one entitled Mileage Chart and Sheet two entitled Travel Form). I am clicking inside cell F11 (on the travel form) and then switching over to Sheet 1 (Mileage Chart). With the mileage chart open, I click on a number inside of a cell, and nothing happens. No information appears in the corresponding cells on the travel form. I feel really ignorant, but I know that I am probably making some stupid mistake. Please Help!

 

[Jonathan Rynd]

I have two sheets (Sheet one entitled Mileage Chart and Sheet two
entitled Travel Form).

Do you have two sheets inside the same workbook, or two workbooks (.xls
files)?

What are the workbook names?
What are the sheet names?

 

[Guest]

I have one workbook entitled "Spreadsheet with Visual Basic Programming" and within this workbook, I have two sheets entitled "Mileage Chart" and "Travel Form"

 

[Jonathan Rynd]

I have one workbook entitled "Spreadsheet with Visual Basic
Programming" and within this workbook, I have two sheets entitled
"Mileage Chart" and "Travel Form"

In that case you have to modify the code. I wrote the code based on what
you sent me, which was two separate workbooks. You are using something
different than what you sent me -- it's not suprising that it doesn't
work. What's happening is it's modifying the "Travel form (2).xls"
workbook that you sent me, which is why you're not seeing it in the
active workbook.

The part of the code that copies the values is the last three lines
before the End Sub. Notice how now it refers to the activesheet of a
given workbook. Instead you want to refer to the activeworkbook and a
named sheet. The syntax is

ActiveWorkbook.Sheets("Travel Form")

Of course, if you've moved the items around on the worksheet, you'll also
need to change the cell references later.

You can also get rid of the "const travelform" line.

 

[Guest]

GREAT!!!!! I have finally gotten it to work in row 11.....but, when I attempt to enter another trip, say in row 12....then 13....then 14 and so on, it changes what I did in row 11, and enters nothing in rows 12 - whatever. Do I have to enter the syntax for each row? You don't know how much I appreciate this...You are such a great help, and If I could (knew where to send it) I would send you a Thank You gift)

 

[Guest]

What happened? I had to shut down my computer because I was having some e-mail problems, and now when open the spreadsheet that WAS working, it doesn't work now......I saved it under several names, so that I would be sure to have it in several different places, but it works nowhere. Is there something that I have to do each time that I open the file in order for the programming to work?

 

[Guest]

Never mind, I figured out that I have to enable macros, instead of Disabling macros....we are back working fine now.

 

[Jonathan Rynd]

GREAT!!!!! I have finally gotten it to work in row 11.....but, when I
attempt to enter another trip, say in row 12....then 13....then 14 and
so on, it changes what I did in row 11, and enters nothing in rows 12
- whatever.

If you look at the code you will see that it always works in row 11. Look
at where it says ".Cells". You only asked for it to work for row 11.

How is the code supposed to determine which row is the correct row?

Do I have to enter the syntax for each row?

The same code gets run whenever you click on a cell in the mileage grid.
Entering it multiple times doesn't get you anything. You have to modify
the code to do what you want. I can't make any suggestions because I don't
know what you want it to do.

 

[Guest]

----- Jonathan Rynd wrote: ----

GREAT!!!!! I have finally gotten it to work in row 11.....but, when
attempt to enter another trip, say in row 12....then 13....then 14 an
so on, it changes what I did in row 11, and enters nothing in rows 1
- whatever.

If you look at the code you will see that it always works in row 11. Look
at where it says ".Cells". You only asked for it to work for row 11

How is the code supposed to determine which row is the correct row

Do I have to enter the syntax for each row?

The same code gets run whenever you click on a cell in the mileage grid.
Entering it multiple times doesn't get you anything. You have to modify
the code to do what you want. I can't make any suggestions because I don't
know what you want it to do

 

[Guest]

On the travel form, a person would have 40 or more trips per month to log onto the travel form. We travel from one school to another school several times per day, and we have to log our mileage on each trip each day. For instance, On any given day one might leave Central and travel to Point A which is 6 miles. Then he/she left Point A and Travels to Point B ( 8 miles), then leaves Point B and goes on to Point C ( 12 miles), and then return to Central (15 miles). The total travel for that day is 41 miles - But each of these trips has to be logged as a seperate line (row) on the travel form. Then we would log the travel for the next day...etc, until month end. So we will have to have 40-50 lines (rows) to enter travel. What we want to be able to do is click in the cell (or row) on the travel form then switch over to the mileage chart, click on the cell containing the amount of mileage. This mileage amount would be entered in one cell of the travel form, and the corresponding row header in a second cell on the travel form, and the corresponding column header in a third cell on the travel form. This would need to be repeated numerous times in 40-50 rows of the travel form. Hope that this explanation makes sense.

 

[Jonathan Rynd]

What we want to be able to do is click in the cell (or row) on the
travel form then switch over to the mileage chart, click on the cell
containing the amount of mileage.

Let me make sure I understand you properly:

When someone clicks on the mileage chart, you want the information to be
entered into the row in the travel form that contains the currently active
cell? Is that correct?

 

[Guest]

Yes, that is correct.
Just so that I can practice explaining myself....
We want to activate the cell (or highlight the entire row if that is easier) on the travel form . We will then switch over to the mileage chart. On the mileage chart we will click on the cell containing the numeric value of the mileage. There would be three actions that would take place from this activation of a single cell in the mileage chart. These actions would take place inside the activated row (or the row of the activated cell) on the travel form.
These three actions are as follows
1. The row header from the active cell on mileage chart would appear in Column B. The number identifier for each row will change with each subsequent trip
2. The column header for the active cell in the mileage chart would appear in column C on the travel form. The number identifier on each row will again change with each subsequent trip
3. The amount of mileage from the active cell on the mileage chart would appear in column F on the travel form. The number identifier on each row will again change with each subsequent trip

Please remember that we will make numerous entries on each travel form (40-50 different rows)

I hope that I am getting netter at explaining myself, I am trying really hard. Thanks again for your help!

 

[Jonathan Rynd]

Yes, that is correct.
Just so that I can practice explaining myself.....
We want to activate the cell (or highlight the entire row if that is
easier) on the travel form . We will then switch over to the mileage
chart. On the mileage chart we will click on the cell containing the
numeric value of the mileage.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Integer
Dim r As Long
If Target.Count > 1 Then Exit Sub
c = Target.Column
r = Target.Row
If c < 2 Or c > 13 Or r < 3 Or r > 14 Then Exit Sub
with activeworkbook.Sheets("Travel Form")
.Cells(ActiveSheet.Row,6) = Target.Value
.Cells(ActiveSheet.Row,3) = Cells(2, c).Value
.Cells(ActiveSheet.Row,2) = Cells(r, 1).Value
end with
End Sub

 

[Guest]

This it the error message that I get:
"Run-time error '438'
Object does not support this property or method"
Then you are given the options to "End" "Debug" or "Help"

 

[Jonathan Rynd]

with activeworkbook.Sheets("Travel Form")

Sorry, I made a mistake and didn't test it well enough. Change the lines to
add "ActiveCell" in between activesheet and row.

.Cells(ActiveSheet.ActiveCell.Row, 6) = Target.Value
.Cells(ActiveSheet.ActiveCell.Row, 3) = Cells(2, c).Value
.Cells(ActiveSheet.ActiveCell.Row, 2) = Cells(r, 1).Value

 

[Jonathan Rynd]

This it the error message that I get:
"Run-time error '438'
Object does not support this property or method"
Then you are given the options to "End" "Debug" or "Help"

Sorry. Change the "ActiveSheet." in the three lines to "ActiveCell".

.Cells(ar,6) = Target.Value
.Cells(ar,3) = Cells(2, c).Value
.Cells(ar,2) = Cells(r, 1).Value

and then view code on Travel Form and put this in:
Public ar

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ar = Target.Row
End Sub