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
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.
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.
I'm following your steps but when I come to Insert>Fill>Fill Down, 'Fill Down' is grayed out.
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.
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.
Thank you Ian.
You've saved me loads of time.
I manage 5 properties that are rented out as holiday homes and I'm using this method to organise the visitor's booking days. So I can check quickly which properties are available when an enquiry comes in. While all the details of each booking are on the allocated sheet, the first sheet is a collection on the names.
Any idea how I can avoid having 0s everytime there is an empty cell? ..however I can live with that.
And do you know how I can remove the year from the automated calendar in row A, ie just '10 May' instead of '10 May 2013' ?
Thanks again.
Michael
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.
Thanks Ian
Formatting dates worked. (I wish there was an option to customizable formats)
The equation to remove the 0s didn't work. But I don't want to take up more of your time.
thank you
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
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 🙂
How do I sync columns between sheets?
