Hi Folks,
I have, for example two sheets and both are used to input data.
One row for each day on sheet 1
One row for each week on sheet 2.
The workbook is set up for relative references.
Is there a way to quickly link the values on sheet 2 from sheet 1?
As currently when trying to copy & paste formulas downwards on sheet 1, it keeps trying to reference every 7th row on sheet 2
eg.
Sheet 1
Copy down produces the following:
Sheet 1
A gap of 7 rows
So the row value in column K increments by 7 each time, whereas I only want it to increment by 1.
when what I want is:
Sheet 1
No gap, the next row
How do I get it to work correctly?
Or is there a better way to approach the whole issue?
The images are just for reference to the row spacing, not the formulas displayed further down the post.
Thanks in advance for any help.
Mike
I have, for example two sheets and both are used to input data.
One row for each day on sheet 1
| 2020-09-14 Records 01.xlsm | |||||
|---|---|---|---|---|---|
| B | C | D | |||
| 6 | 2020-09-14 | Mon | 1962 | ||
| 7 | 2020-09-15 | Tue | 0 | ||
| 8 | 2020-09-16 | Wed | 0 | ||
| 9 | 2020-09-17 | Thu | 0 | ||
| 10 | 2020-09-18 | Fri | 0 | ||
| 11 | 2020-09-19 | Sat | |||
| 12 | 2020-09-20 | Sun | |||
| 13 | 2020-09-21 | Mon | 0 | ||
| 14 | 2020-09-22 | Tue | 0 | ||
| 15 | 2020-09-23 | Wed | 0 | ||
| 16 | 2020-09-24 | Thu | 0 | ||
| 17 | 2020-09-25 | Fri | 0 | ||
| 18 | 2020-09-26 | Sat | |||
| 19 | 2020-09-27 | Sun | |||
Data | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D6 | D6 | ='Mon 1'!Q4+'Mon 2'!K4+'Mon 3'!Q4+'Mon 4'!K4 |
| D7 | D7 | ='Tue 1'!Q4+'Tue 2'!K4+'Tue 3'!Q4+'Tue 4'!K4 |
| D8 | D8 | ='Wed 1'!Q4+'Wed 2'!K4+'Wed 3'!Q4+'Wed 4'!K4 |
| D9 | D9 | ='Thu 1'!Q4+'Thu 2'!K4+'Thu 3'!Q4+'Thu 4'!K4 |
| D10 | D10 | ='Fri 1'!Q4+'Fri 2'!K4+'Fri 3'!Q4+'Fri 4'!K4+'Fri 5'!K4 |
| D13 | D13 | ='Mon 1'!Q5+'Mon 2'!K5+'Mon 3'!Q5+'Mon 4'!K5 |
| D14 | D14 | ='Tue 1'!Q5+'Tue 2'!K5+'Tue 3'!Q5+'Tue 4'!K5 |
| D15 | D15 | ='Wed 1'!Q5+'Wed 2'!K5+'Wed 3'!Q5+'Wed 4'!K5 |
| D16 | D16 | ='Thu 1'!Q5+'Thu 2'!K5+'Thu 3'!Q5+'Thu 4'!K5 |
| D17 | D17 | ='Fri 1'!Q5+'Fri 2'!K5+'Fri 3'!Q5+'Fri 4'!K5+'Fri 5'!K5 |
One row for each week on sheet 2.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E4 | E4 | =(L4*M4)+(N4*O4)+(P4*Q4)+(F4*G4)+(H4*I4)+(J4*K4) |
| E5:E7 | E5 | =(L5*M5)+(N5*O5)+(P5*Q5) |
The workbook is set up for relative references.
Is there a way to quickly link the values on sheet 2 from sheet 1?
As currently when trying to copy & paste formulas downwards on sheet 1, it keeps trying to reference every 7th row on sheet 2
eg.
Sheet 1
Excel Formula:
='Tue 1'!K4+'Tue 2'!K4+'Tue 3'!K4+'Tue 4'!K4Copy down produces the following:
Sheet 1
Excel Formula:
='Tue 1'!K11+'Tue 2'!K11+'Tue 3'!K11+'Tue 4'!K11So the row value in column K increments by 7 each time, whereas I only want it to increment by 1.
when what I want is:
Sheet 1
Excel Formula:
='Tue 1'!K5+'Tue 2'!K5+'Tue 3'!K5+'Tue 4'!K5How do I get it to work correctly?
Or is there a better way to approach the whole issue?
The images are just for reference to the row spacing, not the formulas displayed further down the post.
Thanks in advance for any help.
Mike
