Need MS Excel Help!
Planted Tank Forums
Your Tanks Image Hosting *Tank Tracker * Plant Profiles Fish Profiles Planted Tank Guide Photo Gallery Articles

Go Back   The Planted Tank Forum > General Forums > The Lounge & Introductions


Reply
 
Thread Tools Display Modes
Old 03-09-2012, 03:07 AM   #1
wendyjo
Planted Tank Guru
 
PTrader: (7/100%)
Join Date: Feb 2009
Location: DC area
Posts: 2,989
Default

Need MS Excel Help!


I don't think what I want is possible, but thought I'd ask around before I give up.

Is it possible to link a spreadsheet to an identical spreadsheet so that when you update the original it automatically updates the copy, but not the other way around? I have a spreadsheet that I will be updating daily, and I also need someone else to input a bit of data into it quite often. But I don't want her to have access to the original for fear she'll screw MY data up. Making a copy of the original won't work since I will be adding alot of data each day. Any suggestions? Is it possible to give her edit rights to only some of the columns and not others?

For example, lets say we sell Avon together - you have a spreadsheet of customers with all their contact info. Each day you add new customers along with the items they order. Then you need me to go in every few days after I deliver it to them and update it with the actual amount of the cash we received from them, and mark them paid on the spreadsheet. But you think I'm a dumbass and you're afraid I will accidently screw up the client data you spent weeks entering. How do you allow me to update my info yet insure that you're info stays safe?
wendyjo is offline   Reply With Quote Quick reply to this message
Sponsored Links
Advertisement
 
Old 03-09-2012, 03:43 AM   #2
mistergreen
No more Bow ties
 
mistergreen's Avatar
 
PTrader: (13/100%)
Join Date: Dec 2006
Location: Cincinnati
Posts: 14,346
Default

Not with MS excel. I'm assuming you guys are on separate computers. Try google docs.
https://accounts.google.com/ServiceL...ltmpl=homepage

you can do spreadsheets and share docs. Not sure if it will do what you want though.
mistergreen is online now   Reply With Quote Quick reply to this message
Old 03-09-2012, 04:03 AM   #3
Daximus
Got dirt?
 
Daximus's Avatar
 
PTrader: (0/0%)
Join Date: Oct 2011
Location: Kansas
Posts: 1,877
Default

Quote:
Originally Posted by mistergreen View Post
Not with MS excel. I'm assuming you guys are on separate computers. Try google docs.
https://accounts.google.com/ServiceL...ltmpl=homepage

you can do spreadsheets and share docs. Not sure if it will do what you want though.

Yup, Google Docs is the best way to do this. Another option is a program called Dropbox...basically a little bit of shared storage with an online app that updates the files.
__________________
Daximus is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 04:05 AM   #4
ucantimagine
Planted Tank Enthusiast
 
ucantimagine's Avatar
 
PTrader: (8/90%)
Join Date: Jan 2012
Location: Chicago, IL
Posts: 847
Default

I thought it was about Mosura excel. lol
__________________
ucantimagine is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 04:27 AM   #5
GeToChKn
Planted Tank Guru
 
GeToChKn's Avatar
 
PTrader: (1/100%)
Join Date: Apr 2011
Location: Hamilton, Ontario, Canada
Posts: 5,116
Default

Are these spreadsheets shared on a networkable server or a shared folder on 1 of the PC's?

It's very easy to do. Open both spreadsheets and go to the one you want to copy the data from File A, press = in the cell you want, go to the other sheet, File B, click on the cell you want, press enter. Data from File B with show in the cell in File A and any changes in File B will automatically show in File A.

I love how everyone says you can't do it with MS Excel without knowing that it can or not. Everyone needs intro to Excel, I used to teach this in 3rd lesson.

If they want to do this across the internet, a private SQL database would be the best way to do it.
__________________
20g platy, , 2 x 10g shrimp, 3 x 20g shrimp, 7.5g shrimp and 1 great dane/mastiff puppy.

Sump Pimp #2

My Tanks and my shrimps
GeToChKn is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 04:34 AM   #6
Daximus
Got dirt?
 
Daximus's Avatar
 
PTrader: (0/0%)
Join Date: Oct 2011
Location: Kansas
Posts: 1,877
Default

Quote:
Originally Posted by GeToChKn View Post
Are these spreadsheets shared on a networkable server or a shared folder on 1 of the PC's?

It's very easy to do. Open both spreadsheets and go to the one you want to copy the data from File A, press = in the cell you want, go to the other sheet, File B, click on the cell you want, press enter. Data from File B with show in the cell in File A and any changes in File B will automatically show in File A.

I love how everyone says you can't do it with MS Excel without knowing that it can or not. Everyone needs intro to Excel, I used to teach this in 3rd lesson.

If they want to do this across the internet, a private SQL database would be the best way to do it.
Guess who I'm going to PM next time Excel starts kicking my ass at work, lol. I fought with 2010 for like 15 minutes the other day trying to do something soooo simple, lol.
__________________
Daximus is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 05:11 AM   #7
mistergreen
No more Bow ties
 
mistergreen's Avatar
 
PTrader: (13/100%)
Join Date: Dec 2006
Location: Cincinnati
Posts: 14,346
Default

Quote:
Originally Posted by wendyjo View Post
automatically updates the copy
@GeToChKn: ahemmmmm, clears throat
mistergreen is online now   Reply With Quote Quick reply to this message
Old 03-09-2012, 01:40 PM   #8
wendyjo
Planted Tank Guru
 
PTrader: (7/100%)
Join Date: Feb 2009
Location: DC area
Posts: 2,989
Default

These 2 files would be shared on a network. GeTo, I don't think that would work in this case. There is going to be alot of info added each day to File A - too much to be copying over to File B one cell at a time (unless I misunderstood you). But I will play around with your suggestions and see exactly how it works.
wendyjo is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 02:02 PM   #9
GraphicGr8s
Pixel Prestidigitator
 
GraphicGr8s's Avatar
 
PTrader: (10/100%)
Join Date: Apr 2011
Location: West coast of the east coast of the USA.
Posts: 2,775
Default

What you're looking for is a relational database.Something along the lines of Alpha Five. Use to use it years ago for just what you are doing.
Even though you are entering info into your spreadsheet it is also going into another db. The other persons spreadsheet accesses this for your info and visa versa
__________________
Recedite, plebes! Gero rem imperialem.
Quote me as saying I was misquoted.
Once you get rid of integrity the rest is a piece of cake.
Here's to our wives and sweethearts - may they never meet.
If you agreed with me we'd both be right.
GraphicGr8s is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 03:12 PM   #10
rroginela
Planted Tank Obsessed
 
rroginela's Avatar
 
PTrader: (1/100%)
Join Date: Sep 2011
Location: CHicago, IL
Posts: 311
Default

Quote:
Originally Posted by wendyjo View Post
I don't think what I want is possible, but thought I'd ask around before I give up.

Is it possible to link a spreadsheet to an identical spreadsheet so that when you update the original it automatically updates the copy, but not the other way around? I have a spreadsheet that I will be updating daily, and I also need someone else to input a bit of data into it quite often. But I don't want her to have access to the original for fear she'll screw MY data up. Making a copy of the original won't work since I will be adding alot of data each day. Any suggestions? Is it possible to give her edit rights to only some of the columns and not others?

For example, lets say we sell Avon together - you have a spreadsheet of customers with all their contact info. Each day you add new customers along with the items they order. Then you need me to go in every few days after I deliver it to them and update it with the actual amount of the cash we received from them, and mark them paid on the spreadsheet. But you think I'm a dumbass and you're afraid I will accidently screw up the client data you spent weeks entering. How do you allow me to update my info yet insure that you're info stays safe?
Hi,

Basically you are trying to do what people have been doing to excel since its inception: using it for stuff its not meant for! Sorry but it is the truth... Excel is a really fancy calculator... If you remember that it will serve you well and work good. You may be able to twist it to do what you are asking but in time it will hit a wall in some way and you will spend more time fiddling with excel then actually doing what you set out to do. It is like trying to dig a swimming pool with a snow shovel, it can be done but a backhoe will do it allot better and faster. Yes you may have to learn something new but think of how many pools you can dig after learning

The example you cite would be served by a database backed application of some kind. A point of sale or even a crm system does what you are looking for quite well and you can find some free and open source out there and even internet based ones can be found.

You can use excel to do this and even Google docs will do this but neither is really meant to.

Go to Google and search this term : free sales software for small business

No quotes or anything like that when you search and this will give you an idea of what is available and maybe save you a whole lot of time down the line and actually help you accomplish what you want to do which I'm pretty sure is NOT spend all your time fiddling with excel or bending some other software to your will, against its will.

Too many years in IT and have seen things like this waste so many people's time not to chime in. Good luck and pm me with more specific information if you would like some help but if you are doing something like your example than that search should get you on the right path.

Rafal



Sent from my A100 using Tapatalk
rroginela is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 04:17 PM   #11
GeToChKn
Planted Tank Guru
 
GeToChKn's Avatar
 
PTrader: (1/100%)
Join Date: Apr 2011
Location: Hamilton, Ontario, Canada
Posts: 5,116
Default

Quote:
Originally Posted by wendyjo View Post
These 2 files would be shared on a network. GeTo, I don't think that would work in this case. There is going to be alot of info added each day to File A - too much to be copying over to File B one cell at a time (unless I misunderstood you). But I will play around with your suggestions and see exactly how it works.
That's why you would use the autofill handle to copy the first cell down and over as many times as you needed and only have to do it once. Of course when you do a relational link to another spreadsheet, Excel automatically makes the formula an absolute reference, so you have to edit before you use the autofil handle.
__________________
20g platy, , 2 x 10g shrimp, 3 x 20g shrimp, 7.5g shrimp and 1 great dane/mastiff puppy.

Sump Pimp #2

My Tanks and my shrimps
GeToChKn is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 09:48 PM   #12
wendyjo
Planted Tank Guru
 
PTrader: (7/100%)
Join Date: Feb 2009
Location: DC area
Posts: 2,989
Default

I think I found a way to lock some cells and unlock others so I can control what the other person has access to. I'll test it Monday, but I think that should work for my needs. She can update her stuff but not be able to touch the data that I've entered.
wendyjo is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 11:02 PM   #13
dbosman@msu.edu
Planted Tank Guru
 
PTrader: (16/100%)
Join Date: Jul 2008
Location: East Lansing, MI, USA
Posts: 2,710
Default

GeToChKn said it best with a specific answer. Too many people diss Excel because they aren't familiar with the power behind it or apps built to run with it. There are entire companies still running on tricked out Excel work books.

http://blogs.technet.com/b/office201...-on-excel.aspx
http://www.advanced-excel.com/corporate_budgeting.html
http://www.advanced-excel.com/what_is_excel.html
http://www.exceluser.com/
http://www.billbak.com/2010/05/i-t-c...crosoft-excel/
dbosman@msu.edu is offline   Reply With Quote Quick reply to this message
Old 03-09-2012, 11:25 PM   #14
GeToChKn
Planted Tank Guru
 
GeToChKn's Avatar
 
PTrader: (1/100%)
Join Date: Apr 2011
Location: Hamilton, Ontario, Canada
Posts: 5,116
Default

Quote:
Originally Posted by dbosman@msu.edu View Post
GeToChKn said it best with a specific answer. Too many people diss Excel because they aren't familiar with the power behind it or apps built to run with it. There are entire companies still running on tricked out Excel work books.

http://blogs.technet.com/b/office201...-on-excel.aspx
http://www.advanced-excel.com/corporate_budgeting.html
http://www.advanced-excel.com/what_is_excel.html
http://www.exceluser.com/
http://www.billbak.com/2010/05/i-t-c...crosoft-excel/

Exactly. I've done stuff with Excel that would baffle people that it's even Excel. I disagree with the comment that it's basically a calculator. Add in VBA behind the scenes and you can do amazing stuff with it.
__________________
20g platy, , 2 x 10g shrimp, 3 x 20g shrimp, 7.5g shrimp and 1 great dane/mastiff puppy.

Sump Pimp #2

My Tanks and my shrimps
GeToChKn is offline   Reply With Quote Quick reply to this message
Old 03-10-2012, 05:33 AM   #15
rroginela
Planted Tank Obsessed
 
rroginela's Avatar
 
PTrader: (1/100%)
Join Date: Sep 2011
Location: CHicago, IL
Posts: 311
Default

Quote:
Originally Posted by GeToChKn View Post
Exactly. I've done stuff with Excel that would baffle people that it's even Excel. I disagree with the comment that it's basically a calculator. Add in VBA behind the scenes and you can do amazing stuff with it.
The issue is that a person trying to accomplish a specific task does not need something that needs to be customized when a specific tool is available that does that specific job. I could do what he wants with Word and Access and Excel or all 3 at the same time that isn't the issue. For you it may be profitable and even useful to do all this stuff with Excel because your job is to work with Excel, unfortunately this person sounds like he/she is selling something so for them to spend a bunch of time customizing Excel seems like a waste of time when he/she could setup a POS or CRM that would do all of that just once and not worry OR go through a bunch of hoops to make Excel do things.

Sometimes a general tool is great like if he wanted to export a bunch of stuff out of his CRM and make a pretty chart or find where he needed to do some price adjustment to optimize profits, those things Excel does and does really well now if you have an Excel expert on staff that can work excel and VBA into a very specific tool great, but most ppl who are 2 employees usually fill their time with doing what they do to make money not spend a bunch of time mastering Excel, just not worth it. Software is a tool but it does not mean that you should be hammering nails with a crescent wrench! It will do it but not as good as a hammer. Or maybe if you just grind the crescent wrench so it has a flat surface on one side then it will do it better... Excel is a powerful tool but it can't... correction it shouldn't try to do everything.

What happens when the there is a bunch of entries in there and now you want to gleam some meaning full data out of it or you are looking for something very specific 2 years down the road sorry Excel search function is not that great and then to report on that data is a bunch of copy paste and that data then is its own little island of data that has nothing to do with other data (oh wait you can write a VBA script to search multi cells oh wait you have to learn VBA and waste 4 hours or 4 days depending on your abilities that could have been done in 10 minutes).

Pick your poison and do what you like but after nearly 20 years of seeing people punish themselves by running whole offices on Excel and wasting their time learning more and more intricate ways of doing things with a general tools only to run into road blocks... OR better yet pay someone to do it for them, go for it. And one sheet becomes 3 and 3 becomes 5 and all of a sudden you have to look in 3 different places to find if you shipped a widget... It is a calculator with a bunch of stuff to manipulate numbers in a really fancy way... That is what it is designed and made for the fact that it is flexible and people have managed to stretch it does not mean that it does those other things BETTER then a tool that is specifically made to do it. If you want to do CRM and POS things with a spreadsheet then please by all means do it and enjoy.

Good Luck,
Rafal
rroginela is offline   Reply With Quote Quick reply to this message
Reply

Quick Reply
Message:
Options

Register Now

In order to be able to post messages on the The Planted Tank Forum forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.
User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.
Password:
Confirm Password:
Email Address
Please enter a valid email address for yourself.
Email Address:

Log-in

Human Verification

In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.



Thread Tools
Display Modes

Posting Rules
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 07:14 PM.


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright Planted Tank LLC 2012