[Q] How can i make dropdown value dynamic with help of another cell

[piyush123-jain]

const ExcelJS = require('exceljs');

const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Sheet1');

const categories = ['Fruit', 'Vegetables'];
const fruits = ['Apple', 'Banana', 'Orange'];
const vegetables = ['Carrot', 'Broccoli', 'Lettuce'];

// Set up data validation for the first dropdown
const categoryCell = worksheet.getCell('A1');
categoryCell.dataValidation = {
type: 'list',
formula1: categories.join(','),
allowBlank: true,
showErrorMessage: true,
errorTitle: 'Invalid category',
error: 'Please select a valid category from the list.'
};

// For the second dropdown, set up data validation with dynamic range
worksheet.getCell('B1').dataValidation = {
type: 'list',
allowBlank: true,
showErrorMessage: true,
errorTitle: 'Invalid selection',
error: 'Please select a valid item from the list.',
formula1: =INDIRECT(IF($A$1="Fruit", "C2:C4", "D2:D4"))
};

// Populate options for fruits and vegetables
worksheet.getCell('C1').value = "Fruits";
fruits.forEach((fruit, index) => {
worksheet.getCell(C${index + 2}).value = fruit;
});

worksheet.getCell('D1').value = "Vegetables";
vegetables.forEach((vegetable, index) => {
worksheet.getCell(D${index + 2}).value = vegetable;
});

workbook.xlsx.writeFile('conditional_dropdown.xlsx')
.then(() => {
console.log('Workbook created successfully!');
})
.catch((error) => {
console.error('Error:', error);
});

please help me
thanks