software

My boss sent me a workbook that at present contains only one sheet, however
he wants me to copy certain data (depending on column A) into a second sheet
and other data into a third sheet, but have it update on sheets 2 and 3 when
the data on sheet1 is changed, added, or deleted. Column A contains a
general classification, and he wants certain classifications on sheet2 and
other classifications on sheet3. There are no formulas, just data. It's
just a big list of materials and their locations and vendors, etc. Can this
be done?

Tell your boss it's far easier (and likely to be more accurate, too) to leave
it all in one sheet and then use Datagt;Filtergt; to present a selected subset of
the data.
quot;Lmarie6quot; wrote:

gt; My boss sent me a workbook that at present contains only one sheet, however
gt; he wants me to copy certain data (depending on column A) into a second sheet
gt; and other data into a third sheet, but have it update on sheets 2 and 3 when
gt; the data on sheet1 is changed, added, or deleted. Column A contains a
gt; general classification, and he wants certain classifications on sheet2 and
gt; other classifications on sheet3. There are no formulas, just data. It's
gt; just a big list of materials and their locations and vendors, etc. Can this
gt; be done?

The world is full of bosses with stupid ideas when it comes to spreadsheet
design
lt;ggt;--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;Duke Careyquot; gt; wrote in message
...
gt; Tell your boss it's far easier (and likely to be more accurate, too) to
gt; leave
gt; it all in one sheet and then use Datagt;Filtergt; to present a selected subset
gt; of
gt; the data.
gt;
gt;
gt;
gt; quot;Lmarie6quot; wrote:
gt;
gt;gt; My boss sent me a workbook that at present contains only one sheet,
gt;gt; however
gt;gt; he wants me to copy certain data (depending on column A) into a second
gt;gt; sheet
gt;gt; and other data into a third sheet, but have it update on sheets 2 and 3
gt;gt; when
gt;gt; the data on sheet1 is changed, added, or deleted. Column A contains a
gt;gt; general classification, and he wants certain classifications on sheet2
gt;gt; and
gt;gt; other classifications on sheet3. There are no formulas, just data. It's
gt;gt; just a big list of materials and their locations and vendors, etc. Can
gt;gt; this
gt;gt; be done?
Amen to that

quot;Peo Sjoblomquot; wrote:

gt; The world is full of bosses with stupid ideas when it comes to spreadsheet
gt; design
gt; lt;ggt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;Duke Careyquot; gt; wrote in message
gt; ...
gt; gt; Tell your boss it's far easier (and likely to be more accurate, too) to
gt; gt; leave
gt; gt; it all in one sheet and then use Datagt;Filtergt; to present a selected subset
gt; gt; of
gt; gt; the data.
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Lmarie6quot; wrote:
gt; gt;
gt; gt;gt; My boss sent me a workbook that at present contains only one sheet,
gt; gt;gt; however
gt; gt;gt; he wants me to copy certain data (depending on column A) into a second
gt; gt;gt; sheet
gt; gt;gt; and other data into a third sheet, but have it update on sheets 2 and 3
gt; gt;gt; when
gt; gt;gt; the data on sheet1 is changed, added, or deleted. Column A contains a
gt; gt;gt; general classification, and he wants certain classifications on sheet2
gt; gt;gt; and
gt; gt;gt; other classifications on sheet3. There are no formulas, just data. It's
gt; gt;gt; just a big list of materials and their locations and vendors, etc. Can
gt; gt;gt; this
gt; gt;gt; be done?
gt;
gt;
gt;

Thanks... but is there a way to make the filter include more than one
category? He wants 5 different categories to show at once for one report,
and just one category for another report. Sorry if I seem helpless, but it's
been 8 years since I got my MOUS certification in Excel - my memory has since
diminished when it comes to aspects I haven't used in the 8 years since.

quot;Duke Careyquot; wrote:

gt; Tell your boss it's far easier (and likely to be more accurate, too) to leave
gt; it all in one sheet and then use Datagt;Filtergt; to present a selected subset of
gt; the data.
gt;
gt;
gt;
gt; quot;Lmarie6quot; wrote:
gt;
gt; gt; My boss sent me a workbook that at present contains only one sheet, however
gt; gt; he wants me to copy certain data (depending on column A) into a second sheet
gt; gt; and other data into a third sheet, but have it update on sheets 2 and 3 when
gt; gt; the data on sheet1 is changed, added, or deleted. Column A contains a
gt; gt; general classification, and he wants certain classifications on sheet2 and
gt; gt; other classifications on sheet3. There are no formulas, just data. It's
gt; gt; just a big list of materials and their locations and vendors, etc. Can this
gt; gt; be done?

Well, does that mean there are 6 categories all together?

If so, show the 5 by setting the filter to Custom and then set it lt;gt; the one
you don't want.

If you have more than 6 categories, you can add a helper column that
contains a formula like (assumes the category is in col C):

=IF(OR(C2={quot;tquot;,quot;uquot;,quot;vquot;,quot;xquot;,quot;yquot;}),quot;Inlcudequot;,quot;Exclud equot;)

Copy the formula down, then filter on this new column.
quot;Lmarie6quot; wrote:

gt; Thanks... but is there a way to make the filter include more than one
gt; category? He wants 5 different categories to show at once for one report,
gt; and just one category for another report. Sorry if I seem helpless, but it's
gt; been 8 years since I got my MOUS certification in Excel - my memory has since
gt; diminished when it comes to aspects I haven't used in the 8 years since.
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Tell your boss it's far easier (and likely to be more accurate, too) to leave
gt; gt; it all in one sheet and then use Datagt;Filtergt; to present a selected subset of
gt; gt; the data.
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Lmarie6quot; wrote:
gt; gt;
gt; gt; gt; My boss sent me a workbook that at present contains only one sheet, however
gt; gt; gt; he wants me to copy certain data (depending on column A) into a second sheet
gt; gt; gt; and other data into a third sheet, but have it update on sheets 2 and 3 when
gt; gt; gt; the data on sheet1 is changed, added, or deleted. Column A contains a
gt; gt; gt; general classification, and he wants certain classifications on sheet2 and
gt; gt; gt; other classifications on sheet3. There are no formulas, just data. It's
gt; gt; gt; just a big list of materials and their locations and vendors, etc. Can this
gt; gt; gt; be done?


And if your boss still insists on separate sheets, you could have each
cell on Sheets 2 amp; 3 = the corresponding cell on sheet A, and then
apply the filters on Sheets 2 amp; 3. Your boss would think you did it
his way...

---GJC--
gjcase
------------------------------------------------------------------------
gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
View this thread: www.excelforum.com/showthread...hreadid=528152Thanks so much, that's exactly what I'm going to do! I think he's pretty set
on separate sheets for each. He also wants me to show him how I did it once
it's done, that'll be the toughest part! Thank you all for your help!!
Happy Friday!!!

quot;gjcasequot; wrote:

gt;
gt; And if your boss still insists on separate sheets, you could have each
gt; cell on Sheets 2 amp; 3 = the corresponding cell on sheet A, and then
gt; apply the filters on Sheets 2 amp; 3. Your boss would think you did it
gt; his way...
gt;
gt; ---GJC
gt;
gt;
gt; --
gt; gjcase
gt; ------------------------------------------------------------------------
gt; gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
gt; View this thread: www.excelforum.com/showthread...hreadid=528152
gt;
gt;

One more thing ~ when I go to copy and paste link on the 2nd sheet, it won't
let me paste the link if I select the quot;skip blanksquot; option. Is there a way
around this? There's a heck of a lot of 0's on that sheet!

quot;gjcasequot; wrote:

gt;
gt; And if your boss still insists on separate sheets, you could have each
gt; cell on Sheets 2 amp; 3 = the corresponding cell on sheet A, and then
gt; apply the filters on Sheets 2 amp; 3. Your boss would think you did it
gt; his way...
gt;
gt; ---GJC
gt;
gt;
gt; --
gt; gjcase
gt; ------------------------------------------------------------------------
gt; gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
gt; View this thread: www.excelforum.com/showthread...hreadid=528152
gt;
gt;


One way is to use an IF statement rather than a straight link.

Insert =IF(Sheet1!A1=quot;quot;,quot;quot;,Sheet1!A1) in cell A1 of both Sheets 2 amp; 3
and then copy them across the entire range. This will make all blank
cells on Sheet 1 appear blank on Sheets 2 amp; 3 and should still work
okay with filters amp; sums.

Not sure if there's a way to do this by formatting 0s to be blanks,
perhaps someone else can address that?

---GJC--
gjcase
------------------------------------------------------------------------
gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
View this thread: www.excelforum.com/showthread...hreadid=528152