Excel help needed..

[Eddie Wall]

I have tried the normal excel newsgroups that I used to use to get
some help on an excel problem I have and there all now dead except for
the odd spam....

Does anyone know of a newsgroup with "excel experts" that is
functioning.........

In case there are any here what I am trying to do ( I don't even know
if it possible ) is as follows...

I have a list of incoming customer orders...

They are processed and colour coded accordingly...

White : Checked and acknowledged
Yellow : Ordered from supplier
Red : Received and checked, ready for shipping
Pink : Shipped and ready for invoicing
Green : Invoiced and finished.

My problem is that once a week or so I want to get all of the Pinks
together to prepare invoices..... and although I want to keep Greens
for reference etc etc... I don't generally need them on a day to day
basis..

My plan was that I could have a "background" master copy with all of
the information but I could look at say only the pinks if I wanted....
Also IF I updated any status and changed its colour, on saving, it
should be merged to the master.

The colour system has worked VERY well for me for many years and I am
happy with it... visually instantly I can see and tell the status of
any order., just a little fine tuning would make it even better...

Basically what I want to know is if it is possible... if I know that
much I will find learn how to do it..

Any other ideas are most welcome..

Thanks

Eddie


"Going to church doesn't make you a Christian any
more than going to a garage makes you a mechanic."

[Eddie Wall]

After reading this I can see where some confusion may arise.... I
know that I can custom sort entries with later versions of Excel BUT
they are still there....

What I envisaged was something like a small key block of colours and
an "on/off" switch on each as well as a "master" switch..... I could
choose to have any colour on , all colours on any mix I wanted and
only those chosen would be visible BUT all of them would be there on
the master sheet.

Not sure that adds much clarity............! ;)_

Post by Eddie Wall
I have tried the normal excel newsgroups that I used to use to get
some help on an excel problem I have and there all now dead except for
the odd spam....
Does anyone know of a newsgroup with "excel experts" that is
functioning.........
In case there are any here what I am trying to do ( I don't even know
if it possible ) is as follows...
I have a list of incoming customer orders...
They are processed and colour coded accordingly...
White : Checked and acknowledged
Yellow : Ordered from supplier
Red : Received and checked, ready for shipping
Pink : Shipped and ready for invoicing
Green : Invoiced and finished.
My problem is that once a week or so I want to get all of the Pinks
together to prepare invoices..... and although I want to keep Greens
for reference etc etc... I don't generally need them on a day to day
basis..
My plan was that I could have a "background" master copy with all of
the information but I could look at say only the pinks if I wanted....
Also IF I updated any status and changed its colour, on saving, it
should be merged to the master.
The colour system has worked VERY well for me for many years and I am
happy with it... visually instantly I can see and tell the status of
any order., just a little fine tuning would make it even better...
Basically what I want to know is if it is possible... if I know that
much I will find learn how to do it..
Any other ideas are most welcome..
Thanks
Eddie
"Going to church doesn't make you a Christian any
more than going to a garage makes you a mechanic."

"Going to church doesn't make you a Christian any
more than going to a garage makes you a mechanic."

[Howard]

Post by Eddie Wall
After reading this I can see where some confusion may arise.... I
know that I can custom sort entries with later versions of Excel BUT
they are still there....
What I envisaged was something like a small key block of colours and
an "on/off" switch on each as well as a "master" switch..... I could
choose to have any colour on , all colours on any mix I wanted and
only those chosen would be visible BUT all of them would be there on
the master sheet.
Not sure that adds much clarity............! ;)_

FWIW in my personal opinion Excel is not the place for doing this.

I know you say you want to stick with your present system but ...

What you need is a small and easy to create database program. This would
solve all of your problems, though I sugggest that this colour based
system is now past it's date.

My two cents


Howard

[Eddie Wall]

Post by Howard
FWIW in my personal opinion Excel is not the place for doing this.
I know you say you want to stick with your present system but ...
What you need is a small and easy to create database program. This would
solve all of your problems, though I sugggest that this colour based
system is now past it's date.
My two cents

Thanks for your feedback... an interesting point of view...

From what I see, as with Air traffic Control sometimes the "old ways"
have advantages..... even with the advent of all the computer graphics
and technology, even today in the most modern air traffic control
towers, at some point a planes name is printed on a label on a block
of wood and that is moved up and down slots and manually passed to the
next stage controllers.... there are various checks and measures built
in and a lot of fail safe double checks for doing so. There are good
and solid reason why this very detailed and life critical operation is
still operated this way. ( Ok it also covers the fact that they are
not in toilet if the computer systems go down... but you get the
drift..)

With my system, which took a very long time to evolve...although it is
incredibly simple, there is a clear an immediate visual indication
all the time of every incoming input, instantly identified as to its
stage in progression through the stages it has to go through... It
also provides an instant and immediate overall idea of the state of
each stage as a whole....... I can tell at a glance all of the key
questions one would ask about the progress of an order....

I have tried many "accounts" programs", Access, many "CRM"
programs.... and ultimately the basic spreadsheet hit all of the
right notes....... the database has advantages for quickly allowing
specific customer printouts without all of the sorting and copy and
pasting and the accounts programs had an advantage of specific part
number detail again something that is not that relevant for this
application, I am looking for a quick, broad overview... on a single
glance. A big picture view that I can instantly tell if we need to get
stuck immediately into orders or shipping or invoicing..... ect etc..

Eddie

"Going to church doesn't make you a Christian any
more than going to a garage makes you a mechanic."

[Howard]

Post by Eddie Wall
I am looking for a quick, broad overview... on a single
glance. A big picture view that I can instantly tell if we need to get
stuck immediately into orders or shipping or invoicing..... ect etc..

I know what you are saying and go ahead.

I just would say this .. The old systems are great ... until they just
don't cope with volume.
There comes a time with EVERY system when it outgrows it's usefulness
and needs to be modified and adapted.

I agree that Accounts packages etc and a step too far and unnecessary to
boot.

This is why I suggest getting a local tech guy to write a small db for
you in Access or another easy to program db.

What it will give you is a sorting option where only one kind of invoice
is listed in front of you in list view. It can also give you a listing
with colour codes that RESULT from using numbers during the input
process. So if you change from using colour codings in your input to
using 1,2,3,4,5 etc .... he can then write the simple db in such a way
that all items coded 1 are shown in the listing as green ... etc etc
etc.

With a db you can also have very easy access to a summary where the last
week's comparative numbers can be reviewed compared with the previous
week and the previous week to that.

Once the data is entered ... (And a sensible programmer can make it nice
and straight forward and logical and not complex and idiotic) ... there
is almost no limit as to how it can then be displayed. You just draw up
a mockup of how you want to view the records and let him do the biz.

Howard

[Eddie Wall]

Post by Howard
This is why I suggest getting a local tech guy to write a small db for
you in Access or another easy to program db.
What it will give you is a sorting option where only one kind of invoice
is listed in front of you in list view. It can also give you a listing
with colour codes that RESULT from using numbers during the input
process. So if you change from using colour codings in your input to
using 1,2,3,4,5 etc .... he can then write the simple db in such a way
that all items coded 1 are shown in the listing as green ... etc etc
etc.
With a db you can also have very easy access to a summary where the last
week's comparative numbers can be reviewed compared with the previous
week and the previous week to that.
Once the data is entered ... (And a sensible programmer can make it nice
and straight forward and logical and not complex and idiotic) ... there
is almost no limit as to how it can then be displayed. You just draw up
a mockup of how you want to view the records and let him do the biz.

Thanks Howard...... I am looking at options on what other data or
information I may want to incorporate...

Much appreciate your input.....

Eddie
"Going to church doesn't make you a Christian any
more than going to a garage makes you a mechanic."

[m4rkiz]

Post by Eddie Wall
My problem is that once a week or so I want to get all of the Pinks
together to prepare invoices..... and although I want to keep Greens
for reference etc etc... I don't generally need them on a day to day
basis..

http://excel.tips.net/Pages/T002396_Selecting_Cells_of_a_Specific_Color.html

create new macro in your excel, copy macro from website, change this two lines:

'vbRed, vbMagenta, vbYellow, vbWhite
--> 'lColor = vbBlue

'If you prefer, you can use the RGB function
'to specify a color
--> lColor = RGB(255, 0, 255)

choose name, save it, select all cells in spreadshet,
run macro - all pink cells (rgb 255,0,255) will be selected

copy them into new sheet if you need, prepare invoices,
you will have them selected in main sheet, just change them
to green when you done

there is no simple way to do exactly what you described, you will
have to write some macro that will hide unwanted columns\rows

cant't help you there as i have more experience with hardware
then programing (perhaps some freelancers?)

as always - have some backup copy of your important files!

[Eddie Wall]

Thank you for your input which is much appreciated.... I can see what
you are getting at and as you say it is a partial solution.... I am
looking at one or two other macro tips and will see if I can develop
the skills to botch them together.:)

Post by m4rkiz

Post by Eddie Wall
My problem is that once a week or so I want to get all of the Pinks
together to prepare invoices..... and although I want to keep Greens
for reference etc etc... I don't generally need them on a day to day
basis..

http://excel.tips.net/Pages/T002396_Selecting_Cells_of_a_Specific_Color.html
'vbRed, vbMagenta, vbYellow, vbWhite
--> 'lColor = vbBlue
'If you prefer, you can use the RGB function
'to specify a color
--> lColor = RGB(255, 0, 255)
choose name, save it, select all cells in spreadshet,
run macro - all pink cells (rgb 255,0,255) will be selected
copy them into new sheet if you need, prepare invoices,
you will have them selected in main sheet, just change them
to green when you done
there is no simple way to do exactly what you described, you will
have to write some macro that will hide unwanted columns\rows
cant't help you there as i have more experience with hardware
then programing (perhaps some freelancers?)
as always - have some backup copy of your important files!

"Going to church doesn't make you a Christian any
more than going to a garage makes you a mechanic."

[Eddie Wall]

Post by m4rkiz

Post by Eddie Wall
My problem is that once a week or so I want to get all of the Pinks
together to prepare invoices..... and although I want to keep Greens
for reference etc etc... I don't generally need them on a day to day
basis..

http://excel.tips.net/Pages/T002396_Selecting_Cells_of_a_Specific_Color.html
'vbRed, vbMagenta, vbYellow, vbWhite
--> 'lColor = vbBlue
'If you prefer, you can use the RGB function
'to specify a color
--> lColor = RGB(255, 0, 255)
choose name, save it, select all cells in spreadshet,
run macro - all pink cells (rgb 255,0,255) will be selected
copy them into new sheet if you need, prepare invoices,
you will have them selected in main sheet, just change them
to green when you done
there is no simple way to do exactly what you described, you will
have to write some macro that will hide unwanted columns\rows
cant't help you there as i have more experience with hardware
then programing (perhaps some freelancers?)

Thanks for the suggestions...... Am looking at revising the system and
as per another suggestion looking at a database.....

Your input was much appreciated,

Eddie

Post by m4rkiz
as always - have some backup copy of your important files!

"Going to church doesn't make you a Christian any
more than going to a garage makes you a mechanic."

[Ulick Magee]

Post by Eddie Wall
My problem is that once a week or so I want to get all of the Pinks
together to prepare invoices..... and although I want to keep Greens
for reference etc etc... I don't generally need them on a day to day
basis..

So this used to work for you - what changed? an Office "upgrade" ?

You might well find that Open Office / Libre Office is more helpful to
you than the latest M$Office. Wouldn't be the first time...

[Eddie Wall]

On Sun, 30 Jan 2011 23:46:50 +0000, Ulick Magee

Post by Ulick Magee

Post by Eddie Wall
My problem is that once a week or so I want to get all of the Pinks
together to prepare invoices..... and although I want to keep Greens
for reference etc etc... I don't generally need them on a day to day
basis..

So this used to work for you - what changed? an Office "upgrade" ?
You might well find that Open Office / Libre Office is more helpful to
you than the latest M$Office. Wouldn't be the first time...

Thank Ulick.. I will check it out...

What changed is the sheer volume of entrys, I now have 4-5 years of
"greens" completed items... I still need to refer to them so need them
on hand but could do with all but the last 3 months visible on a day
to day./

Eddie

"Going to church doesn't make you a Christian any
more than going to a garage makes you a mechanic."