Spread sheet programming / calculation help

[Mike Tolk.]

Hi,

Just wondering if someone could point me in the right direction?

I have created a job countdown spread sheet in excel for different companies
I work for (it simply counts down the days to go before I have to do a job)

On the countdown cell on each sheet, I have stuck a conditional format on
there, so that when it gets to 1 (a days notice to plan!) it turns red so I
notice it easily.

What I would like to do is create another page on the spread sheet that
looks at all the sheets and any countdowns that have turned red; it will grab
the details and stack them onto this blank sheet in rows.

This is probably an easy fix problem but thanks in advance anyway!

There is an example of the document on another forum:

http://www.excelforum.com/showthread.php?t=597949

But as you can see I have had no joy with that forum! please see the
additional notes at the bottom with the attachment.

Thanks

Oh I’m using Excel 2003 by the way

 

[jpdphd]

Hi Mike,

I downloaded your file and tried some things. Go to
http://homepage.mac.com/jdilger/Excel/Jobsheetexample.xls
to see the result. I made some notations.
Hope it helps.

jpdphd

 

[Mike Tolk.]

Brilliant ........ hats off to you

The only question I have now is how the heck did you do the custom filter
thingy??!! I'm kinda new to all this !

Oh and just to answer the question you put on the work sheet, I have to send
out letters to people well in advance and they always take their time
responding, so 90 days gives me loads of time to chase them up.

Cool, thanks again and if you could give me some instructions on setting up
that custom filtering you would be a life saver!

Mike

 

[Mike Tolk.]

aahhhhhh this is driving me nuts! I'm just not able to do what you did on the
example ....... help ..... please ......... anyone ....

 

[jpdphd]

If you have a bunch of columns with headings (and no completely empty
rows), you can "filter".
1. Select the cells with the headings.
2. Data > Filter > Auto Filter
a set of up/down triangles will appear in the cells of each heading
3. Click on the triangles and several choices will appear. You could
show all the rows with a particular category.
But, you want to display something not on the list. Select (Custom
filter...)
4. Enter your criteria. What I did was
Select rows where countdown is less than 8 AND is greater than
-30000
Click OK and you should be there.

After you've set it up once, to refresh the filtering, do the same
steps 1-3 above, but you won't have to fill in the criteria, just
click OK.

OK?

jpdphd

 

[jpdphd]

Correcting my last post,
to refresh, you need do only step 3 and click ok.

 

[Mike Tolk.]

Thanks for that, can understand the autofilter thingy but how do you get it
to look at all the other worksheets and drag the info over from there?

Sorry for sounding stupid!!

Thanks

Mike

 

[jpdphd]

Not stupid if you haven't learned it!

The basic idea is to put formulas like
=Builders!B6
into the various cells of your "urgent jobs" sheet (starting at C6).
The only problem with this is that when you do your filtering, it
won't be obvious which sheet the urgent jobs came from (unless you
look at the formula). This is easily fixed by adding a column in the
"urgent" sheet that contains the name of the sheet.
I got too fancy with my =INDIRECT(...) formulas. It saved me a little
time getting things set up, but obviously confused the heck out of
you! Sorry bout that!

jpdphd

 

[Mike Tolk.]

Got you .... so they are links to the other sheets and the 'All Jobs' section
simply contains, well ... all the jobs on the other sheets .... right, the
penny has dropped!!

Ok then, next problem ...... say if I used up all the 17 rows in the
Builders section and wanted to add more, this would I guess throw out all the
calculations for the 'All jobs' sheet, or just simply not include them into
the formatting.

What would I have to do in this situation? Is there any way I can get round
having to add on to the calculations? Is there a way of getting the 'All
jobs' sheet to notice the addition of extra rows and update automatically?

If you get what I mean!

 

[jpdphd]

Mike,
Only a penny? I was hoping for a bigger jackpot!

There's 2 ways to approach this.
1. A macro that would efficiently find the info and put it into 'all
jobs' at the click of a button. Hmmm, I'm not too excited about
teaching macro writing (I'm just a dabbler myself anyway) or writing
and debugging it at this point.
2. Decide on a sufficiently large upper limit of rows that could
possibly be in each section. No need to economize, they're all free.
Set up 'all jobs' to recognize all possible rows. Then, go out and get
tons of clients so you can make tons of money! Gee, maybe even enough
to hire someone to write excel macros for you .... Just kidding of
course!

By the way, if you do insert a new row within an existing range, excel
will update 'all jobs' references to account for cells that have moved
in the process. But, it won't automatically include the new row in the
calculations.

Cheers,
jpdphd