Forum Discussion
Locking reference to another worksheet
Hello,
I'm setting up a home-made bibliography data system in Excel to use for my PhD dissertation since I can't find anything useful online.
I have set up separate, identical, data entry worksheets in a workbook named Bibl. 1, Bibl. 2, etc. that are similar to index cards.
Now, I want to link each cell in the "Bibl. X" pages to a separate worksheet, named Section 3, that summarizes the "Bibl. X" so each "Bibl. X" entry is shown in a table of rows, one row per "Bibl. X" page.
This works just fine for the first table row, which has simple formulas: ='Bibl. 1'!C6 ; ='Bibl. 1'!G6 ; etc.
However, when I want to copy the formulas in the first row to the next row so the cell values are locked: 'Bibl. 2'!$C$6 , just the formula shows up.
I am also having an issue referencing to a worksheet cell that contains a URL address. Here, again, just the formula shows up regardless of whether I use $ or not.
I realize this is probably a very simple problem, but I can't find a reference to this in "help" either in Excel or online. This is probably because I don't know how to phrase the question that'll get me to the right help page for this issue.
Thanks very much for any suggestions regarding where I can find help or how to fix the formula.
Tomas
First, select the table, and set its number format to General.
In A7:
=IFERROR(INDIRECT("'Bibl. "&ROW()-6&"'!C6")&"","")
In B7:
=IFERROR(INDIRECT("'Bibl. "&ROW()-6&"'!G6")&"","")
etc.
7 Replies
Tomas-Vitek I have similar issue… Maybe @Hans Vogelaar will know solution as well.
Unfortunatelly I I'm not allowed to share worksheet, but I can tell you, that each individual Workbook represent one month of statistical data and problem is this:I struggle with "formula autocorrection" that cause to crash formulas referencing sheets in the same workbook, if you copy them from Original_Workbook to New_Workbook
I mean, if I copy =IF(('1'!$AS5)="";"";'1'!$AS5)
It becomes =IF(('[Original-Workbook.xlsx]1'!$AS7)="";"";'[Original_Workbook.xlsx]1'!$AS7)
Doesn't matter if I just Copy&Paste or Copy-Sheet, this bogus autocorrection is trigerred.Normaly, I would just open Original_Workbook and "Save as" New_Workbook, but it already contains statistical data within intricate formulas - so copying those would be maybe even more complicated, than manually adjusting this mid-way formula (and other similar ones) workbook by workbook.
Curently I have to do that 4 months (= 4 workbooks with at least 2 new sheets and a lots of formulas each) back, but probably have to do that 12 months (= 12 workbook) back at the end.
Also, there is a risk, that other new features will be requested, creating same problem again, so I need to find more straightforward solution.One option:
- Select the cell with the formula you want to copy.
- Click in the formula bar.
- Select the entire formula.
- Copy it (Ctrl+C).
- Switch to the other workbook.
- Select the cell where you want to create the formula.
- Paste (Ctrl+V).
- If required, fill down.
- Not a solution.
What you propose is just basic Copy&Paste that works only for single cell. But as I mentioned before, it's month-worth data, which means, that I need to copy on average 500 cells per sheet from one or more sources, since I aggregate statistical data from in-worsheet and Out-Worksheet sources - and I need to copy the design of new feature from one Workbook to several historical ones.
In the case I used as an example, I can get around manually through selective replacement of "[Original_Workbook.xlsx]1'" for "'1'", however I would prefer more stable solution - equivalent of $ in the cell reference,
OR
If posible, Ii would be AWESOME if I could disable autocorrect completely - as for example in other workbooks, I can't edit references, if reference doesn't exist at moment and I'm forced to create "fake future" workbooks, just to be able to create reference on them.
E.g. Excel straight up refuse to replace "[January_2023.xlsx]" with [January_2024.xlsx] unless January_2024.xlsx physically exist.
First, select the table, and set its number format to General.
In A7:
=IFERROR(INDIRECT("'Bibl. "&ROW()-6&"'!C6")&"","")
In B7:
=IFERROR(INDIRECT("'Bibl. "&ROW()-6&"'!G6")&"","")
etc.
Thank you very much for your reply! I'll make the first thing adjustments first thing tomorrow. HansVogelaar
