Populate & automatically add rows to multiple spreadsheets with data from a 'master' spreadsheet

Question

Level 1

0 points

Populate & automatically add rows to multiple spreadsheets with data from a 'master' spreadsheet

Hi,

I manage bars and have many spreadsheets for my stock information.

'Master' as I call it lists every item of stock, it's size, the supplier, costs, selling prices, margins etc.

Column A (the list of stock) is the important thing here, I need an exact copy of this list to populate Column A of my second spreadsheet (and a few others) 'Stock Take'. Also, I regularly add/remove stock items in my bars which means adding/removing rows in my spreadsheets which I have always done manually for each one.

What i'm hoping to achieve is something that automatically adds/removes stock items to Column A of every spreadsheet when I edit 'Master'.

I hope this all makes sense and somebody can help!

Thanks in advance!

MacBook Pro with Retina display, OS X Yosemite (10.10.4)

Posted on Aug 27, 2015 9:54 AM

Reply

Answer 1

Aug 27, 2015 9:13 PM in response to EAUdeVIE_UK

As much as we all love automation, I think that in your case, unless I am misunderstanding you, some data entry will have to continue. You should only have to edit your "Master" sheet, and all others can be automated. More detail would help with more specific answers.

Reply

Answer 2

t quinn

Level 5

7,042 points

Aug 28, 2015 5:23 AM in response to EAUdeVIE_UK

Hi EAUdeVIE_UK,

If you mean a seperate document when you say "Spreadsheet", then you need applescript to move data between the documents. this would require specific info as to document/sheet/table names.If you are talking about tables on seperate sheets within the same document then this can be done easily within Numbers. These two tables are on the same sheet but they don't need to be. As long as they have unique table names I don't have to specify a sheet.

This is the simplest way to move data between tables. I can fill this formula down and everything in the master column A will show in stock take. If I fill it to the right also I can duplicate the table entire. If I delete"beer" from the master it disappears from stock take.

quinn

Reply

Answer 3

EAUdeVIE_UK Author

Level 1

0 points

Aug 29, 2015 3:03 AM in response to t quinn

Hi t quinn,

thanks for the response. I am using one document with multiple tables and sheets. I understand about filling down, which is something I use regularly.

Below is a very basic example of my set up, Column A in both tables is exactly the same and must always remain so. I want to simply add a row for 'vodka' to my 'Master' table and have it appear in the same place automatically in the 'Stock Take' table. I would like a full row to insert automatically & the common data to populate Column A - 'vodka'

I understand in this basic example, inputting a row into each table manually is easy, however I have hundreds of stock items listed across many different tables/sheets.

I am nearly there in excel using 'Microsoft Query' if that helps?

Many thanks.

Reply

Answer 4

EAUdeVIE_UK Author

Level 1

0 points

Aug 29, 2015 3:08 AM in response to charles.christian14

Hi charles.christian14

I'm just posting the same response here as I did with t quinn - I think you'll find it much clearer - hopefully?!!

thanks for the response. I am using one document with multiple tables and sheets. I understand about filling down, which is something I use regularly.

Below is a very basic example of my set up, Column A in both tables is exactly the same and must always remain so. I want to simply add a row for 'vodka' to my 'Master' table and have it appear in the same place automatically in the 'Stock Take' table. I would like a full row to insert automatically & the common data to populate Column A - 'vodka'

I understand in this basic example, inputting a row into each table manually is easy, however I have hundreds of stock items listed across many different tables/sheets.

I am nearly there in excel using 'Microsoft Query' if that helps?

Many thanks.

Reply

Answer 5

Aug 29, 2015 4:27 AM in response to EAUdeVIE_UK

if you would like to have another table that has the same list from column A, then a simple index formula would achieve this perfectly. The formula would be similar to Index(master list::column A, row()). Place a formula in the cell a2 on table 2 and fill down. Read up on the index formula in the formula helper to assist in the syntax.

Reply

Answer 6

Lexiepex

Level 6

14,102 points

Aug 29, 2015 4:59 AM in response to charles.christian14

Maybe using a Template for the basic (always there) items helps?

Reply

Answer 7

t quinn

Level 5

7,042 points

Aug 29, 2015 1:24 PM in response to EAUdeVIE_UK

Hi EAUdeVIE_UK,

There is something I am missing regarding your question. What you are asking is nothing that the simple answer above does not cover. If you have my three row table above with wine and beer and want to add a row for whisky, if there is a 4th row in "Stock Take" with the formula =Master::A4 it will populate when I add a value to the master. If I have three rows in the master and four in stock take the formula will throw an error. Your Stock Take table needs to have the extra row to accommodate "whiskey" when it gets added. Empty rows can be hidden with a filter.

Is "Stock take" also a data entry table? Is it what I might call inventory? Or is it gathering data from other tables?

How are things not working now?

Reply

Answer 8

EAUdeVIE_UK Author

Level 1

0 points

Aug 30, 2015 2:38 AM in response to charles.christian14

charles.christian14 thank you I shall read up on that...

Reply