#### auroralily

##### New Member

- Joined
- Sep 13, 2014

- Messages
- 24

I want to use excel to plot a graph of student grades but the grades are alphanumeric or strings so excel won't plot as is.

I have create 2 sheets as so:

Sheet 1

[TABLE="width: 935"]

<colgroup><col><col><col><col><col span="7"><col span="5"><col></colgroup><tbody>[TR]

[TD][TABLE="width: 500"]

<tbody>[TR]

[TD]Student[/TD]

[TD]Y7 PC3[/TD]

[TD]Y8 PC1[/TD]

[TD]Y8 PC2[/TD]

[TD]Y8 PC3[/TD]

[TD]Y9 PC1[/TD]

[TD]Y9 PC2[/TD]

[/TR]

[TR]

[TD][/TD]

[TD]btn[/TD]

[TD]btn[/TD]

[TD]btn[/TD]

[TD]btn[/TD]

[TD]btn[/TD]

[TD]btn[/TD]

[/TR]

[TR]

[TD][/TD]

[TD]5H[/TD]

[TD]6L[/TD]

[TD]6S[/TD]

[TD]6H[/TD]

[TD]7L[/TD]

[TD]7S[/TD]

[/TR]

</tbody>[/TABLE]

Sheet 2

[TABLE="width: 500"]

<tbody>[TR]

[TD]Student[/TD]

[TD]Y7 PC3[/TD]

[TD]Y8 PC1[/TD]

[TD]Y8 PC2[/TD]

[TD]Y8 PC3[/TD]

[TD]Y9 PC1[/TD]

[TD]Y9 PC2[/TD]

[/TR]

[TR]

[TD][/TD]

[TD]13[/TD]

[TD]14[/TD]

[TD]15[/TD]

[TD]16[/TD]

[TD]17[/TD]

[TD]18[/TD]

[/TR]

</tbody>[/TABLE]

[/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[/TR]

</tbody>[/TABLE]

The cells in the first row contain the simple =Sheet1cell code.

The cells in the second row of sheet 2 will contain either nested if statements or a macro function to convert the grades from sheet 1 to a number in the same cell in sheet 2.

These examples contain the end of term target data that is generated at the beginning of the year. When the students take a test a column is inserted to the left and the data inputted.

What I want the spreadsheet to be able to do:

- I click the btn in a column on sheet 1 (eg the column with header Y8 PC2)
- A blank column is inserted in sheet 1 to the left of that column (eg to the left of column Y8 PC2)
- A column is also inserted in sheet 2 to the left of the equivalent column (eg to the left of column Y8 PC2 in sheet 2)
- The new column in sheet 2 needs have all the formulas copied from the column to the right (eg formulas from column Y8 PC2)

Is this possible and how would I go about coding it?

Thanks