I need help creating a formula that carries over data in a cell from one table to another

BK,

You can test B2 for content by using the LEN function to see how many characters are in the cell or by using the ISBLANK function. They aren't equivalent, but in some cases, either will do.

Use those functions in an IF-statement where you conditionally pull data from the other table.

Jerry

Hi br,

"Table

Day 1 C38, shows the beginning milage (only on day 1 is C38 manually entered). C39 (manual entry) shows the returned milage for Day 1.

Day 2 C38 shows the Beginning milage carried over from Day 1 C39. Day 2 C39 shows the return milage for Day 2.

Day 3 shows no activity so Nothing is entered into C39.

Day 4 C38 shows the milage reading from Day 2 C39.

I need a formula that looksup the previous days data and carries it over to the following day but only when the table has a Name in B2."

As I read this:

Day 3, which has no name in B2, should have no data (in C38) carried over from Day 2 (in C39?).

Day 4, which has a name in B2, should have data (in C38) carried over from Day 3 (in C39).

Problem: How does the data get into Day 3::C39 ?

Suggestion: C38 should pull in the end of day mileage from the previous table without regard to their being a driver named for that day. For Day 3, C38 would contain 21722, retrieved from Day 2::C39, and C39 would be empty (as shown in the example above).

For Day 4, there is no value to be retrieved from Day 3::C39, but Day 3::C38 does contain the current (beginning of Day 4) odometer reading.

Solution: On each table, starting with Day 2, the formula in C38 checks for content in the previous table's C39. IF there is a value in that cell, it is copied to the current cell. If C39 is empty, the value in C38 is copied.

Formula:

Day 2::C38: =IF(LEN(Day 1::C39)>0,Day 1::C39,Day 1::C38)

In succeeding tables, increment "Day 1" to name the table previous to the one containing the formula.

Regards,

Barry

"I am wanting to place a formula that is similar to that one in C38 that will show the milage from a previous Day's table. If possible I want the milage to show in C38 only when a Drivers name is entered in B1, If no Drivers name is in B1 then Nothing should appear in C38."

It's possible, but, as asked above, where in that case will the next day's table pick up the mileage for it's cell C38?

"I have been using a similar formula to the one you advised but it requires that I edit each table with a previous Day. I would like to avoid that now. I was hoping that there was a lookup formula that could do the job."

The 'previous driver(s) were all listed on the same table, as I recall. Here, each previous day's mileage is listed on a different table. If these tables are named Xxx 1, Xxx 2, Xxx 3, etc., with constant text and an incrementing number. it is possible to enter the number part into the same cell on each table, then use INDIRECT with a combination of text and a reference to that cell (and a bit of simple math) to construct the cell reference to the correct cell(s) from which to extract the odometer reading.

Using the manual version of the formula, you need seven clicks, a command-V, one command-click and one return, tab or click to edit each formula. That's a one time process, done only when setting up the tables, and I suspect it takes less effort than composing the formula. Here's the process, starting after you've entered the formula in cell C38 of Day 2:

A; copy the formula:

1. In the entry box or the formula editor, select (highlight) all of the formula.
2. Copy.

B: Paste and edit:

1. Click twice on cell C38 of the next table to enter the cell.
2. Paste.
3. Click on the first lozenge (Day 1::C39) to select it.
4. Click on cell C39 of the previous table (ie, if you are editing on Day 3, the previous table is Day 2)
5. Click on the second lozenge (Day 1::C39) to select it.
6. Command-click on C39 of the previous table.
7. Click on the last lozenge (Day 1::C38)
8. Click on C38 of the previous table.
9. Confrm the changes (Press return, Press tab, or click the green checkmark.
10. Repeat with the next table.

Regards,

Barry

BR,

This is a difficult problem for us to help you with because of the way you have setup your document. Also, the little snapshots of tables are easy to read, but the context is less clear because it's like looking through a peephole.

I have two general suggestions. First, organize your document into the standard database format of entered data in one large table, with multiple reports based on that data and calculations on that data. Second, organize the data table into the standard database format of field names across the top, with each row a new record. If you continue with the arrangement in your last screen shot, you will have a database that is difficult to view and difficult to search.

Believe me, it's as frustrating for us not to be able to help you as for you not to have your simple question answered.

Jerry

Hi br,

The solution in my original post collects the most recent odometer reading from the previous day's table, and always from one of two specific cells on that table. In that situation, there's no need for MATCH or any of the lookup functions; the cells can be directly addressed.

"I want it to post "N/A" on dates posting no activity. I would like the value to show in C38 if a Value is entered into B1 of the same table."

This is the sticking point of the question. Consider these scenarios:

CASE 1

Day 2:

B1 contains "Joe"

C38 contains 100

C39 contains 102

Day 3:

B1 contains "Joe"

C38 looks to Day 2::C39, and retrieves 102

C39 contains 108 (entered manually)

Day 4:

B1 is empty

C38 contains "N/A" (generated by formula from fact that B1 is empty)

C39 is empty (no entry has been made)

Day 5:

B1 contains "Joe"

C38 looks to Day 4::C39, finds an empty cell.

What do you want to happen at this point? Can CASE 2 (below) be handled by the SAME action

CASE 2:

Day 2:

B1 contains "Joe"

C38 contains 100

C39 contains 102

Day 3:

B1 contains "Joe"

C38 contains 100 (retrieved from Day 2::C39)

C39 contains 108 (entered manually)

Day 4:

B1 is empty (No activity)

C38 contains "N/A" (generated by formula from fact that B1 is empty)

C39 is empty (no entry has been made)

Day 5:

B1 is empty (No activity)

C38 contains "N/A" (generated by formula from fact that B1 is empty)

C39 is empty (no entry has been made)

Day 6:

B1 contains "Joe"

C38 looks to Day 5::C39, finds an empty cell.

What do you want to happen at this point? Is that action exactly the same as for CASE 1, or does it require another step be added to the formula?

Question:

What is the purpose of placing "N/A" in C38 on days when there has ben no activity?

If there is none, or if the purpose is decorative, or is trivial, then placing the most recent odometer reading in that cell will greatly simplify the issue that arises when the trucl is inactive for one or more day.

With the odometer reading always carried over from the previous day, the formula can always find the current reading in one of two places:

• If the truck was driven on the previous day, the current reading will have been entered in C39 of that day's table.
• If the truck was not driven the previous day, the current reading will be in C38 of that day's table.

A single test is required: Is there data in C39 of the previous day's table?

• Yes: Get that data.
• No: Get the data from the cell above it (C38).

I'll also take a look at your formula in a separate post.

Regards,

Barry

"IF(LEN(C39)<1,"N/A",LOOKUP(MAX(1,OFFSET(Truck 1::$A$1,3,1,1,MATCH(J1,Truck 1::A2:AH2,0)−2)),Truck 1::A4:AH4,Truck 1::A14:AH14))"

Parsing this:

IF(LEN(C39)<1,"N/A",LOOKUP(MAX(1,OFFSET(Truck 1::$A$1,3,1,1,MATCH(J1,Truck 1::A2:AH2,0)−2)),Truck 1::A4:AH4,Truck 1::A14:AH14))

Tests C39 for content. If none, returns N/A. Otherwise executes LOOKUP.

LOOKUP(MAX(1,OFFSET(Truck 1::$A$1,3,1,1,MATCH(J1,Truck 1::A2:AH2,0)−2)),Truck 1::A4:AH4,Truck 1::A14:AH14)

Match reads the value in J1 of 'this table'. Searches for this value in columns A to AH of row 2 of Truck 1, accepting only an exact match, and returns a number equivalent to the position in the list of this value.

-2 subtracts 2 from this number, and the result is returned to OFFSET as its fifth argument.

For 'this table' = Day 4, MATCH will return 7 (4-Apr is fourth in the list, which includes the word Date in column A, and the two empty positions in columns B and C), -2 will make that 5, and 5 will be returned to OFFSET.)

LOOKUP(MAX(1,OFFSET(Truck 1::$A$1,3,1,1,5)),Truck 1::A4:AH4,Truck 1::A14:AH14)

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

base is cell A1 of Truck 1. From here, the formula says go down 3 rows (to row 4), right 1 column (to column B),get the values for one row and five columns (B4, C4, D4, E4 and F4). OFFSET returns this list of five values (0,0,21650, 21722,0) to MAX.

LOOKUP(MAX(1,0,0,21650,21722,0),Truck 1::A4:AH4,Truck 1::A14:AH14)

MAX extracts the largest value in that list (21722) and returns it to LOOKUP. (I don't see an obvious reason for the 1 at the beginning of the list. It's in the formula as a fixed value, before OFFSET.)

LOOKUP(21722,Truck 1::A4:AH4,Truck 1::A14:AH14)

LOOKUP takes the value provided by MAX (21722) and looks for it in columna A to AH of row 4 (!?!). Although it will not find that value, it will find a 'close-match', the 3 (the largest value in the row less than or equal to the search value) in the column for 6-Aug, and will return the value from row 14 of the same column (21800).

But the LOOKUP part of this formula is redundant. MAX has already returned the value you want in the previous step. Edit your formula to remove the LOOKUP part, and it should provide correct results:

=IF(LEN(C39)<1,"N/A",MAX(1,OFFSET(Truck 1::$A$1,3,1,1,MATCH(J1,Truck 1::A2:AH2,0)−2)))

Regards,

Barry