Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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 -- 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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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 EditReplace to replace the text: D4 with D10 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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. |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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. |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
Max
I hadn't noticed that you had corrected my formula. Many thanks -- Regards Roger Govier "Max" wrote in message ... 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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
|
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|