Dynamic Cutting and Pasting along with sorting

[jcalvacca]

Hello

My 1st post.

I am pretty good at excel, but not good at macro programming.


Trying to create a spreadsheet with a couple of things going on so hopefully I will explain it well.


-Have a datatable with 6 columns and data in one of the columns repeats frequently

-Have several different tabs labeled as the frequently repeated data (6 total)

-Have another tab labeled "other" which will store data not corresponding to the other 6 tabs.


My desire is to have one spot for new data entry (6 columns worth) and have it sort the new data (based off of one column of data that frequently repeats) into one of the existing tabs. This data needs to be copied into the next available line in these preexisting tabs as well as a copy into an "universal" tab that holds all the data that has been entered.


Its like a complex search and "copy if new" type of functionality.


Not even sure if this could be done, and if it can would I be qualified enough to do it.


Like I stated before, I'm pretty good @ excel but not excellent.


Thanks in advance for any help I receive.

 

[Hui]

Jcalvacca

Can you put an example somewhere on a free file sharing site ?

 

[jcalvacca]

Here is the link to the shared file.


Thanks for your help!


http://www.4shared.com/file/197783843/d5c08067/Testfilea.html

 

[Hui]

Jcalvacca


I Understand what you are trying to do, 2 questions though


1. Do you want a separate data entry page which sends data to the appropriate pages automatically or


2. Have you thought about not having a separate data page for each supplier, then using a Table or Auto Filter area where you can easily select the company from a Dropdown and display the equivalent of the Tab data anyway?

 

[jcalvacca]

2 sounds good though i'm finding difficulty understanding how that visually would look. Sample file?


Im basically looking for the company to have two ways to traverse the list, one by tool number and one by customer. Just trying to simplify it for them as their present list was just in number sequence and they had problems with consistent data and people who were not good with excel couldn't find the information quickly so I thought the multiple tabs would work well but I'm open to suggestions.

 

[Hui]

jcalvacca


On the Sort by Mold # page, select all the data, from A1 to H323


In Excel 2003/XP goto Data, Filter, Autofilter

In Excel 2007/10 goto Insert, Table


You can now use the small drop downs shown by the arrows in Row 1 to select various combinations of data ie: Owner = Alliance


You can also sort by any field and select Top 10 etc


The Excel 2007, Table will automatically expand as you add data


Have a browse through the functionality of these columns and see if that satisfies your needs

 

[jcalvacca]

Awsome. Thanks! This is sooooooo much better than what I conceived originally.


One more question though, I have people using both platforms (2003 and 2007). My version is 2007 so functionality wise do I need to do this in 2003 or do the table properties xfer over once I saved a 2007 file into "2003" format?


Thanks again and I am looking forward to our future relationships on this forum!

 

[Hui]

Jcalvacca


The Excel 2007 Table is saved in a format which allows use in Excel 2003.


It will appear slightly different and will have a strong blue border and loose any other formatting, but it retains most but not all of the functionality of the 2007 table including the drop down functions, albeit slightly restricted compared to 2007.


Give it a go.

 

[kJo]

Hi..Hui..

Had it not been for the satisfaction of Jcalvacca what and how your suggestion no.1 i.e. having a separate data entry page which sends data to the appropriate pages automatically, would work ??

 

[Hui]

KJo

It is not impossible to setup a system with a data entry page and send data to both a master list and a series of supplier lists as originally requested.

The issue is maintenance of these lists, what if some one adds or deletes a supplier from one list and not the master or vise-versa, or what if some one updates a product in one list etc.

It is much easier to maintain one master list and then setup a number of reports or summaries from that list. as well it can be used as a master lookup table for all sorts of data validation or lookup purposes.