I have two documents that are identical in columns, but one is a master list. People do inputting in a weekly document, then have to populate to the master using "cut'n'paste" method. I want to automatically populate the master, and have the entries alphabetized. Access is not an option; this needs to be strictly an EXCEL solution, if there is one.
2006-07-05 07:45:49 · 13 answers · asked by Finnegan 7 in Computers & Internet ➔ Software
I am assuming you have sheet 1 and sheet 2. Here is an example.
In Column A,
type in A1, +(b1)
type in A2, +(b2)
Then cut cell B1 and past it in a cell in sheet 2. This will change the formula in A1 from +(b1) to =+(Sheet2!B1)
Then in sheet 1 highlight the A column and click the AZ icon at the top to sort it.
Any changes you make in sheet 2 will reflect in sheet 1.
Also, to protect sheet 1 so it can't be updated directly select the tools menu at the top and Protection - protect sheet
2006-07-05 08:05:24 · answer #1 · answered by Anonymous · 0⤊ 0⤋
You can use sort to alphabetize the entries. Do you not want people to have access to the master list? It seems they do if they are cutting and pasting. Why don't you have them input directly into the master. There are ways to limit what ability they have within the document. EXCEL does a lot of things most users have no clue of. Might try getting some reading material on EXCEL.
2006-07-05 14:52:30 · answer #2 · answered by Curbkindaguy 2 · 0⤊ 0⤋
You need to find someone who can write a Visual Basic (the language embedded in Excel) macro for you. The sequence of operations might be (from the master document):
1. Open the update file.
2. Copy the lines from the file.
3. Move to the end of the master document.
4. Paste into the document.
5. Do the data sort.
Hrm... If the update file is the same every week, you might be able to do this yourself with the Record Macro function (Tools -> Macros -> Record New Macro). Give it a whirl.
2006-07-05 14:52:08 · answer #3 · answered by jv 2 · 0⤊ 0⤋
I believe it is possible to link a cell within the master document to a cell in another document so that the contents of the slave document are display/updated automatically in the master doc. The slave documents would have to be on a network server share that the users can all access.
2006-07-05 14:52:27 · answer #4 · answered by bogus_dude 6 · 0⤊ 0⤋
I believe a merge will help you accomplish this, if not, then linking the two documents so that one updates as the other receives the info. Look in the HELP for Link documents.
2006-07-05 14:54:46 · answer #5 · answered by Anonymous · 0⤊ 0⤋
I know of no way to do this using just excel, but there are free and open source database solutions you can use. MySQL is an option and I have been told, but not confirmed, that openoffice.org includes a database solution.
You can also download a front end for the MySQL database from their webiste.
2006-07-05 14:51:10 · answer #6 · answered by John J 6 · 0⤊ 0⤋
This is something that could easily be done by someone that knows how to use vba (visual basic for applications) code. If I were doing this for myself I would think it should only take a couple hours to get a working prototype and just tweak it from there.
2006-07-05 14:54:23 · answer #7 · answered by Doug B 3 · 0⤊ 0⤋
what about linking the cells between the 2 document?
2006-07-05 14:48:58 · answer #8 · answered by Sportsterjohn 5 · 0⤊ 0⤋
you should have be able to highlight the cells you want and then click the descending button to alphabetize (looks like a A with an arrow going down it).
cut and pasting is the short cut - if i understood you correctly.
2006-07-05 14:48:39 · answer #9 · answered by madison018 6 · 0⤊ 0⤋
To automate the process, you must use MACROS. It's under the tools menu. Good luck !
2006-07-05 14:52:33 · answer #10 · answered by Jonjoe 4 · 0⤊ 0⤋