| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Newbie & linked wordksheets = mass confusion
The concept sounded simple. Copy the cells from the main worksheet and past it as a link in another. What I thought this meant was that any changes made in the original worksheet would automatically be made in the linked worksheet. Consider the database I am using as basically an extensive address books with all sorts of information - some missing when original entries are made and added later, other existing entries changed, and finally additions to the database (new rows) The only changes that were quickly obvious in the linked worksheet were rows of #ref! in cells that were linked to rows that had been deleted in the original, and a cursory look showed that changes had not been made or additions added in the linked data ---- so clearly I don't get it. Can you help? Thanks, Trudy -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#2
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
Hi Trudy, I found that the same problems arose when I did the same thing, Basically what I did was made a Main Sheet that had a command button that brought up a data form for the different sheets that hold data. In that data form you would be able to add info delete info without disturbing the cells that would affect the sheet with all of your formulas. If you would prefer to just bring the form up on the sheet with your data just simply click on cell a1 and go to Data/Form. It will not work if you have any merged cells. If you have mergerd cells you will probably have to do something different. If you would like to create a command button for bringing the form up let me know and I'll try to guide you through it. Hope this helps, Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#3
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
My guess is that you're unnecessarily linking. Why do you need the same
information in multiple workbooks? ************ Anne Troy www.OfficeArticles.com "lburg801" wrote in message ... The concept sounded simple. Copy the cells from the main worksheet and past it as a link in another. What I thought this meant was that any changes made in the original worksheet would automatically be made in the linked worksheet. Consider the database I am using as basically an extensive address books with all sorts of information - some missing when original entries are made and added later, other existing entries changed, and finally additions to the database (new rows) The only changes that were quickly obvious in the linked worksheet were rows of #ref! in cells that were linked to rows that had been deleted in the original, and a cursory look showed that changes had not been made or additions added in the linked data ---- so clearly I don't get it. Can you help? Thanks, Trudy -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#4
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
as soon as you delete any cells from the original range you will get the ref
errors in the other sheets because the links are now "suspect"Its best if you dont actually delete the cells just their contents.Without giving us more information on what information you are actually linking we cant really help -- paul remove nospam for email addy! "lburg801" wrote: The concept sounded simple. Copy the cells from the main worksheet and past it as a link in another. What I thought this meant was that any changes made in the original worksheet would automatically be made in the linked worksheet. Consider the database I am using as basically an extensive address books with all sorts of information - some missing when original entries are made and added later, other existing entries changed, and finally additions to the database (new rows) The only changes that were quickly obvious in the linked worksheet were rows of #ref! in cells that were linked to rows that had been deleted in the original, and a cursory look showed that changes had not been made or additions added in the linked data ---- so clearly I don't get it. Can you help? Thanks, Trudy -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#5
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
keithl816 Wrote: Hi Trudy, I found that the same problems arose when I did the same thing, Basically what I did was made a Main Sheet that had a command button that brought up a data form for the different sheets that hold data. In that data form you would be able to add info delete info without disturbing the cells that would affect the sheet with all of your formulas. If you would prefer to just bring the form up on the sheet with your data just simply click on cell a1 and go to Data/Form. It will not work if you have any merged cells. If you have mergerd cells you will probably have to do something different. If you would like to create a command button for bringing the form up let me know and I'll try to guide you through it. Hope this helps, Larry I could definitely use more help. I've been reading about data forms. Typical of a newbie, I tried the last of your suggestions, made lots of changes - and then cliked "Close". Nnone of the changes were saved. I think I need to look more clearly at my options before closing a window!  -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#6
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
Hi Trudy, Before closing the data form, hit the enter key after you enter your data for that row. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#7
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
paul Wrote: as soon as you delete any cells from the original range you will get the ref errors in the other sheets because the links are now "suspect"Its best if you dont actually delete the cells just their contents.Without giving us more information on what information you are actually linking we cant really help -- paul remove nospam for email addy Paul, this is church database to which names of visitors and their addresses are added for a period of time. When they don't continue to attend, the row is deleted. I had wondered if there were an option to archive these rows instead, but did not know if that would make a diference. Thanks, Trudy -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#8
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
Trudy, When using data form always hit enter key after completion of data entry for that row. If you choose to delete a row, use the delete button on the data form. It will automatically delete your row without affecting the form with the formulas in it. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#9
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
Anne, I know I read somewhere this past week, a comment made by someone responding to another post, that creating many worksheets was unnecessary since the reasons most users stated for doing so could be accomplished by writing macros to do each job, with less work and fewer problems. I am so green! I am taking this on in an emergency situation - the church secrectary died - and she is the only one who knew anything about what she did or how she did it. The data that I am working with is basically what one would find in an extensive address book, with a few more columns pertinent to church activities. There is a separate financial database. One of the reasons I tried to create a second worksheet was because there are couples with different last names which creates a problem in printing labels when a couple shares the same surname. The following is one of the suggestions to deal with that problem, but I ran into a lot of trouble trying to copy it to other cells. Rather than being read as a formula, it became the text inside the cells. I do not know how to write a formula AND APPLY IT. It seems there should be a way to apply a fomula to an entire column and have it automatically covert the cell numbers to those of each row. Is there? Thanks, Trudy You could use a 'helper' column to sort by... for example A:First_Name1, B:Last_Name1, C:AND, D:First_Name2, E:Last_Name2, F:SORT F:=if(isblank(B1),E1,B1) and copy it down the column. This will put Last_Name1 in F if it exists and Last_Name2 if it does not. Note: the = sign needs to be at the beggining of the formula I think this is the easiest way that you won't have to rework the whole spreadsheet...again. Anne Troy Wrote: My guess is that you're unnecessarily linking. Why do you need the same information in multiple workbooks? ************ Anne Troy www.OfficeArticles.com "lburg801" wrote in message ... The concept sounded simple. Copy the cells from the main worksheet and past it as a link in another. What I thought this meant was that any changes made in the original worksheet would automatically be made in the linked worksheet. Consider the database I am using as basically an extensive address books with all sorts of information - some missing when original entries are made and added later, other existing entries changed, and finally additions to the database (new rows) The only changes that were quickly obvious in the linked worksheet were rows of #ref! in cells that were linked to rows that had been deleted in the original, and a cursory look showed that changes had not been made or additions added in the linked data ---- so clearly I don't get it. Can you help? Thanks, Trudy -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#10
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
Trudy: Check your private messages at excelforum.com
************ Anne Troy www.OfficeArticles.com "lburg801" wrote in message ... Anne, I know I read somewhere this past week, a comment made by someone responding to another post, that creating many worksheets was unnecessary since the reasons most users stated for doing so could be accomplished by writing macros to do each job, with less work and fewer problems. I am so green! I am taking this on in an emergency situation - the church secrectary died - and she is the only one who knew anything about what she did or how she did it. The data that I am working with is basically what one would find in an extensive address book, with a few more columns pertinent to church activities. There is a separate financial database. One of the reasons I tried to create a second worksheet was because there are couples with different last names which creates a problem in printing labels when a couple shares the same surname. The following is one of the suggestions to deal with that problem, but I ran into a lot of trouble trying to copy it to other cells. Rather than being read as a formula, it became the text inside the cells. I do not know how to write a formula AND APPLY IT. It seems there should be a way to apply a fomula to an entire column and have it automatically covert the cell numbers to those of each row. Is there? Thanks, Trudy You could use a 'helper' column to sort by... for example A:First_Name1, B:Last_Name1, C:AND, D:First_Name2, E:Last_Name2, F:SORT F:=if(isblank(B1),E1,B1) and copy it down the column. This will put Last_Name1 in F if it exists and Last_Name2 if it does not. Note: the = sign needs to be at the beggining of the formula I think this is the easiest way that you won't have to rework the whole spreadsheet...again. Anne Troy Wrote: My guess is that you're unnecessarily linking. Why do you need the same information in multiple workbooks? ************ Anne Troy www.OfficeArticles.com "lburg801" wrote in message ... The concept sounded simple. Copy the cells from the main worksheet and past it as a link in another. What I thought this meant was that any changes made in the original worksheet would automatically be made in the linked worksheet. Consider the database I am using as basically an extensive address books with all sorts of information - some missing when original entries are made and added later, other existing entries changed, and finally additions to the database (new rows) The only changes that were quickly obvious in the linked worksheet were rows of #ref! in cells that were linked to rows that had been deleted in the original, and a cursory look showed that changes had not been made or additions added in the linked data ---- so clearly I don't get it. Can you help? Thanks, Trudy -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 -- lburg801 ------------------------------------------------------------------------ lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
|
#11
|
|||
|
|||
|
Newbie & linked wordksheets = mass confusion
Trudy, Check your PM. I sent you directions for what you requested but it is not showing up on my side as being sent. Only the second reply has. Let me know if you received it. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287 View this thread: http://www.excelforum.com/showthread...hreadid=480391 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I automatically open linked documents in Excel? | New Users to Excel | |||
Linear Mode
