I want to reference a cell from another table and fill it with text

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.

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

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

"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

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

"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

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

Mostly not paying attention to details.

In this table:

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:

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:

Your formula is as shown here:

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

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

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