Increment within a formula

I have a formula that I'd like to have increment by one, but I can't figure out how to do it.

Here's the formula:
=IF(INDIRECT("'Supervisor'!B"&ROW()-4)="Y",E12-D12,IF(INDIRECT("'Supervisor'!B"&ROW()-4)="N","0",IF(INDIRECT('"Supervisor'!B&ROW()-4)="","0")))

I'd like to have the -4 in the equation increment to -5 when I paste it to the next row without having to fix each one of them. Any ideas?

If the formula were copied down by rows, and the -4 went to -5, -6, ... as copied, the result would be that it always referred to the same cell.

BTW, doesn't this give the same result you have now?

=IF( INDIRECT("'Supervisor'!B" & ROW() - 4) = "Y", E12 - D12, 0)

Thanks for shortening the expression. Yes, the formula does refer back to the same cell for each row in that section of the spreadsheet, so it needs to stay the same for about 9 rows on the second spreadsheet. When I put the following in,
=IF(INDIRECT("'Supervisor'!B"&ROW($A4))="Y",E9-D9,"0")

it appears to work for all rows except when I hit the row that actually matches the row I'm on. For example, if I'm on row 8 in the second spreadsheet, and it needs to look in row 8 on the supervisor spreadsheet, it won't work with a zero. Above row 8, it will need to increment the other direction, so I'm not sure if I need to put a negative sign in the expression when it goes to rows below row 8. Does that make sense?

Okay, I was wrong, the equation didn't exactly do what I wanted it to do. As I tried the formula on another cell, it appears that the $a4 refers back to cell a4 in the original spreadsheet, but then will increment back to $a3 (or the cell above it) on the row above. I need to keep looking at the same cell, and determine if there is a Y there for a series of about 9 rows. (It would also be fabulous if I could get it to be able to copy those nine rows and then increment that $a4 expression by two to get to the next set to data. That may be too much to ask in one post!!!)

You might post a workbook and explain in context.

I would agree that we would prefer to use a database (like Access) for the data, but will not be able to. We want to be able to control access to each spreadsheet, such that only the supervisor can enter data on the first page and then the analyst enters their information on the second page of the spreadsheet. The spreadsheet could then calculate the time difference between each of the steps (e.g., from the date received on the first page to the date the analyst starts testing on the second page, etc.). We can then track cycle times between each step by subtracting dates between each item and see how long it really takes to test a sample. Does that make it any clearer?

I would agree that we would prefer to use a database (like Access) for the data,

I wasn't suggesting that; just that you organize the data you have like records in a database.

You have Batch listed, then a bunch of blank cells, then another Batch. Visually, it's clear that the blank cells are all in the last batch above; formula-wise, it's misery. That's what I'm suggesting you correct.

And I'll be happy to help you with your formula if you will explain what it's supposed to do. All I know now is that it doesn't work.

Okay, I'll admit to not being very good with Excel, but you completely lost me now! I'm not sure how you divide up a spreadsheet, etc. I basically need the number of days between the day listed on spreadsheet 1 and the 3 dates on the last page, or combination of the dates on the last page to determine cycle times. Any way to clarify it?

We originally had the table set up as you stated, however, if I want to copy and add another column, I am forced to update all the $x$ values in the new row, which is what we were trying to avoid in the first place.

Sorry I've made so many changes (see Attached file).

Requests:
1. "I basically need the number of days between the day listed on spreadsheet 1 and the 3 dates on the last page, or combination of the dates on the last page to determine cycle times."
--> See attached formulas on Sheet 2 (red=above target)

2. "if I want to copy and add another column, I am forced to update all the $x$ values in the new row, which is what we were trying to avoid in the first place. "
--> With the attached formulas you should be able to add/delete any number of rows/columns.

Hope this helps at least a bit
Ola


Changes made
* reduced the number of Unique (complex) formulas to 6 ... compare to quite many formulas in the previous file.
* Applied only 1 Unique conditional formatting rule, in Sheet 1
* Applied only 2 Unique conditional formatting rules, to Sheet 2
* Values that exceed targets are automatically colored in red.

To add?
* I removed all the black lines from sheet 2, since I wasn't sure what purpose they served and I couldn't quite understand if the rule were that they should be applied to all rows without a date and when stability was Y or N or ""?

I kind of see what you're doing now. The only part I would still have copy issues with is the Vlookup part that refers to the array. It doesn't appear to pull in the values for the receipt date with the current formula, without changing it. Am I missing something?

There are only 6 unique (master) formulas and they are all located on sheet 2 and row 6. When something needs to be changed you only need to change the formula in row 6 and then copy the formula all the way down.
For instance when the Vlookup range needs to be extended (column I and J), change the range in cell I6 and J6 and Copy and Paste special, Formulas - all the way down.
All 6 formula are designed to be (abled to be) copied all the way down.

If you get tired of changing the length of range, you could instead Insert, Name, Define the vlookup range; for instance call it Myrange. Then use the name Myrange instead of the range in the 2 formulas. Then when the range has to be extended you only update the range defined by Myrange and not the formula. Note that the defined range can be longer then the actual list.

Next step in 'simplifing' is to use a more advanced approach of automating thing. The named range can actually be set to automatically detect length of the range. This can be done by a fairly advanced formula. But this is optional. Some would consider that this makes things much more complicated.

Your question:
"the array. It doesn't appear to pull in the values for the receipt date with the current formula, without changing it. Am I missing something?"
I might be missing some here but I wrote two new dates in Italic in sheet 1. They get transfered to sheet 2. And the number of days are then calculated - if the other dates in sheet 2 are there.
So for that perspective I think the formula works.

But maybe you meant 'Date of manufacture'? In that case, the simplest would be to use the same formula as for 'Date Received in Laboratory' to transfere 'Date of manufacture' to sheet2.

I hope it helped a bit more
Ola