MrExcel Publishing
Your One Stop for Excel Tips & Solutions

PLEASE PLEASE PLEASE Help me !!!! Dialog Sheet Based DATA ENTRY FORMS + Range issues


Posted by Daniel C on November 15, 2001 10:35 PM

Hi to all Excel Experts out there. I have a working
Marks and Grades system with loads of nice features
the only problem is is that Id like A) to be able to
have the user be able to use a custom made dialog sheet
as a data entry point where he can pick the teacher from
a drop-down field and type in names and the predicted
grade into "entry boxes" (edit boxes) and when he clicks
the OK button it transfers them as a new row into my
spreadsheet. I have already got code which copies the
entire row of my named range "End" ( a row with all the
formulas in it) and inserts an entire new row. I
understand how to offset but the prob is that when i
followed the example in my Mastering Excel book of writing

ActiveCell.Offset (0, 2).Value = "whatever i name the
control i want to paste the data in from"

nothing happens but a macro error.


Thanks to anyone who can set me straight or point me
in the right direction to a resource site that can
help me

Daniel C.
My second issue B) is closely related. I have made the
assumption that there is never more than 30 students in
the year group and thus for cells involving caluclating
an average mark, percent, etc for the year groups i
listthe cells to average as being between F8:F38.
Id liketo be able to have excel automatically calculate
theaverage from the first cell (always gonna be F8)
down to the cell above the named range "End" which is
where my loadof formulas are shown.

Thanks VERY much in advance to ne one who can help me


Posted by Aladin Akyurek on November 16, 2001 12:30 AM

The Range Question

Daniel --

Is this what you're looking for?

=AVERAGE(OFFSET(F8,,,MIN(ROW(End))-ROW(F8)))

or equivalently

=AVERAGE(INDIRECT("F8"&":"&ADDRESS(MIN(ROW(End))-1,6)))

Aladin