Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Autofill formulas across worksheets, rows and colums

0

Tracking staff productivity I have 50 staff on each worksheet, and 23 worksheets, one for each workday of the month. (each worksheet has identical formatting)

The staff are down the left and their tasks are at the top of the row.

Their are many tasks and fields and I want a monthly summary.

So the task in the first monthly summary cell is  ='Day1'!L11 

then next cell  ='Day1'!L22  then  ='Day1'!L33

across the row

the down a colum will be ='Day1'!M11   then   ='Day1'!O11  then   ='Day1'!Q11

Is there a formula I can copy across the page (row) and then down the colum, like ='Day1'!L11 + 11 , or someting that will work across the 23 worksheets, so I dont have to copy and pasy each one?

Bruce   thank you in anticipation to anyone that can assist

Answer
Discuss

Answers

0

Hi Bruce and welcome to the Forum.

Not sure I understand your question fully - it nearly always helps if you, when creating your question or later editting it, use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. 

I think you can use the Excel INDIRECT function here (combined with R1C1 referencing and ROW / COLUMN functions). Don has a tutorial on this (INDIRECT Function in Excel - Powerful Range References

In the attached file, I've tried to recreate what you said. The formula in A2 (and copied down to other pale yellow cells) in Sheet1 is: 

=INDIRECT("Day1!R"& 11 & "C" & 13 + (ROW()-2)*2,FALSE)

where the first bold bit fixes the row to 11. The second bit uses the COLUMN fuction (which returns 2 for a cell in column B) to give "skipping" by two columns right from 13 (column M) as it is copied across.

Likewise the formula in row 2 (in pale green cells) is (for B2):

=INDIRECT("Day1!R"& 11+ (COLUMN()-2)*11  & "C" &12,FALSE)
 

where this time it "skips" by 11 rows as it is copied right.

Hopefully this makes sense (and you can see which cells from Day1 the functions give) and you can modify it to suit your purposes. If this works for you, please don't forget to mark this Answer as Selected (it helps to guide others too)

Discuss

Discussion

Did that help? 
John_Ru (rep: 3632) Jul 22, '22 at 7:31 am
No?
John_Ru (rep: 3632) Aug 4, '22 at 4:23 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login