Semi Advanced Excel help - references
Discussion
I'm at a bit of a loss trying to google the answer, as perhaps I don't know the name for the feature or technique that exists in excel.
I work from a spreadsheet provided by my company for pricing. It's fairly complex and is locked.
I want to make a summary sheet - so i'd like to reference various cells, bring them together in the summary sheet, AND be able to manipulate the values in the summary sheet and they will be updated in their original locations too.
Cell references are easy enough ='SomeSheet'!G12 - but if I want to change that value, it of course gets wiped.
Trying to find the right way to do this
Thanks!
I work from a spreadsheet provided by my company for pricing. It's fairly complex and is locked.
I want to make a summary sheet - so i'd like to reference various cells, bring them together in the summary sheet, AND be able to manipulate the values in the summary sheet and they will be updated in their original locations too.
Cell references are easy enough ='SomeSheet'!G12 - but if I want to change that value, it of course gets wiped.
Trying to find the right way to do this

Thanks!
You can't directly do that in Excel and (probably) that is why the sheet is locked, to prevent such amendments.
You could set up macros to do what you described, but not if the sheet is locked.
Linking to the cells is straightforward - as you described.
An alternative in Excel would be to set up pivot tables to provide the reports that you require from the underlying data.
Then change the underlying data and all the reports will update once you click 'data refresh'.
You could set up macros to do what you described, but not if the sheet is locked.
Linking to the cells is straightforward - as you described.
An alternative in Excel would be to set up pivot tables to provide the reports that you require from the underlying data.
Then change the underlying data and all the reports will update once you click 'data refresh'.
Edited by langtounlad on Thursday 16th August 17:23
Go into Data and Connections, then add the data as a connection. Insert a table Pivot Table from that connection and it'll bring a copy of the data in for you. You can modify this copy and it'll update your summary. If you refresh the data it'll over-write it, so either don't or change the table settings so it can only be manually updated.
You won't be able to write any changes back (via VBA) if it's read-only, but you could create a table of changes to go to the data owner so they can choose whether to apply hem for you as a rubbish work around.
You won't be able to write any changes back (via VBA) if it's read-only, but you could create a table of changes to go to the data owner so they can choose whether to apply hem for you as a rubbish work around.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


