Semi Advanced Excel help - references
Semi Advanced Excel help - references
Author
Discussion

devnull

Original Poster:

3,873 posts

188 months

Thursday 16th August 2018
quotequote all
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 smile


Thanks!

langtounlad

799 posts

202 months

Thursday 16th August 2018
quotequote all
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'.

Edited by langtounlad on Thursday 16th August 17:23

devnull

Original Poster:

3,873 posts

188 months

Thursday 16th August 2018
quotequote all
Thanks very much - this looks much more difficult than I first thought, cripes.

I only want to 'clone' pertinent bits of data that I commonly use into one area and just be able to tweak those to see the outcome.

aquarianone

502 posts

208 months

Thursday 16th August 2018
quotequote all
Can you copy/paste special - value from your original sheet into a new tab...and then reference the new sheet, that you can now tinker with....

iwantagta

1,324 posts

176 months

Thursday 16th August 2018
quotequote all
If its tabs within a spreadsheet there are macros out there that unlock them all......

paulrockliffe

16,677 posts

258 months

Friday 17th August 2018
quotequote all
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.