...I have changed the post title again as it was not really clear and tried to rephrase my post a little...I do apologise, I am not sure how to phrase my question properly to be clear.
My spreadsheet has multiple sheets with lots of cross reference formulas for calculating our staff bonus's.
Sheet 1 has a complex calendar showing who worked when and who was off sick/holiday - sheet 2 has the daily takings - sheet 3 the payroll, sheet 4 something else - this question only concerns sheets 1 & 2
This month, Sheet 1 F63 reads from cell B207 on Sheet 2 so contains the formula =sheet2!,B207
The problem is that next month F63 will be =sheet2!,B238 and this will change every month
What I want to do when I start the beginning of each month, is to be able to manually enter the new formula into F63 and have J63 read from F63 adding 1 row to the result (and then copy this formula into various other cells across the worksheet updating plus 1,2,3 rows accordingley)
ie can I put a formula into cell J63 that says:
J63= CONTENTS OF F63 (which is this month=Sheet!2,B207) + 1 ROW thus returning the result of whatever is Sheet!2,B208
Then into N63 = contents F63 + 2 rows (ie whatever is Sheet 2 B209)
Then into R63 = contents F63 + 3 rows (ie whatever is Sheet 2 B210)
(Then V63, Z63, AD63 all the way across to DV63)
The reason I want to do this is because I copy this whole spreasheet every month and rename it with the current month.
Currently i have columns on rows 63 through to 69 updating from info on sheet 2 that changes every month, it would be great to just have to update F63 F64 F65 F66 F67 F68 & F69 with = Sheet2! B(NEW ROW) or C(NEW ROW) or D(NEW ROW) (or whatever it happens to be) and have J63/64/65/66/67/68/69 & (N R V Z etc) all update themselves too and save me loads of time!
Please let me know if this does not make sense
Many Thanks in advance
Tulip X
Bookmarks