I neeed to link names to values in Numbers

Question

Dubbia Author

Level 1

24 points

I neeed to link names to values in Numbers

In table 1 I manually enter the names of the members.

In table 3 I manually enter the amounts paid by bank transfer.

In table 2 I manually enter the values paid in cash, while the values of table 3 are recorded with formula.

This allows me to have a table only with the bank details, for a check, and to have the total cash of the day.

Columns A of tables 2 and 3 take up column A of table 1 with formula PLAINTEXT

While the values of table 3 are reported in tables 2 with the VLOOKUP formula as seen from screenshots

In addition, in table 2 there is conditional highlighting to not show the zeros

The problem is when I update column A of table 1, adding or removing names, because the values in tables 2 and 3 remain fixed on the row and therefore connect to different names.

How can I make the values bound to the names and not to the line?

[Re-Titled by Moderator]

iMac 24″, macOS 15.1

Posted on Jan 10, 2025 1:30 AM

Reply

Answer 1

Camelot

Level 9

55,737 points

Jan 15, 2025 4:21 PM in response to Dubbia

Your problem lies in the fact:

> Columns A of tables 2 and 3 take up column A of table 1 with formula PLAINTEXT

By definition, PLAINTEXT() takes a reference and converts it to... plain text.

You likely have this setup as a static reference - e.g. cell Table 3::A3 probably says =PLAINTEXT(Table 1::A3), right? so it's just grabbing whatever value is in cell A3 and doesn't care if you move things around... whoever's name is in Table 1::A3 is what you get.

You likely need to add one level of indirection. Instead of using/relying on people's names, assign them some kind of unique account number, and use this as the key across your tables.

For example, if Table 1 looks like:

Where I've used an alpha-numeric label based on their initial, plus a number, but this really could be anything.

Now, in the other tables, instead of a PLAINTEXT() you perform a LOOKUP():

In Table 2, B2, for example:

=LOOKUP(A2,Table 1::A,Table 1::B)

This looks up the value in cell A2 in column A of Table 1, and returns the corresponding value from column B in Table 1.

Now, because it's doing a lookup, it will always find the right name associated with that account number, rather than relying on their position in the list. You could also use an VLOOKUP() since you're familiar with that.

A slightly better (although more complex) formula would use XLOOKUP() which adds some additional functionality when values aren't found (i.e. you entered an invalid account number). Using:

= XLOOKUP($A3,Table 1::A,Table 1::Name,"Invalid Account Number",0,1)

Will highlight invalid account numbers, whereas LOOKUP would simply return an error.

Reply

Answer 2

Dubbia Author

Level 1

24 points

Jan 19, 2025 10:14 AM in response to Camelot

Thanks for the advice I followed and it works, although I have to manually enter the codes in each table, every time I have to add a user, and manually sort the names in alphabetical order.

While the values finally remain connected to the names. The alternative would have been to merge all the tables into a single excel-style database.

Reply