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 tables.
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,
“COUNTA((E320:INDEX(E320:$E$984,MATCH(TRUE,E320:$E$984="",0)-1)”
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.
“INDEX(A:A,MATCH("Actual Repairs",A:A,0)+1))”
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 formula is.
Anyone have insight here?
Thanks for your help.
Bookmarks