Excel: Copying formulas, keeping column values and increment only their row numbers
up vote
0
down vote
favorite
What I wanted to do is to copy a page to another page (e.g. copy to page 2, page 3 and so on) and keeping the formulas which is linked from another sheet, more like a database for this page. the problem is the page has several formulas and i want to increment only the row numbers of the formulas.
Like This:
Sample Codes from page 1,
Page 1
=AWS_db!$B7, =AWS_db!$C7, =AWS_db!$D7&AWS_db!$E7
I tried using "$" this symbol but it didn't work the way I wanted it to be
This is what I wanted to happen:
Page 2
=AWS_db!$B8, =AWS_db!$C8, =AWS_db!$D8&AWS_db!$E8
Page 3
=AWS_db!$B9, =AWS_db!$C9, =AWS_db!$D9&AWS_db!$E9
and so on
So every time I copy the page to another page I will not have to edit the formulas one by one, I got 1000 rows on the database page so I need 1000 pages
excel formula
edited Nov 5 at 12:55
Imran Malek
1,5001714
asked Nov 5 at 1:51
MTO
11
add a comment |
up vote
0
down vote
favorite
What I wanted to do is to copy a page to another page (e.g. copy to page 2, page 3 and so on) and keeping the formulas which is linked from another sheet, more like a database for this page. the problem is the page has several formulas and i want to increment only the row numbers of the formulas.
Like This:
Sample Codes from page 1,
Page 1
=AWS_db!$B7, =AWS_db!$C7, =AWS_db!$D7&AWS_db!$E7
I tried using "$" this symbol but it didn't work the way I wanted it to be
This is what I wanted to happen:
Page 2
=AWS_db!$B8, =AWS_db!$C8, =AWS_db!$D8&AWS_db!$E8
Page 3
=AWS_db!$B9, =AWS_db!$C9, =AWS_db!$D9&AWS_db!$E9
and so on
So every time I copy the page to another page I will not have to edit the formulas one by one, I got 1000 rows on the database page so I need 1000 pages
excel formula
edited Nov 5 at 12:55
Imran Malek
1,5001714
asked Nov 5 at 1:51
MTO
11
Could you clarify why you need 1 item per sheet instead of just having all the items on the one sheet? I'm not seeing the use case, and so while I might be able to answer, it might not meet the actual need.
– JMichael
Nov 5 at 17:55
There's only two work sheets in one workbook, the database: file named AWS_db and the result which is the Water Bill as shown in the picture above. I wanted to create several pages in one worksheet base on the number of rows in the database, the data comes from the same columns A,B,C etc. but different rows 1,2,3 etc. like in 1st page would show data from 1st row, 2nd page 2nd row, 3rd page 3rd row and so on
– MTO
2 days ago
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
What I wanted to do is to copy a page to another page (e.g. copy to page 2, page 3 and so on) and keeping the formulas which is linked from another sheet, more like a database for this page. the problem is the page has several formulas and i want to increment only the row numbers of the formulas.
Like This:
Sample Codes from page 1,
Page 1
=AWS_db!$B7, =AWS_db!$C7, =AWS_db!$D7&AWS_db!$E7
I tried using "$" this symbol but it didn't work the way I wanted it to be
This is what I wanted to happen:
Page 2
=AWS_db!$B8, =AWS_db!$C8, =AWS_db!$D8&AWS_db!$E8
Page 3
=AWS_db!$B9, =AWS_db!$C9, =AWS_db!$D9&AWS_db!$E9
and so on
So every time I copy the page to another page I will not have to edit the formulas one by one, I got 1000 rows on the database page so I need 1000 pages
excel formula
edited Nov 5 at 12:55
Imran Malek
1,5001714
asked Nov 5 at 1:51
MTO
11
What I wanted to do is to copy a page to another page (e.g. copy to page 2, page 3 and so on) and keeping the formulas which is linked from another sheet, more like a database for this page. the problem is the page has several formulas and i want to increment only the row numbers of the formulas.
Like This:
Sample Codes from page 1,
Page 1
=AWS_db!$B7, =AWS_db!$C7, =AWS_db!$D7&AWS_db!$E7
I tried using "$" this symbol but it didn't work the way I wanted it to be
This is what I wanted to happen:
Page 2
=AWS_db!$B8, =AWS_db!$C8, =AWS_db!$D8&AWS_db!$E8
Page 3
=AWS_db!$B9, =AWS_db!$C9, =AWS_db!$D9&AWS_db!$E9
and so on
So every time I copy the page to another page I will not have to edit the formulas one by one, I got 1000 rows on the database page so I need 1000 pages
excel formula
excel formula
edited Nov 5 at 12:55
Imran Malek
1,5001714
asked Nov 5 at 1:51
MTO
11
edited Nov 5 at 12:55
Imran Malek
1,5001714
asked Nov 5 at 1:51
MTO
11
edited Nov 5 at 12:55
Imran Malek
1,5001714
edited Nov 5 at 12:55
Imran Malek
1,5001714
edited Nov 5 at 12:55
Imran Malek
1,5001714
1,5001714
asked Nov 5 at 1:51
MTO
11
asked Nov 5 at 1:51
MTO
11
asked Nov 5 at 1:51
MTO
11
11
Could you clarify why you need 1 item per sheet instead of just having all the items on the one sheet? I'm not seeing the use case, and so while I might be able to answer, it might not meet the actual need.
– JMichael
Nov 5 at 17:55
There's only two work sheets in one workbook, the database: file named AWS_db and the result which is the Water Bill as shown in the picture above. I wanted to create several pages in one worksheet base on the number of rows in the database, the data comes from the same columns A,B,C etc. but different rows 1,2,3 etc. like in 1st page would show data from 1st row, 2nd page 2nd row, 3rd page 3rd row and so on
– MTO
2 days ago
add a comment |
Could you clarify why you need 1 item per sheet instead of just having all the items on the one sheet? I'm not seeing the use case, and so while I might be able to answer, it might not meet the actual need.
– JMichael
Nov 5 at 17:55
There's only two work sheets in one workbook, the database: file named AWS_db and the result which is the Water Bill as shown in the picture above. I wanted to create several pages in one worksheet base on the number of rows in the database, the data comes from the same columns A,B,C etc. but different rows 1,2,3 etc. like in 1st page would show data from 1st row, 2nd page 2nd row, 3rd page 3rd row and so on
– MTO
2 days ago
Could you clarify why you need 1 item per sheet instead of just having all the items on the one sheet? I'm not seeing the use case, and so while I might be able to answer, it might not meet the actual need.
– JMichael
Nov 5 at 17:55
Could you clarify why you need 1 item per sheet instead of just having all the items on the one sheet? I'm not seeing the use case, and so while I might be able to answer, it might not meet the actual need.
– JMichael
Nov 5 at 17:55
There's only two work sheets in one workbook, the database: file named AWS_db and the result which is the Water Bill as shown in the picture above. I wanted to create several pages in one worksheet base on the number of rows in the database, the data comes from the same columns A,B,C etc. but different rows 1,2,3 etc. like in 1st page would show data from 1st row, 2nd page 2nd row, 3rd page 3rd row and so on
– MTO
2 days ago
There's only two work sheets in one workbook, the database: file named AWS_db and the result which is the Water Bill as shown in the picture above. I wanted to create several pages in one worksheet base on the number of rows in the database, the data comes from the same columns A,B,C etc. but different rows 1,2,3 etc. like in 1st page would show data from 1st row, 2nd page 2nd row, 3rd page 3rd row and so on
– MTO
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
The closest you can reliably get to doing this via formula would be to have the following formula in column A, and then copy it into columns B.
=OFFSET(AWSdb!B2, $D$1,0)
Column C would use
=OFFSET(AWSdb!D2, $D$1,0)&OFFSET(AWSdb!E2, $D$1,0)
You'd then put in the appropriate values in D1 to move the right # of rows down for the given sheet.
Another, possibly better, option would be a simple macro. Just record yourself copy/pasting the values to a sheet (plus any other actions you'd want to do like renaming the sheets). Drop that logic inside a Do While loop to iterate through the rows.
answered 2 days ago
JMichael
461618
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
The closest you can reliably get to doing this via formula would be to have the following formula in column A, and then copy it into columns B.
=OFFSET(AWSdb!B2, $D$1,0)
Column C would use
=OFFSET(AWSdb!D2, $D$1,0)&OFFSET(AWSdb!E2, $D$1,0)
You'd then put in the appropriate values in D1 to move the right # of rows down for the given sheet.
Another, possibly better, option would be a simple macro. Just record yourself copy/pasting the values to a sheet (plus any other actions you'd want to do like renaming the sheets). Drop that logic inside a Do While loop to iterate through the rows.
answered 2 days ago
JMichael
461618
add a comment |
up vote
0
down vote
The closest you can reliably get to doing this via formula would be to have the following formula in column A, and then copy it into columns B.
=OFFSET(AWSdb!B2, $D$1,0)
Column C would use
=OFFSET(AWSdb!D2, $D$1,0)&OFFSET(AWSdb!E2, $D$1,0)
You'd then put in the appropriate values in D1 to move the right # of rows down for the given sheet.
Another, possibly better, option would be a simple macro. Just record yourself copy/pasting the values to a sheet (plus any other actions you'd want to do like renaming the sheets). Drop that logic inside a Do While loop to iterate through the rows.
answered 2 days ago
JMichael
461618
add a comment |
up vote
0
down vote
up vote
0
down vote
The closest you can reliably get to doing this via formula would be to have the following formula in column A, and then copy it into columns B.
=OFFSET(AWSdb!B2, $D$1,0)
Column C would use
=OFFSET(AWSdb!D2, $D$1,0)&OFFSET(AWSdb!E2, $D$1,0)
You'd then put in the appropriate values in D1 to move the right # of rows down for the given sheet.
Another, possibly better, option would be a simple macro. Just record yourself copy/pasting the values to a sheet (plus any other actions you'd want to do like renaming the sheets). Drop that logic inside a Do While loop to iterate through the rows.
answered 2 days ago
JMichael
461618
The closest you can reliably get to doing this via formula would be to have the following formula in column A, and then copy it into columns B.
=OFFSET(AWSdb!B2, $D$1,0)
Column C would use
=OFFSET(AWSdb!D2, $D$1,0)&OFFSET(AWSdb!E2, $D$1,0)
You'd then put in the appropriate values in D1 to move the right # of rows down for the given sheet.
Another, possibly better, option would be a simple macro. Just record yourself copy/pasting the values to a sheet (plus any other actions you'd want to do like renaming the sheets). Drop that logic inside a Do While loop to iterate through the rows.
answered 2 days ago
JMichael
461618
answered 2 days ago
JMichael
461618
answered 2 days ago
JMichael
461618
answered 2 days ago
JMichael
461618
461618
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147336%2fexcel-copying-formulas-keeping-column-values-and-increment-only-their-row-numb%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Could you clarify why you need 1 item per sheet instead of just having all the items on the one sheet? I'm not seeing the use case, and so while I might be able to answer, it might not meet the actual need.
– JMichael
Nov 5 at 17:55
There's only two work sheets in one workbook, the database: file named AWS_db and the result which is the Water Bill as shown in the picture above. I wanted to create several pages in one worksheet base on the number of rows in the database, the data comes from the same columns A,B,C etc. but different rows 1,2,3 etc. like in 1st page would show data from 1st row, 2nd page 2nd row, 3rd page 3rd row and so on
– MTO
2 days ago