Good afternoon fellow Excel-ers! I am brand new to the forum, however have referenced it many times in past research as an outsider looking in. I must say, I seem to have always found solutions in the replies of the members of this fine forum.
Today, I find myself perplexed with an issue using the INDEX function. The brief background is: I have developed a document control system that manages a company’s quoting system. Management is such that the first tab of a workbook is a LOG page that contains line number entries of specific data in each cell of the line. I refer to the line numbers as the record number. Thus as a new entry is created, the data for that entry is filled in the cells of the new record number line.
The second tab of this workbook is a summary sheet that summarizes data from the following 12 tabs that are department specific tabs each for data entry to support the quote and Advanced Product Quality Planning process. On this second tab, the workbook manager selects the specific record number of interest, and the data from the LOG tab populates both the summary sheet and the respective departmental worksheets. The workbook manager can then click on a Command Button on the Summary Sheet that will create the Summary Tab and all departmental tabs to another saved workbook identified by the line item (part number) and record number specified in the LOG tab. All data links from the departmental worksheets are maintained to the LOG tab as well, so easy modifications to the respective documents are complete, accurate, and controlled by the LOG page.
Once a copy of the Summary Sheet and Departmental worksheets separate and become their own workbook, I have the individual departmental worksheets linked to their workbooks summary tab for a quick reference of % completion.
I then have these summary page % completion cells linked to the LOG workbook via the INDEX function. This is the area that is causing me an issue. If I construct an INDEX function on the LOG workbook line of the specific record number and populate the array by clicking on the open newly created and named summary page that compliments that LOG record number, then add the row and column numbers, the % completion value from that specific Summary Page is displayed in the INDEX coded cell of the LOG workbook. However, if I try to construct that array name on the LOG workbook line by concatenation of the values to form the Record Number Workbook name and include with it the required formatting (“ ‘ “, “ ! “, “ [ ] “) then enter that concatenate cell value in the INDEX array value, I get a #REF! error. The exact same formula hard typed into the INDEX array value yields the Indexed cell value as expected, but will not work with concatenate.
I have also attempted to use the INDIRECT function to reference a cell that has the alphanumeric value of the concatenated cell and still get the same error.
The Summary Workbook is SQ101Tw-T05, that is SQ one, zero, one, and “T” zero, five.
WORKING FORMULA (HARD Typed the file name and tab) into cell on LOG workbook
=INDEX('[SQ101Tw-T05.xlsm]QUOTEAPQP'!$A$1:$Z$50,6,14)
I have this file data concatenated into a cell (GC29) of LOG Workbook.
‘[SQ101Tw-T05.xlsm]QUOTEAPQP’!A1:Z50
=INDEX(GC29,6,14) yields a #REF! error
I have the alphanumeric GC29 in cell FZ29, thus…
=INDEX(INDIRECT(FZ29),6,14) yields a #REF! error as well.
I have the SQ1001Tw-T05 Summary workbook opened in this exercise and working these formulas in the complimentary cell of the LOG workbook.
I suspect my issue exists with the concatenated values and/or the concatenation relationship to the numeric values in the file name.
This is driving me crazy, as I cannot seem to wrap my hands around what I believe to be a very simple error.
Any thoughts or input from this amazing group will be happily accepted.
I thank you in advance for your interest.
Today, I find myself perplexed with an issue using the INDEX function. The brief background is: I have developed a document control system that manages a company’s quoting system. Management is such that the first tab of a workbook is a LOG page that contains line number entries of specific data in each cell of the line. I refer to the line numbers as the record number. Thus as a new entry is created, the data for that entry is filled in the cells of the new record number line.
The second tab of this workbook is a summary sheet that summarizes data from the following 12 tabs that are department specific tabs each for data entry to support the quote and Advanced Product Quality Planning process. On this second tab, the workbook manager selects the specific record number of interest, and the data from the LOG tab populates both the summary sheet and the respective departmental worksheets. The workbook manager can then click on a Command Button on the Summary Sheet that will create the Summary Tab and all departmental tabs to another saved workbook identified by the line item (part number) and record number specified in the LOG tab. All data links from the departmental worksheets are maintained to the LOG tab as well, so easy modifications to the respective documents are complete, accurate, and controlled by the LOG page.
Once a copy of the Summary Sheet and Departmental worksheets separate and become their own workbook, I have the individual departmental worksheets linked to their workbooks summary tab for a quick reference of % completion.
I then have these summary page % completion cells linked to the LOG workbook via the INDEX function. This is the area that is causing me an issue. If I construct an INDEX function on the LOG workbook line of the specific record number and populate the array by clicking on the open newly created and named summary page that compliments that LOG record number, then add the row and column numbers, the % completion value from that specific Summary Page is displayed in the INDEX coded cell of the LOG workbook. However, if I try to construct that array name on the LOG workbook line by concatenation of the values to form the Record Number Workbook name and include with it the required formatting (“ ‘ “, “ ! “, “ [ ] “) then enter that concatenate cell value in the INDEX array value, I get a #REF! error. The exact same formula hard typed into the INDEX array value yields the Indexed cell value as expected, but will not work with concatenate.
I have also attempted to use the INDIRECT function to reference a cell that has the alphanumeric value of the concatenated cell and still get the same error.
The Summary Workbook is SQ101Tw-T05, that is SQ one, zero, one, and “T” zero, five.
WORKING FORMULA (HARD Typed the file name and tab) into cell on LOG workbook
=INDEX('[SQ101Tw-T05.xlsm]QUOTEAPQP'!$A$1:$Z$50,6,14)
I have this file data concatenated into a cell (GC29) of LOG Workbook.
‘[SQ101Tw-T05.xlsm]QUOTEAPQP’!A1:Z50
=INDEX(GC29,6,14) yields a #REF! error
I have the alphanumeric GC29 in cell FZ29, thus…
=INDEX(INDIRECT(FZ29),6,14) yields a #REF! error as well.
I have the SQ1001Tw-T05 Summary workbook opened in this exercise and working these formulas in the complimentary cell of the LOG workbook.
I suspect my issue exists with the concatenated values and/or the concatenation relationship to the numeric values in the file name.
This is driving me crazy, as I cannot seem to wrap my hands around what I believe to be a very simple error.
Any thoughts or input from this amazing group will be happily accepted.
I thank you in advance for your interest.
