how to insert a predetermine value on a cell from a value on another cell

Hi gomlic,

"I am trying to set automatic values on cell B1 and D1 by entering a value on A1."

Is there a mathematical relationship between the value entered in A1 and the resulting values in B1 and D1, or are those values listed on a lookup table?

In your post, what is being entered in A1?

What is the result in B1?

What is the result in D1?

How is Numbers to get from the first to the result?

Regards,

Barry

Here's an example, using LOOKUP. I duplicated the tabe on the left twice to give examples with three different values chosen from the POP-UP in cell A1. All three are using the same LOOKUP table, "Table 2" Formulas below.

Formulas:

Table 1: B1: =IF(LEN(A1)<1,"",LOOKUP(A1,Table 2::A,Table 2::B))

Table 1: D1: =IF(LEN(A1)<1,"",LOOKUP(A1,Table 2::A,Table 2::C))

The POP-UP menu is set to Start with Blank.

The IF part of the formulas sees the 'blank' as having a zero character length, which is less than 1, so the comparison returns TRUE, and the formula returns a null string, which appears as an 'empty' cell. If any other choice is made, the cell contains a value with a length of one or more characters, the comparison returns FALSE, and the LOOKUP part of the formula returns the looked up value from the LOOKUP table.

Regards,

Barry

HI Gomic,

Here is the formula in my example: IF(LEN(A1)<1,"",LOOKUP(A1,Table 2::A,Table 2::B))

Here is the formula in your example: IF(LEN(D6)>1,,LOOKUP(D6,'Table 1-1'::A2:A15,'Table 1-1'::B2:C15))

I see several differences:

LEN(xx)<1,"",...

LEN(xx)>1,(nothing),...

The first returns TRUE if the cell (xx) contains less than one character (ie. is empty). On TRUE, IF returns the result "" - a text string with no content (and no length).

If the cell contains one or more characters, the comparison returns FALSE, and IF calls LOOKUP.

The second returns TRUE if the cell (xx) contains more than one character. On TRUE, IF goes to the space after the first comma, finds no instructions, and returns an error message: "You must specify a value for all the required arguments.".

If the cell contains 0 characters (ie. is blank or empty) or one character (suche as an integer between 0 and 9 inclusive), IF calls LOOKUP

LOOKUP(xx,tablename::A,tablename::B)

LOOKUP(xx,tablename::A2:A15,tablename::B2:C15)

A in the first example references all the cells (except those in header rows or Footer rows) in column A.

A2:A15 in the second references cells A2 to A15 of column A.

If your lookup table has 15 rows, including one row (row 1) defined as a Header row, the two forma are equivalent.

B in the first references all…in column B

B2:C15 in the second references cells B2 to B15 AND C2 to C15, and will cause this error message to be returned whenever LOOKUP is called: "Vector arguments can contain only one row or column."

Change B2:C15 to either B2:B15 or C2:C15, depending whether the results you want in the cell containing the formula will come from column B or from column C.

I'm having difficulty seeing the connection between your formula and the columns of data beneath it. The formula names the lookup table "Table 1-1", but none of the lists in your post are labeled as being on a table with this name.

The Formula is looking for a value in column D, which in your original description was a column where formula results were expected.

Should edits to the formula based on the information above not be enough to solve the issues, please supply some accurate information applying specifically to your table. I (or anyone else jumping in here) need to know the location of a formula you are asking about, the actual formula as it appears in that cell, the location of each cell and/or range of cells referenced by that formula, and the content of any error messages presented.

Clicking once on an error triangle will allow you to read (and to select and Copy) the error message. The copied message can be pasted into your message here (as I've done above).

Regards,

Barry

What is the address of the cell that contains the formula shown?

"A2 have a popup menu with values 0-13. I am trying to insert an specific value on B2 and C2 just by entering a value on A1 (0-13)"

Do you mean:

"A2 have a popup menu with values 0-13. I am trying to insert an specific value on B2 and C2 just by entering a value on A2 (0-13)"

Here are the two formulas from my June 30 reply:

Table 1: B1: =IF(LEN(A1)<1,"",LOOKUP(A1,Table 2::A,Table 2::B))

Table 1: D1: =IF(LEN(A1)<1,"",LOOKUP(A1,Table 2::A,Table 2::C))

These were written to fit your original specification that cell A1 would contain an number from 1 to 13, and the results of the formulas would be displayed in cells B1 and D1. Formulas return their results to the cell they are place in. The first is in Table 1::B1, and returns its result to that cell. The second is in Table 1::D1, and returns its result there.

Both formulas get the value from cell A1 (of Table 1), lookup that value in column A of Table 2, and return the matching result from column B of Table 2 (formula in B1) or from column C of Table 2 (formula in D1)

If I have guessed correctly, and the "A1" in the statement quoted above was a typo, and should have been A2, then these formula, entered in B2 of the table I've named "Main", then filled right to C2, should provide the results you want.

Main::B2: =IF(LEN(A2)<1,"",LOOKUP(A2,Table 2::A,Table 2::B))

Main::C2: =IF(LEN(A2)<1,"",LOOKUP(A2,Table 2::A,Table 2::C))

Regards,

Barry

"Any ideas why is not working?"

Not without seeing the actual formulas you have in B2 and D2.

Regards,

Barry

Hi gomic,

Just rediscovered this while cleaning up the mail. Apologies for not having replied earlier.

Your formulas above:

B2: IF(LEN(A2)<1,,LOOKUP(A2,E1:E7,F1:F7))

D2: IF(LEN(A2)<1,,LOOKUP(A2,E1:E7,G2:G7))

When I enter the first into cell B2, I get a red triangle with this error message: "You must specify a value for all the required arguments."

If I double click on the cell, the formula editor opens and shows me which argument has not been specified:

The two commas following the first 1 need something between them that tells IF what to do when the test ( LEN(A2)<1 ) returns TRUE. My choice, shown in the formula below, was to place a text value with no length, known as a 'null string'.

From my earlier post. Except for the struck through parts (which are correct in your first formula), yours should be identical.

B2: IF(LEN(A2)<1,"",LOOKUP(A2,look-where,return values))

The second formula has the same error, plus one more. With the error described above corrected, the formula works, but returns the wrong result:

Note the colour highlighting in columns E and G. These tell you the range of cells in which the value in A2 may be found, and the range of cells from which to return the value in the same position as the found value in the search column.

The search value 1 is found in the second position in the list in column E, so LOOKUP returns the value in the second position of the highlighted list in column G.

Corrected version:

D2: IF(LEN(A2)<1,"",LOOKUP(A2,E1:E7,G1:G7))

Regards, and apologies again,

Barry