Fill adjacent cell with number from text answer

Hello Mitch,

You could probably just use a simple nested IF statement dragged down in the adjacent column:

=IF(F1="Fatality",1,IF(F1="Major injury/ill-health",2,IF(F1="Serious injury",3,IF(F1="Minor injury",4,IF(F1="Damage only",5,IF(F1="N/A",6))))))

I've referenced Column F (starting at cell F1) in the formula so you'll need to change that to suit your requirement. You can use the same formula for all three sections. Just change the list names to suit.

I hope that this helps.

Cheerio,

vcoolio.

You're welcome Mitch. Glad I could help.

I think that if you stay with your current format of using category numbers, it should keep things fairly simple for you. So test it out at your leisure. You've got the idea now for the type of formula needed.

If you do come back with further queries, please ensure to start a new thread for them. This one is now marked as solved.

Cheerio,

vcoolio.

Hi vcoolio,

Yep, that works.

Only thing I need now is if the cell is blank, I don't want to return anything in the adjacent cell.

Cheers,

Mitch.

Also, I have another tab where I want to put graphs.

So, in my spreadsheet, the Cat No in column 'I' returns a number, I need to total count the times a particular number occurs and and put that total into another tab, for instance:

4+4+4+4 = (4x4's)

3= (1x3)

2 = (1x2's)

This then needs to go into the Totals tab so I can create a graph, for example:

Hello Mitch,

The formula adjusted as follows will leave the relevant cells blank:

=IF(F1="","",IF(F1="Fatality",1,IF(F1="Major injury/ill-health",2,IF(F1="Serious injury",3,IF(F1="Minor injury",4,IF(F1="Damage only",5,IF(F1="N/A",6)))))))

Again change the cell references to suit.

As for the count of values for the graph, place the following formula into cell B5(I think that's the correct cell as I can't really tell from the image that you supplied) of the Totals sheet:

=COUNTIF(Sheet1!I:I,1)

in C5

=COUNTIF(Sheet1!I:I,2)

in D5

=COUNTIF(Sheet1!I:I,3)

etc., etc, etc....

You may have to change the sheet reference to suit. I've used the Sheet code (Sheet1) for the sake of the exercise.

I hope that this helps.

Cheerio,

vcoolio.

Almost there.

The issue no is that it counts all years as one total.

I need to split it into years for the graph to work.

Bloody nightmare :)

Hello Mitch,

I'm not sure what you mean. Where are the years coming from? The COUNTIF formulae relate to Column I only in your data entry sheet.

Cheerio,

vcoolio.

It comes from Column 1 

But I had forgotten about that.

Hello Mitch,

Could you upload a sample of your workbook to a file sharing site, such as WeTransfer or Drop Box, and then post the link to your file back here. Make sure that the sample is an exact replica of your actual workbook in all aspects and if your data is sensitive, then please use dummy data. We can't work or test with an image.

Also, please fully explain how you need this to work (inputs and expected results).

Cheerio,

vcoolio.

Hello Mitch,

While we await your sample workbook, I'll take a stab at the formula that you may need based on the images that you've supplied.

As far as I can tell, you place the year in A5 of the Totals sheet. Hence, this could work:

In B5:

=COUNTIFS(Sheet1!I1:I24,1,Sheet1!A1:A24,A5)  

In C5:

=COUNTIFS(Sheet1!I1:I24,2,Sheet1!A1:A24,A5)

In D5:

=COUNTIFS(Sheet1!I1:I24,3,Sheet1!A1:A24,A5)

etc., etc., etc.........

You'll need to change the ranges to suit (i.e. I1:I24 and A1:A24).      

The formula references whatever year that you place in A5 of the Totals sheet. 

I hope that this helps.

Cheerio,

vcoolio.

Sorry for the late reply.

How do I attach the spreadsheet, I can't find a way.

Thanks,

Mitch.

https://docs.google.com/spreadsheets/d/1c5O4zHfhOjuw9w-fqe9oeTTYrg4xGDQj/edit?usp=drive_link&ouid=114432463184329575150&rtpof=true&sd=true

Does that work?

The chart is on the 'Totals' tab. 

I think you will see what I am trying to achieve.

Hello Mitch,

That link doesn't work. Try WeTransfer who allow you free uploads with links that are valid for a week.

BTW, have you read my post #9?

Cheerio,

vcoolio.

what's the best email to send a wetransfer file to?

I did read the post but didn't really understand it.

You won't need an email address for a free transfer.

After you've uploaded your file, go down to the three dots (...) and select "Get transfer link". Click on the "Get a link button". You'll be given a link code and and be asked to copy the link. Once copied, just post the link in your next reply.

Cheerio,

vcoolio.

https://wetransfer.com/downloads/ad5fd75d3913083640b54290e93ea81020230825094147/27b579

Hello Mitch,

Here's the link to your file with the formulae implemented under the categories beside the chart in the Totals sheet.

Please note that in the ACCIDENTS-INJURY sheet Column O, I've used the formula =YEAR($A4) dragged down to extract the year from the Date column (Column A). Hence Column O is now a helper column. As you add more data below the existing, make sure to drag the formula down further to suit. Later versions of Excel may possibly "spill" the formula for you.

Click on any cell beside the years (A5:A7) in the Totals sheet to see the formulae that extract the required data for the charts.

An example of the formulae, just in case anyone else comes by this way looking for a similar solution, is:

=COUNTIFS('ACCIDENTS-INJURY'!$I$4:$I$74,1,'ACCIDENTS-INJURY'!$O$4:$O$74,Totals!$A$5)

or

=COUNTIFS('ACCIDENTS-INJURY'!$I$4:$I$74,2,'ACCIDENTS-INJURY'!$O$4:$O$74,Totals!$A$7)

or

=COUNTIFS('ACCIDENTS-INJURY'!$I$4:$I$74,3,'ACCIDENTS-INJURY'!$O$4:$O$74,Totals!$A$6)

or

=COUNTIFS('ACCIDENTS-INJURY'!$I$4:$I$74,4,'ACCIDENTS-INJURY'!$O$4:$O$74,Totals!$A$5)

or

=COUNTIFS('ACCIDENTS-INJURY'!$I$4:$I$74,5,'ACCIDENTS-INJURY'!$O$4:$O$74,Totals!$A$7)

You can see the category numbers in black font within the formulae.

Please note that the file link will only be valid for one week.

I hope that this helps.

Cheerio,

vcoolio.

Yep, I see that formula in Column O.

Thanks a lot for sorting this out, it's much appreciated.

I think I may be back, to sort out graphs for Incidents, Non-conformances and vehicle incidents once I can decide what the output should look like.

Thanks again,

Mitch. 

BTW Mitch, you may want to add a few more rows to the COUNTIFS formulae just to cover a larger data set.

Hence,for example, where you see $I$4:$I$74, change the 74 to, say 500 (or whatever you like). Do the same for the Column O references.

Cheerio,

vcoolio.