Referencing cell contents from another sheet (in a specific way)

[jormungand888]

TL;DR version: I need a method to reference cell contents from Worksheet 1 in Worksheet 2 so that inserting/deleting/moving cells/rows/columns in Worksheet 1 does not automatically redefine the references in Worksheet 2.

Detailed version: I have a worksheet of data that needs to be regularly updated and edited. I use a number of detailed filters to examine different aspects of the data set, and I find it inconvenient to constantly re-enter the filter parameters when I want to switch from one filter to another. As far as I can tell, there is no method to store/save the parameters for multiple filters, and this wouldn't be an ideal fix anyway.

I decided that a better solution would be to create other worksheets within the same document whose cell contents exactly mirror the cell contents of the first worksheet in real-time. I could then apply one of the filters I use to each of these additional worksheets and click over to them when I want to view that aspect of the original data. When I wanted to edit the data set, I could do so in the first (unfiltered) worksheet and the changes would be reflected in the other worksheets.

I tried to achieve the above by directly referencing the cell contents of the first sheet, using syntax like " =Sheet1!A1". This accomplished most of my goals; if a cell's contents are directly changed in the first worksheet, the other worksheets reflect the change. However, when I edit the contents of the first sheet, I often need to insert/delete rows/columns (particularly the drag-and-drop insert mode). Such changes are not reflected in the other sheets in the way I'd like, even when using an absolute reference. When I move a row in the first sheet, I'd like it to move in the other sheets.

For example, I define rows 1 and 2 of Sheet2 so that they reference rows 1 and 2 of Sheet1, i.e., A1 = Sheet1!A1, B2 = Sheet1!B2, etc. If I use the insert mode in Sheet1 and move row 1 below Row 2, essentially switching the contents of the two rows, then the row contents in the other sheets do not switch; instead, the references in Sheet 2 themselves change. In Sheet2, I now see that A1 = Sheet1!A2, B2 = Sheet1!B1, etc. I assume that the same "updating" of references occurs whenever rows are inserted/deleted or whenever cells are moved. This is not the effect I want. Instead, I'd like the cell references in Sheet 2 to remain unchanged regardless of the changes made to Sheet 1.

Is there a way to mimic the effect of referencing cell contents so that operations in which rows/columns/cells are inserted/deleted/moved are duplicated in the other sheets?

Thanks very much for your help!

[AliGW]

Welcome to the forum.

I suppose we need a bit of background here: why do you need two workbooks containing identical (?) data and why must they be in exactly the same format?

[jormungand888]

Thanks!

I’d like to use Excel to house a data set that needs to be frequently updated, both by adding rows and also by rearranging the order of the rows. In addition, I frequently need to analyze the data in a way that requires different filters to be applied; I have to toggle from one filter to another and back again. Each filter is rather detailed, but as far as I can tell there isn’t a way in Excel to “save” the filters so that I can switch from one to another with a click or two. Instead, I’d have to manually re-enter the parameters of the filter each time I wanted to filter the data in a different way. So everything I described in the first post is my idea of a workaround to avoid the nuisance of constantly re-entering the parameters of different filters to examine my data set in the ways that I need to.

Of course, if anyone is aware of another way to achieve the same result, I’d be happy to approach the problem differently!

But regarding my original idea of a workaround: if I have my terminology straight, each workbook is its own Excel file. In that case, I don’t need two workbooks, but rather two (or more) worksheets in the same workbook. I would like each of those worksheets to contain the same data set is so that I can apply different filters to each worksheet to reflect different aspects in the data set that are of interest to me. The data in each worksheet needs to be identical, including formatting, because they aren’t different data sets; they are the same one, duplicated so that I can filter each one differently. If every worksheet except the first is simply referencing the cells in the first worksheet, then that first worksheet could serve as the “master” sheet; if the data set itself needs to be edited, I can make changes in that first sheet that will be reflected in all the other sheets. That way, each sheet is reflecting the latest version of the data, but each sheet can still be filtered differently. But for the reasons I described in my original post, simple cell references don’t get the job done.

I hope that clarifies things! I appreciate the consideration.

[AliGW]

Please attach a sample workbook - there are instructions at the top of this page.

Your profile says Excel for iPad - this is very limiting in terms of what we can suggest. Are you using a desktop version as well? If so, which one? Please update your profile accordingly.

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.