Hi Folks, I wonder if someone can help me.

I am building a spreadsheet that uses a master table of PCs on an
estate that are being upgraded. PCs P4 and above dont need an upgrade,
hence if they are already out there, all they need is our new build
installing.

If it is a P4, we take the old P4 off the estate and replace it with a
new like for like model. When this happens, the one coming off the
estate is reuseable, so what I want to do, is to use a formula that
identifies this PC by model (within a scope of about 10 P4s') in a
particular column, then sorts by Dept no and then it copies that record
to another table such as a buffer stock table that updates dynamically.

I suspect its part of the IF function but beyond me I am afraid.

I am more than happy to send the sheet if anyone needs it, but
essentially its the function or string I need to be honest.

Many thanks

Mike


--
kapiliary
------------------------------------------------------------------------
kapiliary's Profile: http://www.excelforum.com/member.php...o&userid=33424
View this thread: http://www.excelforum.com/showthread...hreadid=532403

Rather than transferring records between tables, I suggest you add a new
column (field) to your existing table which identifies the information
you wish to collect e.g. location, or buffer stock

When you have all of your related data on a single datasheet in this
manner, it greatly simplifies maintenance.

There are innumerable useful tools and functions when you hold your
data in this format. However, probably the simplest one you'll be
interested in is the filter (Data-filter-autofilter). 2 seconds to set
up. You make your selections from the drop-down lists it creates and
VOILA all entries you don't want to see are hidden. Instantaneously
you have a buffer stock table without having to create or maintain a
buffer stock table!


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532403

John, thanks for the reply. In terms of the autofilter function, I am
pretyt comfy, but I hadnt considered what you said in those terms
before.

I guess its simple therefore to simply add a conditional format that
says, if the work is complete and it equals one of a number of PC type
options, turn it green, or filter it to show in another column, is this
what you mean?

regards

Mike


--
kapiliary
------------------------------------------------------------------------
kapiliary's Profile: http://www.excelforum.com/member.php...o&userid=33424
View this thread: http://www.excelforum.com/showthread...hreadid=532403

Hi Mike,

Don't bother with conditional formatting. If you want to create a
field which is dependent upon other fields then add a new column
(field) to your datasheet, name it in the first row and put the formula
in the next row and copy down against all records. Then you'll be able
to filter based on that field or do lots of other things which reuse
that result. If you use conditional formatting, you won't be able to
filter, or do almost anything else on the outcome of that calculation,
except look at how pretty it is.

For example, if you want to identify a particular model in a particular
department, then select those criteria from the filter drop-down boxes
and all other records will be hidden. If you run into limitations with
the autofilter, use the advanced filter. If you move the PC into buffer
stock, then change the entry in the location field to reflect the new
location (e.g. "Buffer") - or create a new field (column) called buffer
stock, and enter "Yes" or some other indicator to indicate it is buffer
stock. Then you'll readily be able to filter out all stock which is
not buffer stock and see all this buffer stock at a glance.

This single database structure is the solid foundation which will keep
your spreadsheet simple, flexible, low maintenance and reliable. Once
you've got that understood, only then it is time to start looking at
improving your formulae or improving your analysis/reports.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532403

John thanks, I'll give that a try when the next opportunity manifests
itself.

regards


Mike


--
kapiliary
------------------------------------------------------------------------
kapiliary's Profile: http://www.excelforum.com/member.php...o&userid=33424
View this thread: http://www.excelforum.com/showthread...hreadid=532403

John thanks, I'll give that a try when the next opportunity manifests
itself.

regards


Mike


--
kapiliary
------------------------------------------------------------------------
kapiliary's Profile: http://www.excelforum.com/member.php...o&userid=33424
View this thread: http://www.excelforum.com/showthread...hreadid=532403