Previous 1 2 Next 18 Replies Latest reply: Feb 17, 2014 12:22 AM by Barry
blkrocket Level 1 Level 1 (0 points)

I I have several tables that update each other with data from the previous one. These tables are identical and follow each other by date. In each table is a cell (B1) where a persons name goes. the cell (B2) references another cell from a previous dated table. Some days, consecutively, no name is entered in to cell B1. I want cell B2 to always refer back to the most recent table with a name is cell B1. How can this be done?

 

I have written this formula (applied to cell B2 of this example) already: IF(Day 1::B1="","",IF(Day 1::B1≥"",Day 1::B1))  but it only references the previous days table and stops there. Some days nothing is inputed into cell B1 causing causing the following days table cell B2 to show "0".

 

 

Monday, April 1
Driver
Driver for TodayDavid
Previous Driver (Last person to drive vehicle)

 

Tuesday, April 2Driver
Driver for Today

Previous Driver (Last person to drive vehicle)

David
Wednesday, April 3Driver
Driver for TodayMike
Previous Driver (Last person to drive vehicle)David
Thursday, April 4Driver
Driver for Today
Previous Driver (Last person to drive vehicle)Mike
Friday, April 5Driver
Driver for Today
Previous Driver (Last person to drive vehicle)Mike
Saturday, April 6Driver
Driver for TodayCarl
Previous Driver (Last person to drive vehicle)Mike
Sunday, April 7Driver
Driver for TodayDavid
Previous Driver (Last person to drive vehicle)Carl

OS X Mountain Lion (10.8.3)
  • Barry Level 7 Level 7 (29,215 points)

    HI br,

     

    This might be simplified by making an auxiliary table hat gathers the sequential list of drivers and creates a serial number for each day there is a driver;

     

    The 'previous driver' could then be retrieved using a LOOKUP formula. Here's an example.

    Screen Shot 2014-01-30 at 12.22.23 AM.png

    Formulas:

     

    Table 1 contains one formula:

     

    B3: =IFERROR(IF(LEN(B2)<1,"",LOOKUP(MAX(OFFSET(Aux :: $C$1,1,0,MATCH(A1,Aux :: A,0)-2)),Aux :: $C,Aux :: $B)),"N/A")

     

    MATCH returns the position of the value (date) in A1 in the list in column A of Aux. Two is subtracted from this value to determine the number of rows from which OFFSET collects the set of values in column C of Aux (limiting the search to rows with dates earlier than the table on which the formula resides). MAX extracts the largest value in this range. LOOKUP looks up this value in column C of Aux, and returns the value (name) fom the same row of column B.

     

    The same formula is pasted in to B3 of each of the other small tables. (OR a single table may be created, formulas and fixed labels entered, and the table duplicated as many times as needed, then each table renamed to follow the pattern shown (Text, plus a single space, plus a serial number).

     

    Aux contains two formulas:

     

    B2: =IF(LEN(INDIRECT("Table "&ROW()-1&"::B2"))<1,"",INDIRECT("Table "&ROW()-1&"::B2"))

     

    Fill down to the end of column B. If there are more rows in this table than there are small tables, extra rows will contain error triangles. To avoid these, you could enclose the formula with an IFERROR statement, as above.

     

    A2: Column A of this table was filled manually, as it was assumed there would be a small table for every day. If there will be missing dates, use the same formula here as in column B, changing both instances of "::B2" to "::A1"

     

    C2: =IF(LEN(B2)<1,"",MAX($C$1:C1)+1)

     

    Fill down to the end of column C.

     

    This generates the incremented serial number in each row containing a name in column B, used by LOOKUP to fetch names for B3 on the small tables.

     

    Regards,

    Barry

  • blkrocket Level 1 Level 1 (0 points)

    Barry,

    I have tried to apply the formula's to my tables but have not got them to work like you suggested they would. I assumed in the fomula that is place into the Aux table B2 that I shouldreplace "table" with the name of the table I am using. Is that correct? Is there anything else that I should change?

  • Barry Level 7 Level 7 (29,215 points)

    Hi br,

     

    The tables must be named as shown ("some text " followed by a serial number, with the serial numbe on the first table being 1). You can use any text you want for "some text " provided it is exactly the same for every one of the tables, and is exactly the same as what is enclosed in the quotation marks in the formula.

     

    In B2, INDIRECT("Table "&ROW()-1&"::B2") generates the cell reference to 'Table 1::B2'

    In B3, INDIRECT("Table "&ROW()-1&"::B2") generates the cell reference to 'Table 2::B2'

    In B4, INDIRECT("Table "&ROW()-1&"::B2") generates the cell reference to 'Table 3::B2'

    Etc.

     

    If those cell references do not match the table name and cell location of the cell holding the required information, that information will not be retrieved.

     

    Regrds,

    Barry

  • blkrocket Level 1 Level 1 (0 points)

    Hi Again Barry,

    I am having trouble getting the formulas you suggested to work. I am not sure where the problem is coming from. Could you look at the following spreadsheet and tell me where I am going wrong? The following pic shows Day 1 and AUX tables. In Day 1 cell B1 a Drivers name is entered. As you can see by the formula that you gave the B2 is for "Product" not the drivers name "David".

     

    Day 1.jpgAUX B2.jpg

    I made a adjustment and changed cell B2 to B1 here:Day 1 v2.jpg

    Nothing changed in the AUX table. I get a error in cell B4 stating, "The Formula contains a invalid refference". I get a error in cell C4 stating, cell "Driver Saturday, August 3, 2013"  contains a error". I copied down the formula in Column B2 - B6, changing the the tables name as directed. As you can see the name that appears in cell B2 and B3 is "Product".

    AUV v2.jpg

    Hopefully this will help in figuring out this formula. Your assistance is greatly appreciated.

  • Barry Level 7 Level 7 (29,215 points)

    "I am having trouble getting the formulas you suggested to work. I am not sure where the problem is coming from. Could you look at the following spreadsheet and tell me where I am going wrong? The following pic shows Day 1 and AUX tables. In Day 1 cell B1 a Drivers name is entered. As you can see by the formula that you gave the B2 is for "Product" not the drivers name "David"."

     

    Hi br,

     

    The formulas provided above apply directly to the set of tables shown with them. In each of these tables, the target cell, containing the name of the driver for that day is cell B2 on the table for that day.

     

    For Day 1, the driver is Huey, found in cell B2 of Table 1, and the cell reference to retrieve the information from that cell is =Table 1::B2

     

    The formula in B2 of Aux uses INDIRECT to build that cell reference from the fixed text string "Table ", the calculated value ROW()-1, and the text string "::B2"

     

    ROW() returns the number of the row in which the function sits. In B2, that's row 2, and ROW() returns 2. -1 subtracts 1 from that value, leaving 1. In B2, ROW()-1 returns 1.

     

    The ampersand is the concatenation operator. It joins the value before it to the value following it. The result is a text string. in the INDIRECT part of the formula, there are two & operators, one after "Table ", the other after ROW()-1.

     

    "Table " & ROW()-1 & "::B2" with the result of ROW()-1 as the value between the two text strings.

     

    "Table " & 1 & "::B2"   =   "Table 1::B2"

     

    INDIRECT( ) takes that text and converts it to the cell address that it represents.

     

    B2: =IF(LEN(INDIRECT("Table "&ROW()-1&"::B2"))<1,"",INDIRECT("Table "&ROW()-1&"::B2"))

     

    The first instance on the string in the formula is a test to see if there is anything in the target cell. If not, the formula returns a null string. If there is at least 1 character there, the second instance of INDIRECT is called on to return the name to B2 of the table Aux.

     

    Compare your formula below:

     

    B4: =IF(LEN(INDIRECT("Day 3"&ROW()-1&"::B2"))<1,"",INDIRECT("Day 3"&ROW()-1&"::B2"))

     

    As shown, this is the formula in B4 of your Aux table. In Row 4, ROW() returns 4, and ROW()-1 returns 3.

     

    So the three elements of the cell address constructed in INDIRECT are "Day 3", 3, and "::B2"

     

    The cell being addressed is "Day 33::B2", cell B2 on the table named Day 33.

     

    The error results from there being no table with that name.

     

    The fix (to the table part of the reference) is to change the first text string to "Day " (including the space after the word Day).

     

    The fix to replace Product with the driver name is to change "::B2" to ::B1"

     

    To fit the names of your day tables and the location of the driver's name on each, your formula in B2 should read:

     

    B2: =IF(LEN(INDIRECT("Day "&ROW()-1&"::B1"))<1,"",INDIRECT("Day "&ROW()-1&"::B1"))

     

    Regards,

    Barry

  • blkrocket Level 1 Level 1 (0 points)

    Thanks Barry for the help. Your solution worked. Can you tell me how to get  "N/A" to appear when no name is entered for Driver? It shows up in table "Day 1" but does not appear in any of the other folowing tables. Am I missing anything? Here is what I see.

    Day 1.jpegDay 2.jpegDay 3.jpegAux.jpg

  • Barry Level 7 Level 7 (29,215 points)

    Hi again,

     

    i got to thinking over dinner that I remembered you saying you'd changed B2 to B1, but that I didn't recall that in the formula I was dealing with above. Correct recollection, as it turns out—that change was in a different formula, the one that is to retrieve the previous driver from AUX.

     

    As before, the formulas in my initial post applied to the tables shown in that post. Here's the one that brings the Previous Driver name into Table 1, 2, etc.

     

    B3: =IFERROR(IF(LEN(B2)<1,"",LOOKUP(MAX(OFFSET(Aux :: $C$1,1,0,MATCH(A1,Aux :: A,0)-2)),Aux :: $C,Aux :: $B)),"N/A")

     

    LEN(B2) checks for an entry in cell B2 of 'this table' (the table the formula is on).

     

    B2 on this table contains the word Product. Since the purpose of LEN(B2) is to check whether a driver has been named for 'today', change this reference to LEN(B1).

     

    MATCH(A1,Aux :: A,0)-2)),Aux :: $C,Aux :: $B)

     

    In your original tables and in my examples, cell A1 of the daily tables contained the date of the record kept on that table. MATCH searches for this date in column A of the table Aux. A1 on your current table contains the word "Driver", which will not match any of the dates in column A of Aux. Match returns an error, trapped by IFERROR, which returns "N/A" to the cell containing the formula.

     

    Change the A1 reference in MATCH to the cell containing the date (K1 if J1, K1 and L1 have not been merged, J1 if they have).

     

    With those changes, the formula in C45 of each day's table should look like this:

     

    C45: =IFERROR(IF(LEN(B1)<1,"",LOOKUP(MAX(OFFSET(Aux :: $C$1,1,0,MATCH(J1,Aux :: A,0)-2)),Aux :: $C,Aux :: $B)),"N/A")

     

    (J1 assumes J1, K1 and L1 have been merged.)

     

    Regards,

    Barry

  • blkrocket Level 1 Level 1 (0 points)

    Hi Barry,

    That helped with the error I was getting cell C45. C45 will still not show "N/A" in any other table other than Day 1 (See Day 2). I assume it shows in Day one because there is no "Driver" present for a non preceeding day (No other table exsists before Day 1).

  • Barry Level 7 Level 7 (29,215 points)

    "I assume it shows in Day one because there is no "Driver" present for a non preceeding day (No other table exsists before Day 1)."

     

    Pretty much. The mechanical reason is that the formula asks OFFSET to return values from a range of zero cells. OFFSET says 'Say what?!?!  ERROR!", which catches the attention of IFERROR, which puts N/A in C45.

     

    After Day 1, I chose to leave the cell 'blank' to match the empty cell above it when there was no driver assigned for that day.

     

    If you want to use N/A rather than show an 'empty' cell, put N/A between the double quotes in the formula for C45.

     

    Regards,

    Barry

     

    PS: I see you made the adjustment discussed in my last post above. Well done!

     

    B

  • blkrocket Level 1 Level 1 (0 points)

    Hello again Barry,

     

    I made some adjustments to the tables and am at a loss as to why they are not working now. I integrated the Formulas in AUX table into my Checkpoint table. Instead of the cells filing down they now file across the table as shown.

    Checkpoint.jpeg

    Full view.jpeg

    Checkpoint Driver.jpegCheckpoint count.jpeg

     

    Can you please tell me what I did wrong?

  • Barry Level 7 Level 7 (29,215 points)

    "Can you please tell me what I did wrong?"

     

    Mostly not paying attention to details.

     

    In this table:

    Checkpoint Driver.jpeg

    You have merged the first three cells in rows 1, 2, 3 and 4 (and possibly others not visible in the screen shot).

     

    Your formula in D3 is:

    =IF(LEN(INDIRECT("Day "&COLUMN()-1&"::B1))<1,"",INDIRECT("Day "&COLUMN()-1&"::B1))

     

    The formula shown is in cell D3. Placed in this cell, ROW() returns 3 and COLUMN() returns 4.

    4 - 1 = 3, so this formula is referencing cell B1 on the table named Day 3. If no driver has been entered in B1 of Day 3, then the correct result for this formula is shown.

     

    In this table:

    Checkpoint count.jpeg

    The formula is returning a null string, which is correct. With a name in D3 it should return 1. If not, the problem is possibly due to the merged cells in columns A-C.

     

    In this table:

    Full view.jpeg

    Your formula is as shown here:

    Screen Shot 2014-02-15 at 1.14.16 AM.png

    The formula is returning N/A, a result that can be caused by either of two onditions:

     

    Cell B1 has no contents.

    One or more of the functions is returning an error message.

     

    Cell B1 contains "Tim", so that isn't the cause.

     

    There are several possibilities for errors here:

    • LOOKUP is searching for a value in row 4, and will accept the largest value less than or equal to that value. But Row 4 does not contain any numerical values, so the maximum value is non-existent. That may cause an error.
    • LOOKUP is searching in a range containing three merged cells. That may also cause an error
    • MATCH is used to construct the list of values from which MAX is chosen. Here in the syntax for MATCH:
      MATCH(search-for, search-where, matching-method)
      Search-for in your formula is the value in J1, a Date and Time value: Aug 31, 2013 00:00:00
      Search-where is the cell range A2:AH2 on the table Checkpoint. These values may be formatted differently, but they must be Date and Time Values, with the time part set to zero (this setting is automatic when only the date part is entered). If those dates were entered since January 1, 2014, then unless the full date, including the year, was entered, they will be 2014 dates, and will not be found in a search for the date in J1.
      Matching-method in your formula is set to "Find value"—an exact match is the only match that will be accepted.
    • MATCH returns a number representing the position of the found item in the list. I don't know how MATCH will count the three merged cells at the beginning of the list. If that isn't determined, there's a good chance the count will be off and the formula will return the wrong name.
    • -2 is an adjustment, Increasing the number shortens the list of values from which MAX is determined, and moves the name selection to the left.

     

    Trouble shooting:

     

    Step 1 is to remove the error trap so that errors will produce an error message which can be read by clicking the error triangle that appears in the cell containing the formula. The formula to be tested for errors starts and ends as shown below, and includes everything between the parts shown:

     

    =IF( ... Checkpoint::A3::AH3))

     

    If you get an error and can't figure out what caused it, test the parts, starting with MAX, then working your way out to the full formula one step at a time. Beginning and end of the logical units are listed below. Remember to include an = sign at the beginning of the formula, and to have each step working before going on to the next.

     

    MATCH( ... find value)

     

    MAX( ... columns))

     

    LOOKUP( ... Checkpoint::A3AH3))

     

    IF( ... Checkpoint::A3::AH3))

     

    Full formula.

     

    Happy hunting!

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,215 points)

    Added:

     

    OFFSET as written, is returning a number of values (determined by MATCH) from the same COLUMN, which was correct for the original orientation AUX table, where the driver count, names and dates were listed in columns.

     

    The syntax for OFFSET is:

     

    OFFSET(base, row-offset, column-offset, rows, columns)

     

    For the table orientation I used, MATCH( ... ) is the rows argument for OFFSET.

     

    With the table rotated to list the driver count in a row, MATCH( ... ) must be the columns argument. The rows argument may be omitted, but the commas that mark its place and the columns place must both be included. In Numbers 3, which you are using the word "rows" will display in this position in the formula box.

     

    As you may have noticed, the editing needed to change the orientation of this table is far from trivial. I do hope your reason for making the change is equally non-trivial.

     

    Regards,

    Barry

  • blkrocket Level 1 Level 1 (0 points)

    Hello Barry,

     

    I changed the orientation to fit the format of my spreadsheet. I am not a Numbers expert and am having difficulty figuring out how to make this work. I use this spreadsheet to manage my business and Im seeking your help because you seem very experienced but since I am more of a novice some of your instructions are flying way over my head, Think Elementary School vs. Graduate School. I dont understand how to convert what you gave as a example table in vertical (Column) table to work in a horizontal (Row) table. I just seem to be confusing myself more by trying variations of the formula's without really understanding how they all work together. I noticed "Row" and "Column" and switched them but got errors.

  • Barry Level 7 Level 7 (29,215 points)

    Hi br,

     

    "but got errors"

     

    Click the red triangle and read the error message. It tells why the formula is not working, which is generally useful information.

     

    Making the layout of your tables clear in your initial post or soon after that might have saved a huge amount of time for both of us, as the original formulas would have been written to fit that layout.

     

    In my two posts above, I've indicated the parts of the long formula to check individually, and the order in which to check them. Below is some descriptive detail on the steps. You will still need what's above, as I've tried to avoid repeating what was said there. Read carefully. Proceed slowly.

     

    Do all of the tests in cell C45 on the Day 31 table. For the tests, you will be adding or deleting the name in B1 to test the IF statement. I have also suggested changing the date in J1. CHECK FOR ERRORS on the first change of date in testing any section of the formula. If an error occurs in a cell other than the one containing the tested formula (including cells on the AUX table) re-enter the correct date for Day 31, and leave it on that date.

     

    To test MATCH, you will need a date in J1 of the table containing the formula, and a series of dates including the one in J1, in row 2 of Aux. These dates must be entered as dates, including the year, but may be displayed using whichever Date and Time format you prefer.

     

    The result of MATCH will be a number indicating the position of that date in the range A2-AH2. If you change the date in J1 to one day later, the result should increase by 1

     

    When MATCH is working correctly, add the MAX and OFFSET parts. To test this part, you will need the driver count numbers in Row 4 of Aux. OFFSET here returns a number of values, and can't be tested without adding th MAX part to choose the value to be displayed.

     

    OFFSETbase, row-offset, column-offset, rows, columns)

    base, row-offset and column-offset appear to be correct in your example. rows may be left empty. columns should contain the tested and working version of MATCH.

     

    The result should be a number, but will probably not be the correct one. The number should change as the date in J1 (the tested date) is changed The change will be in the same up-down order as the date change, but will not change in lock step with the date change if there are days without a driver count number. When you get this result and date changes change the result as described, add the correction factor (-2) to the end of this part of the formula.

     

    The result should now be the largest number in row 4 of AUX that is to the left of the column containing the same date as J1. If not, then adjust the correction factor (-2) to make it so. Increasing the number (eg. -3) will move the result to come from a column further left. Test the result for dates for which the column to the left contains a count number and for dates where the previous driver count number cell is 'empty' for one or two columns to the left of the tested date.

     

    When working (result number is always the largest number in row 4 and to the left of the column containing the tested date), enclose this part in the IF part of the formula.

     

    To test IF, you will need the seriesof dates in row 2, driver names on row 3 for each date in row 2 on which a driver was assigned, and a count number in row 4 in each cell below a driver name,

     

    The result should be the name of the driver in the closest filled cell of row 3 to the left of the column for the tested date,

     

    When this version is working correctly, add the IFERROR part. The formula can then be copied and pasted into C45 in the other day tables. Check the results in each as you proceed.

     

    Regards,

    Barry

Previous 1 2 Next