Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're in?
Hi Guys
I'm sure "no" is probably the answer to the question, but its always worth a try! I've been putting together a workbook with the original intention of having a worksheet for each of our customers holding details of equipment they have on a maintenance contract. I've created 3 worksheets - the 1st is called Picklist and just has 2 columns (item and price), the 2nd is called Customers and has about 15 columns holding details about all our customers (e.g. name, address etc) and the 3rd is the actual contract "template". I've created the "template" so that I can cut down on the great deal of typing usually necessary when renewal times come around, and have used VLOOKUP as much as possible to pull information from the Picklist and Customer worksheets. I had intended to keep the original contract worksheet blank and then copy it to a new one for each customer. Now comes the big BUT... BUT, I've now discovered that you can't copy a worksheet and keep all the data in cells that have more than 255 characters. (Quite infuriating since a large portion of the Contract worksheet is text, which probably means our contracts are incredibly waffly, but as I'm no legal eagle I'm not about to mess around with legal speak!) So...I was wondering whether I could save the Contract worksheet as a proper Excel template and take it out of the workbook altogether, so just leaving Picklist and Customers, but still be able to use VLOOKUP to reference them? If anybody knows, or, if anybody has a much less convoluted idea for doing what I need to do then I'd be grateful if you could put me out of my misery! Many thanks! Ali |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're in?
Where did you get this idea?
Copying a worksheet does restrict you to 255 characters in a cell. Copying a cell from worksheet A to Worksheet B does not restrict you to 255 characters in a cell. Aside from this, maybe save the 3 sheets as a Template(*.xlt) with the Contract sheet blank except for formulas and formatting. When you select FileNewTemplate and click on your template, a workbook will open based upon that Template. Fill in the Contract sheet for a customer and save. Gord Dibben MS Excel MVP On Mon, 5 Feb 2007 14:06:00 -0800, MeWivFree wrote: BUT, I've now discovered that you can't copy a worksheet and keep all the data in cells that have more than 255 characters. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're i
Where did you get this idea?
Its an idea I came up with myself. At the moment I have a separate Word document for each customer contract, and at contract renewal time have to not only make sure their contact details are still correct but also make changes to the equipment they've got and prices we charge to maintain it as necessary. Bit of a pain! So I thought it would be easier to have one workbook that keeps track of everybody so I at the very least only have to change the equipment charges in one place. Copying a worksheet does restrict you to 255 characters in a cell. Copying a cell from worksheet A to Worksheet B does not restrict you to 255 characters in a cell. There's too many cells in the original worksheet which would defeat the purpose of making my life easier. Aside from this, maybe save the 3 sheets as a Template(*.xlt) with the Contract sheet blank except for formulas and formatting. When you select FileNewTemplate and click on your template, a workbook will open based upon that Template. Fill in the Contract sheet for a customer and save. Sounds like a good compromise Gord - I've been working with separate files for each customer up until now anyway but at least saving the whole workbook at as a template will mean that I'll still only have to change most things in one place! Many thanks for the help - I'll give it a go!! Ali |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're in?
Should read "does not"
Gord On Mon, 05 Feb 2007 15:03:47 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Copying a worksheet does restrict you to 255 characters in a cell. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're i
Did you mean "Excel" workbook and not "Word Document"?
Yes, the Template is the way to go. Just make sure when you Save As the original it is saved as MS Excel Template(*.xlt) Name it and let Excel add the .xlt extension. Gord On Mon, 5 Feb 2007 15:20:00 -0800, MeWivFree wrote: Where did you get this idea? Its an idea I came up with myself. At the moment I have a separate Word document for each customer contract, and at contract renewal time have to not only make sure their contact details are still correct but also make changes to the equipment they've got and prices we charge to maintain it as necessary. Bit of a pain! So I thought it would be easier to have one workbook that keeps track of everybody so I at the very least only have to change the equipment charges in one place. Copying a worksheet does restrict you to 255 characters in a cell. Copying a cell from worksheet A to Worksheet B does not restrict you to 255 characters in a cell. There's too many cells in the original worksheet which would defeat the purpose of making my life easier. Aside from this, maybe save the 3 sheets as a Template(*.xlt) with the Contract sheet blank except for formulas and formatting. When you select FileNewTemplate and click on your template, a workbook will open based upon that Template. Fill in the Contract sheet for a customer and save. Sounds like a good compromise Gord - I've been working with separate files for each customer up until now anyway but at least saving the whole workbook at as a template will mean that I'll still only have to change most things in one place! Many thanks for the help - I'll give it a go!! Ali |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're i
Sorry Gord, that was a bit confusing!
I did mean to put "Word document" as I use a Word template at the moment but have now recreated it within Excel. Thanks again! Ali "Gord Dibben" wrote: Did you mean "Excel" workbook and not "Word Document"? Yes, the Template is the way to go. Just make sure when you Save As the original it is saved as MS Excel Template(*.xlt) Name it and let Excel add the .xlt extension. Gord On Mon, 5 Feb 2007 15:20:00 -0800, MeWivFree wrote: Where did you get this idea? Its an idea I came up with myself. At the moment I have a separate Word document for each customer contract, and at contract renewal time have to not only make sure their contact details are still correct but also make changes to the equipment they've got and prices we charge to maintain it as necessary. Bit of a pain! So I thought it would be easier to have one workbook that keeps track of everybody so I at the very least only have to change the equipment charges in one place. Copying a worksheet does restrict you to 255 characters in a cell. Copying a cell from worksheet A to Worksheet B does not restrict you to 255 characters in a cell. There's too many cells in the original worksheet which would defeat the purpose of making my life easier. Aside from this, maybe save the 3 sheets as a Template(*.xlt) with the Contract sheet blank except for formulas and formatting. When you select FileNewTemplate and click on your template, a workbook will open based upon that Template. Fill in the Contract sheet for a customer and save. Sounds like a good compromise Gord - I've been working with separate files for each customer up until now anyway but at least saving the whole workbook at as a template will mean that I'll still only have to change most things in one place! Many thanks for the help - I'll give it a go!! Ali |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're i
I get this message when I try to make a copy of the worksheet:
"The sheet you are copying has cells that contain more than 255 characters. When you copy the entire sheet, only the first 255 characters in each cell are copied. To copy all of the characters, copy the cells to a new sheet instead of copying the entire sheet." Ali "Gord Dibben" wrote: Should read "does not" Gord On Mon, 05 Feb 2007 15:03:47 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Copying a worksheet does restrict you to 255 characters in a cell. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're i
So copy the sheet.
After you get that warning message go back to the original sheet select all the cells edit|copy Then off to the newly copied sheet Edit|paste MeWivFree wrote: I get this message when I try to make a copy of the worksheet: "The sheet you are copying has cells that contain more than 255 characters. When you copy the entire sheet, only the first 255 characters in each cell are copied. To copy all of the characters, copy the cells to a new sheet instead of copying the entire sheet." Ali "Gord Dibben" wrote: Should read "does not" Gord On Mon, 05 Feb 2007 15:03:47 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Copying a worksheet does restrict you to 255 characters in a cell. -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you reference a worksheet outside of the workbook you're i
Thanks for the clarification.
Have not run into that before, but defeinitely got the "more than 255" message when I tested. Learn new stuff every day. See Dave P's posting. Gord Dibben MS Excel MVP On Tue, 6 Feb 2007 02:46:00 -0800, MeWivFree wrote: I get this message when I try to make a copy of the worksheet: "The sheet you are copying has cells that contain more than 255 characters. When you copy the entire sheet, only the first 255 characters in each cell are copied. To copy all of the characters, copy the cells to a new sheet instead of copying the entire sheet." Ali "Gord Dibben" wrote: Should read "does not" Gord On Mon, 05 Feb 2007 15:03:47 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Copying a worksheet does restrict you to 255 characters in a cell. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit-Replace worksheet reference nightmare | Excel Discussion (Misc queries) | |||
worksheet to worksheet . . . | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Reference & update destin. cells on 1st machine from source workbook on 2nd machine. | Excel Discussion (Misc queries) | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions |