David
Workbook received, amended and returned.
my apologies, I missed out some parentheses in the formula I typed.
In cell A4 of your Log, the formula should be
= IF(INDIRECT("'REQ ("&ROW(A1)&")'!D4")="","",INDIRECT("'REQ
("&ROW(A1)&")'!D4"))
There is no need for the $ sign as absolutes for the formula, as the cell
reference in included within the quotes and will not alter as you copy down.
The formula is the same for other cells on the log, just change the final
cell reference accordingly.
--
Regards
Roger Govier
"dfmstes1" wrote in message
...
Thanks for quick reply Roger, but as I said, I'm a rookie, and obviously
I'm
either doing something wrong or don't have a clue...probably the latter.
Anyway, Excel says there's an error and I'm not smart enough to know where
it
is. Maybe I'm trying to put the formula in the wrong place?
I appreciate your help very much, but I think I'm a little lost. Can you
be
a little more precise on what you want me to do and where you want me to
put
the formula please?
Thanks again.
"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.