# Listing chart plot points horizontaly

Hi Experts,

I am presented data in the form of the first table (Input Limits) and I want to plot this data as shown in the chart.

To do this the data has to be converted into the plot points in the second table (x and y values).

Is there a way of writing a formula that would create this second table from the first? Or...

I know how to create the required plot points and list them horizontally next to the associated data (Input Limits) as in the third table but then the chart can not plot them. Is there a way to get this chart to plot with the plot points listed horizontally?

Thanks.
chart.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I think you will have to write quite a lot of macro code to achieve your objectives:

Microsoft MVP ExcelCommented:
Hello,

it's not quite clear how much you want to automate.  In the attached file you can find a grid that translates the data from the Input Limits (which I've transformed into an Excel Table with Insert > Table) to a grid that is used as the chart source.

You can add a new row of data to the Input Limits table. If you want to include that in the chart, copy the block of five rows and paste below. Specify which row of the Input Limits table to plot. Then update the data source for the chart to include the new rows.

See attached file.

cheers, teylyn
chart.xlsx
EngineerCommented:
If the given data is in A3:E5 and the list starts in A8 then paste this formula in A8 and copy it all the way down and one column to the right. You will have to delete the #NA# cells.

=IFERROR(INDEX(\$A\$3:\$E\$5,1+INT((ROW()-ROW(\$A\$8))/6),CHOOSE(MOD((ROW()-ROW(\$A\$8)),6)+(COLUMN()-1)*6+1,1,1,2,2,1,1/0,3,4,4,3,3,1/0)),NA())

If the locations are different the change the one instance of \$A\$3:\$E\$5 and two instances of \$A\$8

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

EngineerCommented:
To avoid deleting the #NA# cells,

Paste the formula in A8
Pull it to B8
Select A8:B8
Pull it down to B12
Select A8:B13
Pull it all the way down.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.