Can INDIRECT() be used to reference a Defined Name in a formula

Hi MikeH_944,

Thanks for contacting us, I'm happy to help you with your Excel formula.

Yes, you can use the INDIRECT() function to reference a Defined Name in a formula. The INDIRECT() function returns the reference specified by a text string, which can be a cell reference, a named range, or a formula that yields a valid cell reference.

In your case, you can use the INDIRECT() function to concatenate the text "QData" with the number that equals COLUMN()-2 of the cell containing the formula. For example, if the formula is in cell F5, then COLUMN()-2 will return 4, and the text string will be "QData4". The INDIRECT() function will then evaluate this text string as a reference to the named range QData4, which is Sheet1!$E$5:$E$39.

The final formula will look something like this:

=INDEX(INDIRECT("QData"&COLUMN()-2),1,1)

This formula will return the value in the first row and first column of the named range QData4, which is Sheet1!$E$5. You can copy this formula to other cells and it will automatically adjust the reference to the corresponding named range based on the column number.

Let me know if this helps or if you need further assistance.

Regards, Sola

“Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below. “

Kindly note that this is a user to user forum, we are users helping other users, we aren't Microsoft employee neither are we Microsoft agents.