How to use a function or variable when defining a separate cell

I am trying to create a dynamic pie chart with the following definition:

SERIES(,'2023'!$B$39:$B$46,'2023'!$AA$39:$AA$46,1)

Where $B$39:$B$46 is the legend range and $AA$39:$AA$46 is the data range.

My question is how to define $AA$39:$AA$46 using a function or value from another cell to make it dynamic depending on other inputs.

I have a function in cell A1, which converts the value in cell E33 to "AA." The function is:

=SUBSTITUTE(ADDRESS(1, (E33), 4), "1", "")

I want to use the output from this function in the range used for the pie chart data. Rather than $AA$39:$AA$46, I would like to use either cell A1 or the actual equation =SUBSTITUTE(ADDRESS(1, (E33), 4), "1", "") combined with the row range 39:46.

So, my question is whether it's possible to define a range as:

• $A1$39:$A1$46, or
• SUBSTITUTE(ADDRESS(1, (E33), 4), "1", "")$39:SUBSTITUTE(ADDRESS(1, (E33), 4), "1", "")$46

I may not have explained my situation well, but I would appreciate any help.

You won't be able to put this formula directly into the source definition for the chart. You can put this formula (or any other formula that delivers the desired range) into a range name (or named formula), using the Formulas Ribbon > Name Manager > New. Specify a name and enter the formula. Make sure to use absolute referencing with $ signs if you always want to use the value in cell E33.

Given that your result refers to column AA, I assume the value in cell E33 is 27. Instead of the complicated substitute formula, you can use Index() like this:

=INDEX(Sheet2!$39:$39,Sheet2!$E$33):INDEX(Sheet2!$45:$45,Sheet2!$E$33)

Put that formula into a range name and plug the range name into the chart data source as shown in the screenshot.