How to automate in Excel?

[Mark Dawson]

I'm trying to help my kid's school home and school club better use Excel
for tracking a walking program.

They use Excel spreadsheets to calculate the number of laps/miles walked
each week. Each weekend they add a new column, entering the number of
laps walked (i.e., column 1 could be 10, column 2 would be 20, meaning
they walked 10 laps the 2nd week).

They send a list for the the school newsletter indicating the kids who
have reached a milestone of walking an even number of miles (2, 4, 6,
etc). Right now, they hand generate this list. Note that its only when
they reach that milestone are they mentioned--if week one they walk 2
miles, they get mentioned; if week 2 they walk another 1.75 miles, they
do not get mentioned--as they haven't reached the 4 mile milestone.

The school track is 8 laps/mile.

Is there a way of auto-generating a list of kids who have reached a
milestone?

I know I could add a "hidden" column that calculates the number of mile
milestones (i.e. do a integer division by 16, so both 16 and 31 would
divide to 2). To calculate whether a milestone has been reached, you'd
just have to check if the previous column is less than the current
column (i.e., column 2==2, column 4 == 2, no milestone).

However, what I'm not sure how to do is to take that information &
generate a list of all the kids with the "2" milestone, the "4"
milestone, etc. It would be nice for the solution to be cross-platform
(as I'm the only Mac user), but it would be OK, if it was-Mac only (they
could just send the spreadsheets to me and I could send them back the
list), if something like AppleScript needed be used, instead of
something that could be done inside of Excel itselfŠ

Thanks!

Mark
(e-mail address removed)

 

[CyberTaz]

Hi Mark-

I'm trying to get away from my PC @ work so I'm not sure I read your
post thoroughly. If I get the gist of it, try going to
Data>Filter>Advanced Filter, especially the "Copy to another location"
feature. This may be what you're looking for, and you sound savvy
enough to handle it with what's in XL Help.

If this is not what you need or if you have any further questions,
please post back. If I can't do better I'm sure someone else will. |:>)

 

[Mark Dawson]

Hi Mark-

I'm trying to get away from my PC @ work so I'm not sure I read your
post thoroughly. If I get the gist of it, try going to
Data>Filter>Advanced Filter, especially the "Copy to another location"
feature. This may be what you're looking for, and you sound savvy
enough to handle it with what's in XL Help.

If this is not what you need or if you have any further questions,
please post back. If I can't do better I'm sure someone else will. |:>)

I'm not sure if this is exactly what I want (or I don't completely
understand it):

I want something like:
IF (AE5=1, COPY(B5 & " " & A5) TO A100)

i.e., if AE5=1, copy the kid's name to that particular cell

I didn't see in the filter options a way to say what cells get copied

Thanks!

Mark
(e-mail address removed)

 

[CyberTaz]

Hi Mark-

re: I didn't see in the filter options a way to say what cells get
copied

In order to use the Adv Filter you must first have captions/fieldnames
at the top of each column of data and those columns in the data range
need to be consecutive. This is probably the case already. The filter
relies on a Criteria Range being set up on the same sheet, which is
nothing more than a copy of the same field names. The cells immediately
below them are where you specify the criteria, and the content of each
field included in the Criteria Range will be copied to the Copy To
location for each record that meets the criteria. When you specify the
Criteria Range in the Adv Filter dialog box, just make sure to specify
the range of cells where your duplicate field names are thru the cells
where the actual criteria is. In your case, probably just 2 rows in all
(such as G1011). Also- make sure you click somewhere in your data
range before opening the dialog box so the feature knows where your
records are.

re: I want something like: IF (AE5=1, COPY(B5 & " " & A5) TO A100)

Something like this can also be done, but what you need is a Lookup fx
(HLookup or VLookup depending on how your data is arranged) rather than
a "Copy" fx. In this case your range of data would serve as a Table.
IMHO, this would be a somewhat more complicated way to get the job
done... never did like nested fx's. Then, again, I can't be sure having
never seen how the sheet has been set up.

If you'd like, click the "show options" link next to my handle and
click "Reply to Author" to send me an email address. I can send you an
XL attachment that uses the Adv Filter.

Hope this is useful|:>)