Forum Discussion
Solved
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 wor...
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.
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.
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".