Increasing the numbers of some Cells being targeted in a formula, by a fixed amount

[CryoniX]

Increase Cell Range value.JPG

I'm trying to create a spreadsheet that collates hours from different operatives who work on different sites.

Currently I am having to change the cell's being targeted in my formula each time. Which is becoming quite painstaking.

Using the picture I've attached as an example. Would it be possible to increase D15 by 8 each time keep C9 the same, and increase D11 by 8 each time, going downwards.

[Pepe Le Mokko]

Hi and welcome
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[CryoniX]

Hi Pepe,

Thanks for the welcome and the feedback!

I've uploaded a small version of the workbook.

I'm attempting to reconcile the information from the first sheet "TimeSheet" where I am entering each persons hours they work per day, which job they worked on, and any overtime that will affect their pay.
The second sheet "Job Weekly Costing Summary" is where I will grab the data from each operative, based on where they worked i.e. everyone who worked on job 1 during the week, will go into a table, to display the total hours worked on that site, and the cost (the total wages spent).
Forgot to mention, the problem is with this sheet. I am attempting to grab data from each operative, but am currently having to amend each cells formula by 9 i.e. =IF(TimeSheet!E7=JobLists!C9,TimeSheet!E11,0) to =IF(TimeSheet!E15=JobLists!C9,TimeSheet!E19,0)
The third sheet "JobLists" is purely used to store information on the jobs being currently worked.

Any help will be greatly appreciated.

[JeteMc]

"Would it be possible to increase D15 by 8 each time keep C9 the same, and increase D11 by 8 each time, going downwards."
Yes it is possible. If you place the following into cell D10 and drag down to D14 it will direct the formula to TimeSheet!D39, JobLists!C9 and TimeSheet!D43 just as the original.

Please Login or Register  to view this content.

That said changing the layout will likely help more than changing the formula. The time sheet is set up like a dashboard (sheet set up to display final results). If instead the time sheet were set up as a proper record set, such as the one modeled on Sheet1 (took less than 15 min), then a Pivot Table(s) could possibly do the rest of the work (as modeled).
Let us know if you have any questions.

[CryoniX]

Hi JeteMc,

Thanks for replying and giving me the 2 different ways of solving the problem.

1.

Would you be able to explain what the forumla is doing please? So I can possibly understand it a bit better, and maybe use in the future?

I've also noticed it seems to draw a thick outline around the cell after entering the forumla!

2.

I've just discovered what pivot tables are, and that does seem a pretty efficient way of displaying the data. I'm going to look into these more.

Thanks again for your response!

[JeteMc]

When you drag the formula down a small "Auto Fill Options" box should appear next to the fill handle. Select the box then choose "Fill without formatting". That should stop the thick outline.
I suggest two things in learning how the formula works.
1. After dragging the formula to D14, select that cell and use the Evaluate Formula feature (Formulas tab) to see step by step how it evaluates.
2. Look at the information about INDIRECT given here: https://support.office.com/en-us/art...rs=en-US&ad=US
If you have questions after doing both, I'll answer as best I can.
Note that INDIRECT is volatile meaning it will recalculate any time a change is made to any cell. Since it appears that it will be used in D14:J39 (210 cells) it may cause the program to run more slowly than normal.
Let us know if you have any questions.

[CryoniX]

I've tried to follow the example in the link you sent, but I'm not getting the results it says I should. Would you mind having a look?

I'm not sure I quite understand the function of INDIRECT completely. I did give it a go when you first posted the solution.

[JeteMc]

Attached is the file with INDIRECT operating as expected. To get the expected result in A12 the cell B4 must be named 'George'. The formula in cell A13 should be: =INDIRECT("B"&A5)
Here is a link to a tutorial that may prove more helpful.
Let us know if you have any questions.