Help

[dfmstes1]

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.

 

[Max]

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

 

[dfmstes1]

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 don’t 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.

 

[Roger Govier]

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)

 

[dfmstes1]

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]

The formula I posted was based upon your formula for E4, so that is the cell
where it should be posted.

If you are still having problems, send me the file and I will take a look
To send direct email to
roger at technology4u dot co dot uk
Do the obvious with at and dot

 

[Max]

Here's a quick sample illustrating how you can get it to propagate using
INDIRECT and ROWS when you copy down from the top cell:
http://www.freefilehosting.net/download/3c1a8
Indirect.xls

In Log,

In A4, copied down:
=IF(INDIRECT("'REQ ("&ROWS($1:1)&")'!D4")="","",INDIRECT("'REQ
("&ROWS($1:1)&")'!D4"))

In E4, copied down:
=IF(INDIRECT("'REQ ("&ROWS($1:1)&")'!D10")="","",INDIRECT("'REQ
("&ROWS($1:1)&")'!D10"))


In Log (2),
Roger's earlier suggestion using ROW(A1), slightly amended

In A4, copied down:
= IF(INDIRECT("'REQ ("&ROW(A1)&")'!D4")="","",INDIRECT("'REQ
("&ROW(A1)&")'!D4"))

In E4, copied down:
= IF(INDIRECT("'REQ ("&ROW(A1)&")'!D10")="","",INDIRECT("'REQ
("&ROW(A1)&")'!D10"))


---

 

[Max]

And to "propagate" the top cells' formula from A4 to E4 .. just copy A4's
formula (from its formula bar) & paste directly into E4's formula bar. Then
use Edit>Replace to replace the text: D4 with D10

---

 

[Roger Govier]

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.

 

[dfmstes1]

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]

Max

I hadn't noticed that you had corrected my formula.
Many thanks

 

[Max]

Welcome, David.

---

 

[Max]

Welcome, Roger

---