Ottsel
Board Regular
- Joined
- Jun 4, 2022
- Messages
- 167
- Office Version
- 365
- Platform
- Windows
I have a sheet where I need to extract a small array of data and using reference formulas saves a lot of time, but I feel there is a way to structure it, so it can a bit more automated.
Row 1 is my header, and column A contains data that'll help fill out the other cells
Columns B:F will contain the formula and depending on what's if column A contains any values it'll need to be entered.
The beginning of the reference is always:
='G:\JC\
After which the content in 'G1' \ 'A1' \
Then I encounter one that usually always stops me from being able to complete this, which is the first half is always the same followed by the file type, but the number will change, which depends on what is in column A
CMT Column A
# Column B
REF Column C
ID Column D
LOT COLUM E
NAME Column F
the endings of the formula change depending on the column they're in, but will be structured the same after all the references have been met.
After being completed will look like this:
B2
C2
D2
E2
F2
Column A could contain multiple inputs, but normally ranges anywhere from 1-10 entries.
Any help, ideas or code examples would be highly appreciated.
Row 1 is my header, and column A contains data that'll help fill out the other cells
Columns B:F will contain the formula and depending on what's if column A contains any values it'll need to be entered.
The beginning of the reference is always:
='G:\JC\
After which the content in 'G1' \ 'A1' \
Then I encounter one that usually always stops me from being able to complete this, which is the first half is always the same followed by the file type, but the number will change, which depends on what is in column A
CMT Column A
# Column B
REF Column C
ID Column D
LOT COLUM E
NAME Column F
the endings of the formula change depending on the column they're in, but will be structured the same after all the references have been met.
After being completed will look like this:
B2
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]Plan'!$AN$4
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]LCV'!$M$3
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]Plan'!$B$11
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]Fm'!$I$8
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]Jcm'!$N$5
Column A could contain multiple inputs, but normally ranges anywhere from 1-10 entries.
Any help, ideas or code examples would be highly appreciated.