Text in Numbers from one sheet to the other (via function)

Question

Text in Numbers from one sheet to the other (via function)

Hello,

As a violin teacher, I am trying to make a recital program using Numbers. I have created a nice template that will take the information from "sheet 2" (which includes names, titles of pieces, and composer) and will put it nicely into "sheet 1." I have decided to move some students around because one needs to start first. I altered the order (by dragging and dropping the rows in "sheet 2") but the order in "sheet 1" remains constant. I checked the function, and the number of the row/column that each cell is supposed to reference changed as I moved the cells in "sheet 2."

I would love to have a solid function that will ALWAYS have a cell in "sheet 1" reference "sheet 2 - A3" (for example), regardless of if I change information or move cells in "sheet 2."

Is this an option? I have found that, basically, whatever I entered when I created the format sticks and moves with the cells when I move them.

Thank you!

iPad Air

Posted on May 5, 2016 7:43 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

May 6, 2016 12:42 AM in response to shstkvchvlnst

Hi s',

There's no need to move the cells around in Sheet 2. Set the information up as shown on the table "Data" on the right. The numbers in the first column show the order o performance for the recital. If you need to change the order, the only data that needs changing are the numbers beside the students whose place in the program is changed.

"Program" contains the list of performers, titles and composers in the order they will appear.

On this table, the numbers in column A are entered data and are fixed in order. This column may be hidden if you do not want the numbers to appear on the printed programs.

The rest of the data on Program is gathered from Data using the formula below, entered into cell B3, then filled right to D3 and filled down to row 10.

B3: VLOOKUP($A,Data::$A:$D,COLUMN(),FALSE)

In the example, both tables are on the same sheet. To move Program to Sheet 2:

Select the table, press command-C to Copy
Click on the tab for Sheet 2.
Click on an empty part of Sheet 2, press command-V to Paste

You will now have two copies of Program, one on Sheet 2, the other on Sheet 1 (with the Data table). Both copies of Program are linked to Data, and either may be deleted if desired. If both are kept, you will have an instant indication of changes in the order of performance when you make changes in column A of Data.

Once Program has been formatted to your design, select and Lock that table (both copies, if you have not deleted one) to protect the formulas.

Regards,

Barry

Reply

Answer 2

May 8, 2016 7:29 AM in response to Barry

Thank you, Barry!

For some reason the formula you have given isn't working... When I paste in what you say it says it is invalid. After I click on it to edit it shows up like this:

Am I doing something incorrectly? I have put the two tables in one sheet and it looks like this. I must have missed something.

Reply

Answer 3

SGIII

Level 8

35,276 points

May 8, 2016 9:22 AM in response to shstkvchvlnst

You need to remove the B3: in front of the formula. Barry meant to enter the formula in cell B3.

But why are you going to all the trouble of complicated formulas and extra tables and such when you can just use the built-in sorting and filtering in Numbers?

Just click and choose from the dropdown to the right of the column letter.

Which, in one click, results in this:

You can then hide column A if you don't want to show it.

SG

Reply

Answer 4

Barry

Level 9

59,861 points

May 8, 2016 9:59 AM in response to shstkvchvlnst

Hi,

SG is correct. B3 is the location to place the first copy of the formula, and is not part of the formula itself. Click in the editor between B3 and VLOOKUP, then press delete to remove the space, the colon and B3.

Regards,

Barry

Reply