Turning User input into a table to analyse

[ashl]

I want to turn the user input attached in this example into a table (data tab) so that i can then use it to analyse and possibly eventually turn into a dashboard for other users to drill into.


The way the user input is designed is best for the user to visualise but I am having problems figuring out how to get the data in the blue square boxes into a table format. I think formulas such as index match or offset match would work but it is a bit out of my league without seeing how it works (I'm excellent at copying and understanding formulas when I see them function in a spreadsheet).


Is anyone out there able to help me please or refer me to a book/website


Thanks


https://www.box.com/s/ecb61e9fca2f9c20f4bf

 

[Hui]

Ashl


I would look to rearrange the data with more fields in Columns and a single row for each record, where at present you have multiple rows and other odd cells for each record

In your example this will require 5 rows for Banana etc


Once it is in a straight forward table you can rearrange it via formulas to suit yourself

 

[ashl]

Hi Hui thanks for replying so quickly.


I was thinking that, the only problem is that the user would have to enter the same data for example hazard group and hazard multiple times if it had more than one cause, consequence or outcome and I was hoping to spare the user that and spare myself from having to type the same data into a seperate table in another worksheet - or is there something that I'm missing.


Could u give me a hint please as to what formulas I would use to rearrange it with sorry i'm a bit out of my league.


Thanks

 

[Hui]

I assume that not every record has 5 rows of data and so I would rearrange the data manually


When entering data you can use Ctrl D to duplicate values in the cell above