Using the excelJs library in a Node.js project, my goal is to generate an Excel workbook. On the first sheet, I will display all the items. In this sheet, I will utilize two dropdowns, one for item_type and the other for item_unit, which are linked to each other. My approach involves creating sheets based on item_type, and within each sheet, I display the corresponding item_unit.
Column E is designated for the item_type dropdown, while column F is for the item_unit dropdown. However, the formula used in the item_unit column only operates within a specific range, whereas I require it to dynamically adjust based on the selected sheet's column range.
formula: [=INDIRECT("'"&INDIRECT("E"&ROW())&"'!$B$2:$B$3")]
However, I need the range to vary for each column on each sheet. For instance, in the "Weight" sheet (item type), we might have 5 rows, while in the "Size" sheet (item type), we could have 7 rows.