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.

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

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

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

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

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

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

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

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

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

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

Second Verse: I promised you some code to make setting this up easier? Here
it is.

RULE #1: Always try new code on a COPY of your workbook. Especially in this
case where if I've messed up something it may mess up your work to date and
I'd hate to do that. So do the following in a copy of your workbook.

Open the workbook. Press [Alt]+[F11] to open the VB Editor (VBE). From the
VBE menu, choose Insert | Module.
Copy the code below and paste it into the code module that showed up in the
VBE.
Make any changes to sheet name or cell addresses that you need to.
Also change the "To 149" part of the For LC = 1 To 149 statement to match
your worksheet layout. With only 5 IDs in each half of your sheet presently,
you may want to just change it to For LC = 1 to 4
Close the VBE.

The formulas need to be set up in the top two IDs (left/right half of sheet)
before running the macro. To run it, use Tools | Macro | Macros and select
its name in the list and click the [Run] button.

If you can't run it, check your Macro Security level: Tools | Macro |
Security (you may have to open up the list to see that). In the dialog that
opens, choose the Security Level tab and set it to MEDIUM. I don't recommend
LOW - too unsafe. With medium you'll get a warning when workbooks contain
code and be given the opportunity to enable or disable them. Close the
dialog. Close and reopen Excel (this must be done for the new Security
Setting to take effect). Reopen your workbook, and when prompted, choose
[Enable] for macro operation.

Finally, here's the code:

Sub CopyIDFormulas()
Const IDsheetName = "Sheet1" ' change as required
Const GapSize = 13
'these are for Left side of sheet of IDs
'change cell addresses as required
Const FirstLeftNameCell = "B4"
Const FirstLeftCityCell = "B6"
Const FirstLeftStateCell = "C6"
Const FirstLeftZipCell = "D6"

Dim rngLeftName As Range
Dim rngLeftCity As Range
Dim rngLeftState As Range
Dim rngLeftZip As Range
'these are for Right side of sheet of IDs
'change cell addresses as required
Const FirstRightNameCell = "I4"
Const FirstRightCityCell = "I6"
Const FirstRightStateCell = "J6"
Const FirstRightZipCell = "K6"

Dim rngRightName As Range
Dim rngRightCity As Range
Dim rngRightState As Range
Dim rngRightZip As Range

Dim LC As Long ' Loop Counter
Dim rOffset As Long ' formula row offsets

'a little preparation
Set rngLeftName = Sheets(IDsheetName).Range(FirstLeftNameCell)
Set rngLeftCity = Sheets(IDsheetName).Range(FirstLeftCityCell)
Set rngLeftState = Sheets(IDsheetName).Range(FirstLeftStateCell)
Set rngLeftZip = Sheets(IDsheetName).Range(FirstLeftZipCell)
Set rngRightName = Sheets(IDsheetName).Range(FirstRightNameCell)
Set rngRightCity = Sheets(IDsheetName).Range(FirstRightCityCell)
Set rngRightState = Sheets(IDsheetName).Range(FirstRightStateCell)
Set rngRightZip = Sheets(IDsheetName).Range(FirstRightZipCell)

'change 149 to be total number of ID cards to be made down
'the left side of the worksheet -1
' so if you make 150 side-by-side (300 total) then
' 300/2 = 150-1 = 149
For LC = 1 To 149 ' assumes 150 entries in LEFT side of ID sheet
rOffset = rOffset + GapSize
rngLeftName.Offset(rOffset, 0).Formula = _
rngLeftName.Formula
rngLeftCity.Offset(rOffset, 0).Formula = _
rngLeftCity.Formula
rngLeftState.Offset(rOffset, 0).Formula = _
rngLeftState.Formula
rngLeftZip.Offset(rOffset, 0).Formula = _
rngLeftZip.Formula

rngRightName.Offset(rOffset, 0).Formula = _
rngRightName.Formula
rngRightCity.Offset(rOffset, 0).Formula = _
rngRightCity.Formula
rngRightState.Offset(rOffset, 0).Formula = _
rngRightState.Formula
rngRightZip.Offset(rOffset, 0).Formula = _
rngRightZip.Formula
Next
'all done!
End Sub



"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

One last thought for the moment - I just realized you said you're still
designing the layouts of your ID cards. You may not even need the code I
gave you in that case. Once you get the top two cards (the ones using B4 and
I4 for the name formula) all set up and the formulas entered into them, then
you can copy those on down the sheet and the formulas will copy properly with
them - at least the ones I gave you and modifications properly made for other
similarly obtained from the linked sheet.

Just make sure the 'gap' between the starting row of each ID card area is
always the same, all the way down the 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

My comments are in between yours, below.

"JLatham" wrote:

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.

I see that. Somewhere along the line I changed it, and I know that makes a
difference. Thank you for your patience. I changed this formula again to
=OFFSET(Sheet2!$A$1,(ROW()-6)/13,0) because once I had more than one page,
the first page was different than the rest.

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).

That works perfectly (with an "!" that was in the first formula), but I used
131 instead of 147 because _right now_ there are 252 members. We get new ones
every year, though. It took two tries to get this right and have learned the
value of FIND and REPLACE in the process ;-)

I understand that the cards will print down the all left side and then on
the right. I'm not looking a gift horse in the mouth - absolutely fascinated!


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.

It is now B8, and I defined similar formulas for city, state and type (you
didn't know there was no zip code), based on your original formula above, all
of which work perfectly.


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.

This is a lot easier to understand than the 'Help' for OFFSET in Excel.
Thank you - it makes sense now.

So having put formulas as you explained, I picked one graphic and made one
page, then copied it - formulas, formatting, flowers and all - down 26 pages
and voila! all the names and other data of _all_ the members are there, in
addition to the other stuff that was just typed in, like the name of the
organization. It seems that the OFFSET command imports the data just right.

<snip

You're right, and I was feeling a little sheepish about it. I have saved it
and will edit it for use on the file. If nothing else, I will learn to work
with a macro, which I have never done.

I am absolutely stunned. I thought 'computer wizards' - helpful folks
willing to share their computer knowledge just because they have it - had
gone the way of the dodo bird.

Yet here you are, not only answering my uninformed questions, but providing
new and wonderful ways to use Excel.

Thank you for your time and your patience, and the code you wrote, from
which I can learn something totally new to me.


"JLatham" wrote:

One last thought for the moment - I just realized you said you're still
designing the layouts of your ID cards. You may not even need the code I
gave you in that case. Once you get the top two cards (the ones using B4 and
I4 for the name formula) all set up and the formulas entered into them, then
you can copy those on down the sheet and the formulas will copy properly with
them - at least the ones I gave you and modifications properly made for other
similarly obtained from the linked sheet.

Just make sure the 'gap' between the starting row of each ID card area is
always the same, all the way down the 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

Thank you for helping me to make this file do what it is supposed to do. Now
that it works, it just _so_ far superior to hand-writing all those cards,
which is what the last treasurer did.

And I have learned so much in the process. THANK YOU! THANK YOU! THANK YOU!

"JLatham" wrote:

One last thought for the moment - I just realized you said you're still
designing the layouts of your ID cards. You may not even need the code I
gave you in that case. Once you get the top two cards (the ones using B4 and
I4 for the name formula) all set up and the formulas entered into them, then
you can copy those on down the sheet and the formulas will copy properly with
them - at least the ones I gave you and modifications properly made for other
similarly obtained from the linked sheet.

Just make sure the 'gap' between the starting row of each ID card area is
always the same, all the way down the 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

You're quite welcome - and thanks for the acknowledgments and for the praise.

As you see from looking around, there are many, including D. who first
jumped in to help in this discussion, who are willing to share their
knowledge. You'll find so many here that I dare not mention so much as one
name without having to mention so many or risk slighting someone who doesn't
deserve it. I am a 'true believer' that knowledge shared is knowledge
multiplied: I share with you, you share with another, I share with another,
and suddenly the knowledge that was held by one is held by 4, and as long as
each Passes It On, then all are better for it.

"Ruthie" wrote:

Thank you for helping me to make this file do what it is supposed to do. Now
that it works, it just _so_ far superior to hand-writing all those cards,
which is what the last treasurer did.

And I have learned so much in the process. THANK YOU! THANK YOU! THANK YOU!

"JLatham" wrote:

One last thought for the moment - I just realized you said you're still
designing the layouts of your ID cards. You may not even need the code I
gave you in that case. Once you get the top two cards (the ones using B4 and
I4 for the name formula) all set up and the formulas entered into them, then
you can copy those on down the sheet and the formulas will copy properly with
them - at least the ones I gave you and modifications properly made for other
similarly obtained from the linked sheet.

Just make sure the 'gap' between the starting row of each ID card area is
always the same, all the way down the 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

I need to set up a basic monthly billing form in Excel 2003.
There will be five columns:
A=Date;B= Hours;C= Project Name;D= $ Rate;
E=Total for that date.

The bottom of the form needs to show the total numbers of hours worked and a
summary of the total billed for that date.

I am a dinosaur and would appreciate any help.
Thank you.
Howard Levine


"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.