Links between sheets.

mikecee

New Member
Joined
Sep 15, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,
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
BCD
62020-09-14Mon1962
72020-09-15Tue0
82020-09-16Wed0
92020-09-17Thu0
102020-09-18Fri0
112020-09-19Sat
122020-09-20Sun
132020-09-21Mon0
142020-09-22Tue0
152020-09-23Wed0
162020-09-24Thu0
172020-09-25Fri0
182020-09-26Sat
192020-09-27Sun
Data
Cell Formulas
RangeFormula
D6D6='Mon 1'!Q4+'Mon 2'!K4+'Mon 3'!Q4+'Mon 4'!K4
D7D7='Tue 1'!Q4+'Tue 2'!K4+'Tue 3'!Q4+'Tue 4'!K4
D8D8='Wed 1'!Q4+'Wed 2'!K4+'Wed 3'!Q4+'Wed 4'!K4
D9D9='Thu 1'!Q4+'Thu 2'!K4+'Thu 3'!Q4+'Thu 4'!K4
D10D10='Fri 1'!Q4+'Fri 2'!K4+'Fri 3'!Q4+'Fri 4'!K4+'Fri 5'!K4
D13D13='Mon 1'!Q5+'Mon 2'!K5+'Mon 3'!Q5+'Mon 4'!K5
D14D14='Tue 1'!Q5+'Tue 2'!K5+'Tue 3'!Q5+'Tue 4'!K5
D15D15='Wed 1'!Q5+'Wed 2'!K5+'Wed 3'!Q5+'Wed 4'!K5
D16D16='Thu 1'!Q5+'Thu 2'!K5+'Thu 3'!Q5+'Thu 4'!K5
D17D17='Fri 1'!Q5+'Fri 2'!K5+'Fri 3'!Q5+'Fri 4'!K5+'Fri 5'!K5


One row for each week on sheet 2.
2020-09-14 Records 01.xlsm
BCDE
42020-09-14Mon1320
52020-09-21Mon0
62020-09-28Mon0
72020-10-05Mon0
Mon 1
Cell Formulas
RangeFormula
E4E4=(L4*M4)+(N4*O4)+(P4*Q4)+(F4*G4)+(H4*I4)+(J4*K4)
E5:E7E5=(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'!K4

Copy down produces the following:

Sheet 1
Excel Formula:
='Tue 1'!K11+'Tue 2'!K11+'Tue 3'!K11+'Tue 4'!K11
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
Excel Formula:
='Tue 1'!K5+'Tue 2'!K5+'Tue 3'!K5+'Tue 4'!K5
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,119,255
Messages
5,576,989
Members
412,757
Latest member
Thalalala
Top