Can you reference a worksheet outside of the workbook you're in?

[MeWivFree]

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

 

[Gord Dibben]

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 File>New>Template 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

 

[MeWivFree]

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 File>New>Template 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

 

[Gord Dibben]

Should read "does not"

Gord

 

[Gord Dibben]

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

 

[MeWivFree]

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

 

[MeWivFree]

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

 

[Dave Peterson]

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

 

[Gord Dibben]

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