+ Reply to Thread
Results 1 to 3 of 3

How to make this formula relative to a labels position?

  1. #1
    Registered User
    Join Date
    09-28-2007
    Posts
    17

    How to make this formula relative to a labels position?

    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
    Please Login or Register  to view this content.
    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.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517
    You could perhaps use the ROW() and COLUMN() Formulas,
    =INDIRECT(ADDRESS(ROW()+3,COLUMN()+2))
    will give the value from the cell 3 rows down and 2 columns over.

    Alternatively you might choose the OFFSET function, if the current cell is G8 then:
    =OFFSET(G8,1,2)
    will give the value 1 row down and 2 across.

    Hope this helps.

    Mark.

  3. #3
    Registered User
    Join Date
    09-28-2007
    Posts
    17
    Quote Originally Posted by Mark@Work
    You could perhaps use the ROW() and COLUMN() Formulas,
    =INDIRECT(ADDRESS(ROW()+3,COLUMN()+2))
    will give the value from the cell 3 rows down and 2 columns over.

    Alternatively you might choose the OFFSET function, if the current cell is G8 then:
    =OFFSET(G8,1,2)
    will give the value 1 row down and 2 across.

    Hope this helps.

    Mark.
    Thanks Mark!

    The "OFFSET" worked brilliantly.

    In fact, the combination of INDEX, MATCH and OFFSET is one that I willnot soon forget!

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1