Forum Discussion
Locking reference to another worksheet
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.
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.I'd create a template workbook with all the formulas that you need in place, but no data.
Create a new workbook from this template each time you need it, and start entering data in it.
- As I mentioned in my first post, that's how I do it when generating new workbook.
However time to time I need to add new sheet with new feature to already existing workbooks and agreggate quarterly/yearly data through the new feature.
And as I mentioned before, it's nigh impossible to copy template of said new feature without it getting mutilated by "autocorrection".