Create Radar Chart using Data from a different tab

[jennyblack9]

Hi,

I need to create a radar chart in my excel sheet that uses data from a different tab.

Tab "Ph 1 & Ph 2" has data for the qualification percentage of people. This tab has a marcos that will "Add New Person". I need to reference the data in the % score for "Evaluation". Person 1's % Evaluation is in cell J6, Person 2's % Evaluation is in cell O6, etc. The % Evaluation will only show up if there is content in the column next to the "Name:" cell (I8, N8, etc).

Anyways, the radar chart needs to use the % Evaluation number (J6, O6, etc) only if there is a person's name listed in I8, N8, etc).

I have attached the Excel sheet for reference.


Thank you!

[ben_hensel]

On the 'Graphical Representation' tab:

A3 = OFFSET('Ph 1 & Ph 2'!$I$8, 0, (ROW()-ROW($A$3))*5, 1,1)
B3 = OFFSET('Ph 1 & Ph 2'!$J$6, 0, (ROW()-ROW($A$3))*5, 1,1)

Does that do what you want?

Well, this seems like an unintuitive use of a radar chart, though.

[jennyblack9]

yes this works great thank you! I just had to edit the data selection.

Is there any way to also include the data from "Ph 3 & 4" tab in the same data column for the same chart?

[jennyblack9]

Also, I only want it to graph if there is a name - any way to have a blank vs. 0 when the formula is used?

Thank you.

[ben_hensel]

First: unless you re-organize your data to have a less awful format, the only solutions are going to be ugly-looking hacks, not anything simple or easy.

In particular, I think you need to split the category descriptions, and the assessment data, out into separate tables or something, because smacking them up against each other like this is an awful-looking mess.

Is there any way to also include the data from "Ph 3 & 4" tab in the same data column for the same chart?

Include how? you could slap another test range from like 26-50 or something on the bottom, or maybe a second array of data if it's looking for 1&2 and 3&4 matching on the same person.

Also, I only want it to graph if there is a name - any way to have a blank vs. 0 when the formula is used?

It delivers zero by default.

If you want the chart to ignore it, rather than charting the cero, you'd probably need to wrap an IF tester to return an error code instead if you want the chart to not even include zeros in the range.