lookup

[Ian Jones]

learnt lots from these pages and now have formulas doing all my results, but
i can't find a way to auto my entries. i have 900 students with a larges
number entering say the athletics. column A first; B surname; C house; D
grade; E gender; F 100m to Q discus. i put a 1 in the events they enter. i
then filter say junior girls discus and copy to appropriate form on the
discus sheet. its time consuming and the whole process has to be redone if i
have to accept late entries. i have tried vlookup with if. index and row but
i can't get anything to work. is it possible say from the junior girls discus
form to have formula to automatically have first, surname and house listed on
the form? Would love to know if possibe? thx.

[Gary Brown]

I'm going to assume that the athletics are basically a list with columns
specific to that event.
My suggestion is to keep it all in the one main database with the 900
students.
1) Add columns appropriate for each event
2) Set up a filter for the database
DATA / FILTER / AUTOFILTER
3) Create 13 Custom Views
The first shows all data.
VIEW / CUSTOM VIEWS / ADD...
Call the view "All Info"
Make sure the 'Print Settings' and 'Hidden Rows, Columns and filter
settings' boxes ARE checked.
4) For each of your 12 events in Col F through Col Q...
a) Hide the columns that are NOT needed for that event.
b) Set up any specific print settings you want for that event.
c) Filter the data for that event
d) Save the custom view for that event. I suggest naming the Custom view
for the event.
e) Reset the worksheet to show all data so that you can create a custom
view for the next event
VIEW / CUSTOM VIEWS...
Highlight the 'All Info' view and select SHOW.
f) Repeat until you have created a custom view for each event.

Now it is a simple matter of selecting the proper view and you're done.
This is a bit of front-end work but should handle your needs.
HTH,
--
Gary Brown
[email protected]


"Ian Jones" wrote:

> learnt lots from these pages and now have formulas doing all my results, but
> i can't find a way to auto my entries. i have 900 students with a larges
> number entering say the athletics. column A first; B surname; C house; D
> grade; E gender; F 100m to Q discus. i put a 1 in the events they enter. i
> then filter say junior girls discus and copy to appropriate form on the
> discus sheet. its time consuming and the whole process has to be redone if i
> have to accept late entries. i have tried vlookup with if. index and row but
> i can't get anything to work. is it possible say from the junior girls discus
> form to have formula to automatically have first, surname and house listed on
> the form? Would love to know if possibe? thx.

[Ian Jones]

Thx for your help Gary.
I can see what you are saying but each event has different forms with their
own qualifying distances and records on them. also the track, highjump,
longjump and throwing events have different formats. We have 5 different
grades, elementary thru to senior. The way i do it now is filter say
highjump, girls, junior copy. then do the boys then change to the next grade.
but if i could have formula in each form which looks up if the highjump
column if=1 and gender column=girls and grade=junior and listed them all in
the form it would be fantastic. do you think its possible? thx ian

"Gary Brown" wrote:

> I'm going to assume that the athletics are basically a list with columns
> specific to that event.
> My suggestion is to keep it all in the one main database with the 900
> students.
> 1) Add columns appropriate for each event
> 2) Set up a filter for the database
> DATA / FILTER / AUTOFILTER
> 3) Create 13 Custom Views
> The first shows all data.
> VIEW / CUSTOM VIEWS / ADD...
> Call the view "All Info"
> Make sure the 'Print Settings' and 'Hidden Rows, Columns and filter
> settings' boxes ARE checked.
> 4) For each of your 12 events in Col F through Col Q...
> a) Hide the columns that are NOT needed for that event.
> b) Set up any specific print settings you want for that event.
> c) Filter the data for that event
> d) Save the custom view for that event. I suggest naming the Custom view
> for the event.
> e) Reset the worksheet to show all data so that you can create a custom
> view for the next event
> VIEW / CUSTOM VIEWS...
> Highlight the 'All Info' view and select SHOW.
> f) Repeat until you have created a custom view for each event.
>
> Now it is a simple matter of selecting the proper view and you're done.
> This is a bit of front-end work but should handle your needs.
> HTH,
> --
> Gary Brown
> [email protected]
>
>
> "Ian Jones" wrote:
>
> > learnt lots from these pages and now have formulas doing all my results, but
> > i can't find a way to auto my entries. i have 900 students with a larges
> > number entering say the athletics. column A first; B surname; C house; D
> > grade; E gender; F 100m to Q discus. i put a 1 in the events they enter. i
> > then filter say junior girls discus and copy to appropriate form on the
> > discus sheet. its time consuming and the whole process has to be redone if i
> > have to accept late entries. i have tried vlookup with if. index and row but
> > i can't get anything to work. is it possible say from the junior girls discus
> > form to have formula to automatically have first, surname and house listed on
> > the form? Would love to know if possibe? thx.

[Leo Heuser]

Ian

You're welcome to mail a copy of your workbook to me,
and I'll give it a try.

Remove NOSPAM from my address.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Ian Jones" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Thx for your help Gary.
> I can see what you are saying but each event has different forms with
> their
> own qualifying distances and records on them. also the track, highjump,
> longjump and throwing events have different formats. We have 5 different
> grades, elementary thru to senior. The way i do it now is filter say
> highjump, girls, junior copy. then do the boys then change to the next
> grade.
> but if i could have formula in each form which looks up if the highjump
> column if=1 and gender column=girls and grade=junior and listed them all
> in
> the form it would be fantastic. do you think its possible? thx ian
>
> "Gary Brown" wrote: