Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have multiple sheets relating to each room in a house, I also have a master sheet showing the overview of the whole house, what id like to do is pull all the data from the room sheets into the master sheet so that I can use filters across the whole house.

I could just copy and paste the data across but id like the data to be dynamic so that if I make a change in the room sheets its reflected in the master sheet.

I know I can reference cells by using =bedroom!A1 in the cell but how can I do the whole sheet?

Im using Google docs for the spreadsheet as it allows every one in the team access to the data, it seems to be very similar to excel so im sure the awnser would be similar

share|improve this question
    
Based upon my limited experience with Google docs, I'm not so sure that the answer would be similar. –  dav Jun 29 '12 at 12:27
2  
It's tough to answer without seeing the actual layout, but typically you would do this the OTHER way. You maintain data in the master sheet, then use formulas on the room sheets to pull the data out dynamically, especially since there could be any number of rooms but only one master. Constructed correctly, you might just end up with a room sheet layout where you need only copy the room sheet and rename it to then have all the data for that new room name flow into the sheet. My answer(s) will be Excel only, no exp with Google Docs directly. –  Jerry Beaucaire Jun 29 '12 at 14:08
    
having a link to the whole sheet will link to probably hundreds of thousands of unused cells with no data, yet a reference would be kept. It would be better to link only to the cells you really need. –  datatoo Jul 6 '12 at 0:16

1 Answer 1

up vote 0 down vote accepted

Why bother at all with the secondary sheets? They don't seem to add any value other than separating out the data.

Instead, keep everything in a single sheet (as suggested by @jerry-beucaire) and instead us filters to restrict the data to a single room.

I've put up a very simplistic spreadsheet to illustrate:

https://docs.google.com/spreadsheet/ccc?key=0Aj8m4we0FdxcdEVNYVVEdXB6VlVlWk92OEdKazZlMFE

What I would normally do in Excel would be to add a very simple macro to take a cell value as the room number and apply the filter automatically on change of cell value. That way, the users don't need to know about Excel filters, they just change the number to get a different room. I'd maybe allow room zero or "A" or something that would show all rooms.

By using data tables or a data entry form, you could also make the data entry easier by automatically filling the room number if a filter is set.

Google spreadsheets also have script capabilities but I'm afraid I haven't explored them as yet so I don't know how easy it would be.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.