Wow. This is amazing. This is _very_ cool.

OK, I used Sheet2 of IDCard.xls (the file which will be used to print ID
Cards) and put links to IDCardsList.xls (the file exported from my accounting
software) as you specified, and deleted the two $ signs. I copied the
formulas across and down so that sheet now has all the same data as the
exported file.

Then, in the cell B4 of IDCard.xls (sheet1) I used the formula
=OFFSET(Sheet2!$A$1,(ROW()-3)/14,0) because that is where the first name is
now. I copied that formula to the other name fields in the left column of
cards, with the following result:

card 1 (B4): 1st name on list
card 2 (B17): 2nd name on list
card 3 (B30): 2nd name on list (instead of 3rd)
card 4 (B43): 3rd name on list
card 5 (B56): 4th name on list

In the 6th card, at the top right of the first page, I used the formula
=OFFSET(Sheet2!$A$6,(ROW()-3)/14,0) and copied that formula to the other four
cards below it, with the following result:
card 6 (cell I4): 6th name on list
card 7 (I17): 7th name on list
card 8 (I30): 7th name on list (instead of 8th)
card 9 (I43): 8th name on list
card 10 (I56): 9th name on list

Without this different formula, the sixth card has the name of the first
person on the list. Since some cells give the right data and some don't, I
double-checked that the links to the exported file are correct, and re-copied
the formulas in IDCard.xls just to be sure I had put the right thing in the
right places. I don't understand this formula, so I'm not going to try to
edit it.

I did the same for the next data field, city, which is first in cell B6 of
IDCard.xls, using =OFFSET(Sheet2!$B$1,(ROW()-3)/14,0). Nine of the ten are
correct - only the last one is wrong.

At this point, I have only made one page of IDCard.xls because I'm still
fine-tuning graphics, but the last row of the last page will be 1890 - there
are 63 rows per page.

I'm feeling like I have opened a can of worms, but if you're willing to
continue, I'm all ears. Sorry it took me so long to reply, but I sent this
all once and got a 'server busy' error and had to write it all over again.

"JLatham" wrote:

Ruthie, This is a kind of round about way to do this, but I think the effort
to set up and update may be worth it to you in the end.

Start by adding a brand new worksheet to the workbook with the ID cards
sheet in it. Go to that sheet, with that data workbook open and in cell A1
of that sheet, make a link to cell A1 over in the workbook with the data
exported from the database. It will look something like
=[Exported.xls]Sheet1!$A$1

As before, edit that formula to remove the $ symbols. Now drag that formula
(using the technique I showed you earlier) over to the right far enough to
display all the columns of data you need to use. Then highlight that group
of cells in row 1 from A to where ever you stopped and using the right-most
cell, again drag the entire group down the sheet for as many rows as you
need. There are quicker ways, but this is one you should be familiar with by
now.

At this point you can close the Exported.xls workbook or whatever it's name
is. You have a copy of that data in this workbook to use in a friendlier
fashion. We will call this new sheet LinkedSheet.

Go to your ID card sheet and in cell B3, type this formula (this time you
NEED the $ signs)
=OFFSET(LinkedSheet!$C$3,(ROW()-3)/14,0)
to see if my idea is going to work, copy that exact formula, no changes at
all, into cell B17 and see if you don't get the second name in your list.

For your other cells in other areas of the ID cards, you can use the same
logic. Lets say your next entry from the record that goes into the first ID
card comes from LinkedSheet!G3 but it goes into F7 on the ID card sheet.
Put this formula into F7 on the ID card sheet
=OFFSET(LinkedSheet!$G$3,(ROW()-7)/14,0)
notice that all we've done here is change the column letter for the source
of information from LinkedSheet from C to G and we changed the -3 to -7
because this formula is entered on row 7 of the ID card sheet.

Copy that formula into F21 and it should pick up the proper information for
the second ID card.

IF this works, give me the following information once you've set up that new
LinkedSheet in your workbook AND manually set up the formulas in the first ID
card area:

****
tell us the name of the ID Card sheet and what four cells on it have your
formulas in them. Also let us know what the last used row number on the ID
card sheet is - should be somewhere down around 4200 or 4214?
****

I'll give you a little macro that'll copy the formulas out of the first ID
card record into the appropriate cells in the other 299 of them.

I think a nice effect of this is that in the future when the data is
exported to that Excel workbook, if they use the same filename in the same
location then your links in LinkedSheet will still work (you'll need to check
to see if all the information is there in case there are more records the
next time. But even if they use a new filename, you can use Edit | Links to
point to the new file and those external links will be updated to point into
it. This helps reduce maintenance of the LinkedSheet sheet.

While you're replying to this, I'll be working on the code - trust me, it'll
be short and easy to cut and paste into your workbook for future use if
needed.







"Ruthie" wrote:

Thank you for explaining that. I'll try again. I am trying to make ID cards
of each of ~300 people.

I have made a worksheet that will print as ID cards - ten on a page. For
example, there are ten cells which contain the 'name' field; the first is at
B3, the second is at B17, etc. These are near the center of each 'card' and
so are not contiguous. B3 and all the rest of the 'name' fields contain
=([filename]Sheet1!$C$3), which is the location of the first name in my
supporting worksheet. I can manually change the formula from ...$C$3 to
...$C$4 for the data to be copied into the next name field (cell B17), but I
was hoping for a shortcut, since there are so many.

I am trying to copy data from one other worksheet (data that was exported
from an accounting program) that is in tabular form - names in one column,
other data in subsequent columns.

I'm sorry I didn't make this clear before. Does that help? Any ideas how I
can do this?


"Peo Sjoblom" wrote:

The reason you don't get more answers is probably because nobody understands
what you want to do. How your data is setup etc. From your first post it
sounded as though you just wanted to be able to copy down the formula and
have C3 change into C4 and thus to return what's in C4. Since that wasn't
the case I have no idea what you want to do. If you mean that by copying the
formula somehow you will get the name of another workbook then that is not
possible. You would first need to get a list of all workbooks in question
ether by using a DOS command in windows to print out the names of the
workbooks to a text file or by doing the same using VBA in Excel. Then you
could use INDIRECT and refer to the list, it still requires that all
workbooks are open

There is no built in formula way that will give you

=([filename1]Sheet1!$C$3)

=([filename2]Sheet1!$C$3)

and so on


--
Regards,

Peo Sjoblom

"Ruthie" wrote in message
...
So, does any kind soul know how this can be done? I appreciate your
thoughts...

"Ruthie" wrote:

Thanks for explaining that, and it works! (I love it when I learn
something
new!) Unfortunately, the destination cells are not contiguous. The
worksheet
is a template for ID cards, ten to a page, and the cells containing
formulas
for imported data are spread out on the card.

I'm still trying to figure out how to modify the formulas without
changing
each one. I could do that, but there are ~300 cards with four imported
fields
in each. I just keep thinking there's got to be a better way to do
this...

"JLatham" wrote:

If the cells in the destination sheet are continuous, in a column or
row,
then as D. said, get rid of the $ symbol (as you show in last post),
Next
move the mouse pointer toward the lower right corner of the cell with
the
formula in it until it becomes a narrow + symbol. Click and hold and
drag
down the sheet (or across it) and formulas will be duplicated in the
cells
below/to the right and their address will change automatically. Search
Excel
Help for topic Fill Data that should help you some.

"Ruthie" wrote:

Thanks for your response. I have tried =([filename]Sheet1!C3) for the
first
name, and that works. I really don't understand what you're telling
me to do
besides get rid of the $ signs. I am trying to define a formula which
will
copy subsequent names on the list without having to type each cell
reference.
Is that possible?

"D." wrote:

On Jul 31, 9:34 pm, Ruthie
wrote:
I have built a worksheet in Excel to print ID cards using data in
another
worksheet exported from an accounting program. The field for the
first name
is =([filename]Sheet1!$C$3). Is there a way to use a variable to
copy the
next name in the file, ie, cell C4, without having to enter each
one
manually?

Sorry if this has already been covered. I love Excel and know it
will do SO
much more than I understand.

Thank you in advance.

You should be able to if you get rid of the absolutes ($ signs)
then you should be able to drag the formula down