MacOS – How to combine two text values from different cells to equal another cell

macosnumbers

Let’s say I always know my row will be 14.

I only know the column by another value above it.

For example, $Q$7 = A

I want to be able to take that value A and add it so that my formula is now $A$14.

I need a formula that finds the value of $Q$7 and places it as $A$14.

Then if I applied that to let’s say $D$5 where $D$5 = R
the same formula would now equal $R$14.

Does that make sense?

I know to use = $A$14 in the cell but I want it to depend on the column.

Thanks for any help. enter image description here

Best Answer

I would approach this a little differently to (hopefully) simplify things a little!

Let's assume you will use Rows 4, 7, 10, etc to enter the text of your words/sentences (which is the approach in your screenshot).

Let's also assume that you're only going to use Columns C to W to enter your text into (although there's no reason why it can't be more columns, but this is just what I gather from your screenshot).

For this answer, let's assume your Alphabet is in Row 50 and your Substituted letters are in Row 51.

In summary, this means:

the formula you need will have to be entered in every cell from C5 to W5, C8 to W8, C11 to W11, and so on
your alphabet will be in Row 50
your substituted letters will be in Row 51.

So, let's build this using the following steps:

In Row 50 add the letters of the alphabet from A50 to Z50
In Row 51 add your substituted letters
Enter the following formula in cell of C5:

=IFERROR(LOOKUP(C4, $A$50:$Z$50,$A$51:$Z$51),"")

(The reason we're using IFERROR in the formula is so Numbers will ignore any spaces in your sentences, rather than displaying an ugly error icon.)

Copy and paste the formula from cell C5 to your other cells (i.e. cells D5:W5, C8:W8, C11:W11, and so on)
Now start entering your text to be ciphered into cells C4:W4, C7:W7, C10:W10 and so on
As you enter the text, the ciphered result should appear in the cell below. If it's a space, the cell below will remain blank

NOTES:

Be careful to only include the letter and nothing else in each cell of your Alphabet row. Having a space or anything else will cause problems. For example, if you entered your E with a space after it, then Numbers will never find the E because it's looking for an 'E' and not a 'E ', if you get what I mean.
If you have the need to have no substitute value for some letters of the alphabet, then the result will be a 0 (i.e. a zero is displayed) instead of a blank cell. If this applies to you, you could create a conditional highlight to hide the result.
Personally, I would actually put your alphabet and substituted values in another worksheet, but this may not be necessary for your purposes.

Related Solutions

Getting the First Cell NOT Containing Certain Text in Numbers

As it turns out, I've figured it out. It did require Applescript. What I did was loop through the rows and selected the values in the row where "not" was not present in column B. Here's the Applescript I used via Automator:

on run {input, parameters}

    tell application "Numbers"
        tell table "Calculator 1" of sheet 2 of document of front window
            set aRange to "A2:A22"
            set bRange to "B2:B22"
            set aRows to the rows of range aRange
            set bRows to the rows of range bRange

            set correctRow to -1
            set correctCol to -1

            repeat with i from 1 to count of bRows
                set aCell to "A" & (i + 1)
                set bCell to "B" & (i + 1)

                if value of (cell bCell) is not "not" then
                    set correctRow to value of (cell aCell)
                    set correctCol to value of (cell bCell)
                end if
            end repeat

            set the value of (cell "C2") to correctRow
            set the value of (cell "C4") to correctCol
        end tell

        display dialog "Updated!"
    end tell

    return input
end run

Keep in mind that the cells I used are specific to my spreadsheet, but generally this method could be applied for any 2D MATCH() function.

Create a table has the name of the each row in each row of the header column, and use the desired MATCH() function per row in this way (see the picture of the table in my question).
Use this Applescript to loop through the MATCH() functions and find the correct row and column. Change "C2" to whichever cell will be your Correct Row and "C4" to whichever cell will be your Correct Column.
Use the INDEX() function to get the value of the cell and put it into another cell, using the coordinates the Applescript calculated for you.

In Numbers can you auto-populate a cell with a date when another cell is populated

I offer this answer only because at least it provides a workaround, albeit a slightly manual one.

Assuming that B1 is the cell that needs to have the date and time auto-populated when cell A1 is filled in, enter this formula in cell B1:

IF(ISBLANK($A1),"",NOW())

Now, when the time comes that cell A1 is filled in, cell B1 will auto-populate with the date and time.

However, to prevent cell B1 from updating if/when cell A1 is edited again, you now need to do as follows:

After B1 is populated, select cell B1
Use commandC (or go to Edit > Copy) to copy the cell
Use shiftcommandV (or go to Edit > Paste Formula Results)
Now the formula in cell B1 is replaced with the calculated value only, thereby meaning it will no longer calculate in future

NOTES:

In case this is useful for anyone else trying to come up with an answer, I also thought of:

using an AppleScript script to replicate the above, but then realised that using a script would be no quicker that just using the Copy/Paste functionality within Numbers.
creating an Automator service and then allocating a keyboard shortcut to that, but that too would be no quicker as you'd still need to select the cell, so using the built-in shiftcommandV shortcut is no slower than using a customisable shortcut.

Regardless, someone more adept at AppleScript may think of a more elegant solution that works with Numbers to achieve the OP's goal.

Best Answer

Related Solutions

Getting the First Cell NOT Containing Certain Text in Numbers

In Numbers can you auto-populate a cell with a date when another cell is populated

Related Question