1

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.

B0ggsB1nny
  • 11
  • 1

1 Answers1

2

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.

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • If that resolved your issue, please mark the answer as described in the [tour]. If it didn't, please leave a comment, so I can follow up. – teylyn Jun 16 '23 at 02:02
  • Worked like magic :) thank you – B0ggsB1nny Jun 21 '23 at 20:00
  • Thanks for the feedback. If would be great if you could finish the paperwork and mark the answer as described in the [tour]. That is how you can say 'thank you' around here. – teylyn Jun 22 '23 at 05:44