Ruthie,
Part of the problem is that you have 13 rows between groups and I'd planned
on 14, as I thought your first name was at row 3 (B3) instead of at row 4.
Change the formula at Sheet1 B4 to:
=OFFSET(Sheet2!$A$1,(ROW()-4)/13,0)
and things should work for the ID cards down the left side of the page.
We'll have to figure out what the correct starting cell for the formula in I4
is, I think it will be
=OFFSET(Sheet2$A$147,(ROW()-4)/13,0)
although since you'd said earlier you were doing 300 IDs at a time I'd have
actually expected it to be 151 (with 1-150 down the left side of the sheet).

It is important that the gap between rows with same type information (as the
name) be the same throughout all of your layout for the formula to work
without alteration in the middle of things.

You mentioned that the CITY was in B6, so other city entries should be at
B19, B32, etc., and at I6, I19 and I32, etc.

Just so you'll have an idea of what's going on, I'll explain how the formula
works:
OFFSET() needs at least 3 parameters:
1 - the reference location (for our left-column IDs that is always
Sheet2!$A$1)
2 - how many rows away from the reference location to get the information:
negative numbers are rows above that location, 0 is same row and positive
numbers are rows below it.
3 - how many columns away from the reference location to look for the
information: negative numbers are columns to the left of the reference, 0 is
same column as the reference, and positive numbers are columns to the right
of it. We always use 0 for the column offset because names are all in column
A. (and when you switch to City, it's always in B, which is why we use $B$1
in the city formula - etc.)

ROW() always returns the row number of the cell that the statement is in
when the () is left empty like that.

So, in your first formula in B4, with formula as (ROW()-4)/13 it works out
to be (4-4)/13 which is same as 0/14 or just plain zero, so we get zero rows
and zero column offset from Sheet2!$A$1, which is Sheet2!$A$1 itself.

When you get down to cell B17, the formula evaluates to (17-4)/13 = 13/13 =
1, so now the row offset is 1, and OFFSET(A1,1,0) would point to A2
(Sheet2!$A$2).

At B30, it's (30-4)/13 = 26/13 = 2 which points to Sheet2!$A$3. And so on
and so forth to the bottom of your ID card templates in the left half of your
sheet.

"Ruthie" wrote:

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