Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatically?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatically?
Tell your boss it's far easier (and likely to be more accurate, too) to leave
it all in one sheet and then use DataFilter to present a selected subset of the data. "Lmarie6" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatically?
The world is full of bosses with stupid ideas when it comes to spreadsheet
design <g -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Duke Carey" wrote in message ... Tell your boss it's far easier (and likely to be more accurate, too) to leave it all in one sheet and then use DataFilter to present a selected subset of the data. "Lmarie6" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatica
Amen to that
"Peo Sjoblom" wrote: The world is full of bosses with stupid ideas when it comes to spreadsheet design <g -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Duke Carey" wrote in message ... Tell your boss it's far easier (and likely to be more accurate, too) to leave it all in one sheet and then use DataFilter to present a selected subset of the data. "Lmarie6" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatica
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. "Duke Carey" wrote: Tell your boss it's far easier (and likely to be more accurate, too) to leave it all in one sheet and then use DataFilter to present a selected subset of the data. "Lmarie6" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatica
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 < 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={"t","u","v","x","y"}),"Inlcude","Exclud e") Copy the formula down, then filter on this new column. "Lmarie6" wrote: 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. "Duke Carey" wrote: Tell your boss it's far easier (and likely to be more accurate, too) to leave it all in one sheet and then use DataFilter to present a selected subset of the data. "Lmarie6" wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatically?
And if your boss still insists on separate sheets, you could have each cell on Sheets 2 & 3 = the corresponding cell on sheet A, and then apply the filters on Sheets 2 & 3. Your boss would think you did it his way... ---GJC -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=528152 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatica
Thanks 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!!! "gjcase" wrote: And if your boss still insists on separate sheets, you could have each cell on Sheets 2 & 3 = the corresponding cell on sheet A, and then apply the filters on Sheets 2 & 3. Your boss would think you did it his way... ---GJC -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=528152 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatica
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 "skip blanks" option. Is there a way around this? There's a heck of a lot of 0's on that sheet! "gjcase" wrote: And if your boss still insists on separate sheets, you could have each cell on Sheets 2 & 3 = the corresponding cell on sheet A, and then apply the filters on Sheets 2 & 3. Your boss would think you did it his way... ---GJC -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=528152 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatically?
One way is to use an IF statement rather than a straight link. Insert =IF(Sheet1!A1="","",Sheet1!A1) in cell A1 of both Sheets 2 & 3 and then copy them across the entire range. This will make all blank cells on Sheet 1 appear blank on Sheets 2 & 3 and should still work okay with filters & 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: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=528152 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatica
Thanks! I actually found this in Excel Help and think it's a bit simpler:
Use a number format to hide zero values in selected cells Caution Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format. Select the cells that contain the zero (0) values that you want to hide. On the Format menu, click Cells, and then click the Number tab. In the Category list, click Custom. In the Type box, type 0;-0;;@ "gjcase" wrote: One way is to use an IF statement rather than a straight link. Insert =IF(Sheet1!A1="","",Sheet1!A1) in cell A1 of both Sheets 2 & 3 and then copy them across the entire range. This will make all blank cells on Sheet 1 appear blank on Sheets 2 & 3 and should still work okay with filters & 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: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=528152 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|