Hi
I am battling with named ranges, inserting columns and VLOOKUP's all living together in harmony.
My main workbook is called Figure the Deal2.xlsm (FTD2.xlsm) and the sheet populated with data is called StockSheet
The columns run from A1 to AI and each are labelled.
Column A1 named STOCK is populated with stock number of motor vehicles on hand and the range is A2 to A6999
The named ranges are:
STOCK which references to =OFFSET(StockSheet!$A$2,,,COUNTA(StockSheet!$A:$A)-1)
ColumnLabels which references to =OFFSET(StockSheet!$A$1,,,,COUNTA(StockSheet!$1:$1))
TABLE which references to =OFFSET(StockSheet!$A$2,,,COUNTA(StockSheet!$A:$A)-1,COUNTA(StockSheet!$1:$1))
In the main workbook (FTD2.xlsm) there is a sheet called JobcardTemplate.
Here there are various cells that input data via VLOOKUP accessing StockSheet. EG, =VLOOKUP($I$4,Table,MATCH(StockSheet!D1,ColumnLabel,0),0)
Cell I4 is a list box with data validation which access the stock numbers.
If I insert a column into StockSheet, the VLOOKUP formula corrects itself and the data is correct.
HOWEVER, all vehicle stock cards are stored in a folder called Job Cards. Each is a file layout is an exact copy of the StockCardTemplate except for the data which changes via the data validation cell.
The problem is that if I input a column in StockSheet, the formulas in each stock card file doesnt change and thus the data is incorrect.
If I look at the the named ranges in each file they reference back to the StockSheet in FTD2.xslm via the correct path
EG, TABLE references to =OFFSET('[Figure the deal2.xlsm]StockSheet'!$A$2,,,COUNTA('[Figure the deal2.xlsm]StockSheet'!$A:$A)-1,COUNTA('[Figure the deal2.xlsm]StockSheet'!$1:$1))
Please can someone help as this is a work in progress and if changes are made to the main workbook, it has to populate to all the other files.
Regards
I am battling with named ranges, inserting columns and VLOOKUP's all living together in harmony.
My main workbook is called Figure the Deal2.xlsm (FTD2.xlsm) and the sheet populated with data is called StockSheet
The columns run from A1 to AI and each are labelled.
Column A1 named STOCK is populated with stock number of motor vehicles on hand and the range is A2 to A6999
The named ranges are:
STOCK which references to =OFFSET(StockSheet!$A$2,,,COUNTA(StockSheet!$A:$A)-1)
ColumnLabels which references to =OFFSET(StockSheet!$A$1,,,,COUNTA(StockSheet!$1:$1))
TABLE which references to =OFFSET(StockSheet!$A$2,,,COUNTA(StockSheet!$A:$A)-1,COUNTA(StockSheet!$1:$1))
In the main workbook (FTD2.xlsm) there is a sheet called JobcardTemplate.
Here there are various cells that input data via VLOOKUP accessing StockSheet. EG, =VLOOKUP($I$4,Table,MATCH(StockSheet!D1,ColumnLabel,0),0)
Cell I4 is a list box with data validation which access the stock numbers.
If I insert a column into StockSheet, the VLOOKUP formula corrects itself and the data is correct.
HOWEVER, all vehicle stock cards are stored in a folder called Job Cards. Each is a file layout is an exact copy of the StockCardTemplate except for the data which changes via the data validation cell.
The problem is that if I input a column in StockSheet, the formulas in each stock card file doesnt change and thus the data is incorrect.
If I look at the the named ranges in each file they reference back to the StockSheet in FTD2.xslm via the correct path
EG, TABLE references to =OFFSET('[Figure the deal2.xlsm]StockSheet'!$A$2,,,COUNTA('[Figure the deal2.xlsm]StockSheet'!$A:$A)-1,COUNTA('[Figure the deal2.xlsm]StockSheet'!$1:$1))
Please can someone help as this is a work in progress and if changes are made to the main workbook, it has to populate to all the other files.
Regards
