Roger,
Sorry I couldn't get back to you until today, but I was out of pocket all
day yesterday. I would like to thank you so much for your help. I took the
file and was able to expand it and modify it to fit my needs with the help
you provided, and everything works perfectly. You are indeed a lifesaver.
And thank you Max as well. I see that your solution was about the same as
Roger's, but I wasn't able to do it by myself. As I said, I'm a rookie...a
hero as well thanks to you guys.
This problem is solved now and I'll move on to the next one.
Thanks again
David
"Roger Govier" wrote:
Hi
One way would be to make use of the Indirect() function and the Row()
function.
=ROW(A1) will return a value of 1 . As it is copied down it will change to
Row()A2 and return 2 and so on.
Try
= IF(INDIRECT("'REQ ("&ROW(A1)&")'!"&$D$10="","",
INDIRECT("'REQ ("&ROW(A1)&")'!"&$D$10)
--
Regards
Roger Govier
"dfmstes1" wrote in message
...
Thanks for the quick reply Max. I'll try to explain a little further.
The first tab I need to reference is labeled REQ (1), with REQ (2), being
next and so on. In cell D4 of each, there is a date that needs to be
referenced in cell B4 of the running log sheet. These cells go down
through
B154, with each cell being referenced to the next sheet. The same goes
for
cell D10, which goes to E4 through E154, again with each cell down the
column
being the corresponding cell on the next worksheet. The same goes for
D11,
D12, D18, and finally L46 which shows the total value of each worksheet.
All
the worksheets are identical by the way...copies of the first one.
What I came up with is this:
In A4 of the log sheet I have:
=IF('REQ (1)'!$D$4="","",'REQ (1)'!$D$4) referencing cell D4 of REQ
(1)
In E4 of the log sheet I have:
= IF('REQ (1)'!$D$10="","",'REQ (1)'!$D$10) referencing cell D10 of REQ
(1)
and so on across the row with each reference. This works fine when I copy
and paste. The problem comes when I try to copy and paste the formula(s)
down. I need to find some way to get the worksheet numbers to advance as
I
paste so I dont have to edit each cell with a different worksheet
reference.
I may well need to change the way I'm doing it, but I'm still a rookie at
this and thought I was on the right track. Hopefully you can set me
straight. Thanks again for your help.
"Max" wrote:
Post some examples of your link formulas and how you want it to propagate
in
copying across or down. There are probably ways to propagate it using
either
INDIRECT, OFFSET, INDEX, ROWS, COLUMNS or some combinations thereof
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dfmstes1" wrote in message
...
I have a an excel doc that I need the information entered to be
transferred
to a log. Each copy of the doc must be saved, so there are about 150
worksheet tabs, each one with a copy of the doc. What I want to do is
have
the information go to a log sheet as each doc is filled out in order to
keep
from filling in the information more than once. The problem is that
when
I
enter the formulas to transfer the data, I can't seem to copy and paste
into
the log without having to edit each formula's sheet reference. The
different
entries are kept from changing by using the $. Is there a way to get
the
worksheet tabs to move to the next one each time I paste the formulas
into
the log sheet row? Any help you can give will be greatly appreciated.
Thanx.