Skip navigation

How do I sync columns between sheets?

463 Views 8 Replies Latest reply: Feb 7, 2013 2:20 PM by big-cat RSS
mikexuereb Calculating status...
Currently Being Moderated
Jan 30, 2013 7:25 PM

Hello people. Let's say I have 5  spreadsheets and I want to create a 6th spreadsheet that contain all the column Bs of all the other 5 spreadsheet. I'd like the 6th spreadsheet to sync automatically everytime I open it. Is this possible? thanks

  • Yellowbox Level 4 Level 4 (3,900 points)
    Currently Being Moderated
    Jan 30, 2013 11:20 PM (in response to mikexuereb)

    Hi Mike,

     

    When you say "spreadsheets" I assume you mean several sheets within one Numbers Document. Yes, Sheet 6 can be a summary of all the B columns of Sheets 1 to 5.

     

    Screen Shot 2013-01-31 at 6.12.52 PM.png

    This is a screenshot of Sheet 6. I clicked on B2 of Sheet 6, typed = and then went to Sheet 1 Table 1. Clicked on B2 of that table, then enter (return) key. Fill Down.

     

    Column C in Sheet 6 refers to Column B on Sheet 2 (not sure if you want that).

     

    Hope this helps.

     

    Ian.

    MacBook Pro (13-inch, Mid 2012), OS X Mountain Lion (10.8.2)
  • Yellowbox Level 4 Level 4 (3,900 points)
    Currently Being Moderated
    Feb 1, 2013 10:35 PM (in response to mikexuereb)

    Hi Mike,

     

    I assume you got this far:

     

    Clicked on B2 of Sheet 6, typed = and then went to Sheet 1 Table 1. Clicked on B2 of that table, then enter (return) key.

     

    Cell B2 of Sheet 6 should now be selected. If not, click on it once to select it.

     

    The white circle on the bottom right of Cell B2 of Sheet 6 is the Fill Handle. Drag the handle down to Fill Down.

     

    If your Numbers document is set up as in my screenshot, you can also fill B2 on Sheet 6 across to copy the formula for the other sheets.

     

    You can download the Numbers User Guide from the Numbers Help Menu. Well worth a read.

     

    Ian.

  • Yellowbox Level 4 Level 4 (3,900 points)
    Currently Being Moderated
    Feb 1, 2013 11:44 PM (in response to Yellowbox)

    Sorry Mike,

     

    I just tried my advice:

     

    If your Numbers document is set up as in my screenshot, you can also fill B2 on Sheet 6 across to copy the formula for the other sheets.

     

    And it didn't work.

     

    This does work:

     

    Repeat Step 1 for each sheet. Then Fill Down each column on Sheet 6.

     

    My apologies.

     

    Ian.

  • Yellowbox Level 4 Level 4 (3,900 points)
    Currently Being Moderated
    Feb 2, 2013 9:48 PM (in response to mikexuereb)

    Michael,

     

    Thanks for the feedback.

     

    The formulas are in this form.

    =Sheet 1::Table 1 :: B2

     

    To avoid zeroes, use IF to check if the source cell is blank. If it is, insert "" (nothing) else insert cell value.

     

    =IF(Sheet 1::Table 1 :: B2="","",Sheet 1::Table 1 :: B2)

     

    Formatting dates:

    Select the column, then

     

    Inspector> Cells Tab > Cell Format > Date and Time

    Choose a format.

     

    Regards,

     

    Ian.

  • big-cat Calculating status...
    Currently Being Moderated
    Feb 7, 2013 2:20 PM (in response to mikexuereb)

    Hi just read your problems, while searching for a solution to mine, and I have the solution that you are looking for to remove the 0’s and I think I have the solution you want to customize formats.

     

    0’s

     

    • Select the column/columns you wish to change
    • Select ‘Show conditional format rules’ from Format Menu
    • Click on ‘Choose Rule’
    • Select ‘Less than or equal to’ from drop down menu
    • Type in 0.00 in the box
    • Click on ‘Edit’ button
    • Click on box next to ‘Text’ and select white from colour palette.
    • Click ‘Done’

     

    This changes the text to white so it is no longer visible.  If however, you have a coloured background in your cells then you should choose a text colour that is the same as the background.

     

    You can also customize other formats in here – depending on what you want to customize.  You can also use the method explained below.

     

     

    Customize Formats (I think this is what you are looking for)

     

    Follow the steps provided by Ian but instead of selecting ‘Date and time’ from the drop down menu select ‘Custom’ a dialogue box will appear that allows you to customize “Text and numbers’ or ‘Date and time’.

     

    Hope this helps

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.