Continuing on with what I have already learned here, I would like to finish what I have started.
In an earlier
thread I learned the “INDEX/MATCH” technique to make a formula “look” for a position in a column of
data. This technique is really slick and automates my formulae, partially.
What I am trying to accomplish is to
build a formula that calculates its result based on its position relative to two cells, regardless of where the
formula’s position is….
There is an example sheet attached.
So-far the formula looks up the
column A and finds a value and compares that value to the count in another range.
In the example sheet the
formulas I am attempting to fix are the “checksums”
The first table in the sheet is an extraction from
the main database that I use to generate the second table.
The second table is a table I manually alter according to observed patterns and methods.
The third, fourth and fifth tables are the unique records extracted from the second table in preparation for insertion back
into the main database after correction.
The checksum formulas simply keep me aware of problems across the
The problem is, all of these tables vary in size from day to day….
For example, The check
sum found in cell A325 is as follows
This formula counts the records found in the range E320:E327 via the,
Section of the code, and then looks up column A and find a match to the “Actual Repairs” reference and then
subtracts the value underneath that reference from the count made previously in the formula.
Thanks to NBVC I now understand both of these
techniques. After employing them however I notice they still do not do what I want them to do.
The first half of
the formula is where I need help. I need the formula to look 5 cells to the right and 1 cell down (cell E320) relative to the
“repairs” reference in A319.
This needs to be a static reference, in that it needs to look 5 cells over and 1 down, every time regardless of where the
Anyone have insight here?
Thanks for your help.