Fill adjacent cell with number from text answer

Solved/Closed
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023 - Jul 27, 2023 at 07:08 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 25, 2023 at 10:14 AM

Hello, and good morning,

I have a cell with a dropdown box, to select a certain text.

When I select the type of incident from a drop down box, I would like the adjacent cell to poulate with the number (eg Minor injury = 4)

I have to do it for the three blank columns 

Any help will be much appreciated.

Thanks,

Mitch.
Windows / Chrome 115.0.0.0

20 responses

vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jul 28, 2023 at 02:40 AM

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.

1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 25, 2023 at 07:44 AM

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.

1
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Updated on Aug 1, 2023 at 04:23 AM

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.

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 1, 2023 at 04:42 AM

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:

0

Didn't find the answer you are looking for?

Ask a question
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 1, 2023 at 06:46 AM

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.

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 7, 2023 at 03:56 AM

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 :)

0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 7, 2023 at 07:08 AM

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.

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 7, 2023 at 11:33 AM

It comes from Column 1 

But I had forgotten about that.

0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 8, 2023 at 03:01 AM

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.

0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 17, 2023 at 01:28 AM

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.

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 25, 2023 at 04:48 AM

Sorry for the late reply.

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

Thanks,

Mitch.

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 25, 2023 at 04:52 AM

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

Does that work?

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 25, 2023 at 04:53 AM

The chart is on the 'Totals' tab. 

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

0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 25, 2023 at 05:10 AM

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.

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 25, 2023 at 05:28 AM

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

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

0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 25, 2023 at 05:38 AM

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.

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 25, 2023 at 05:42 AM

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

0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 25, 2023 at 07:30 AM

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.

0
MitchP Posts 11 Registration date Thursday July 27, 2023 Status Member Last seen August 25, 2023
Aug 25, 2023 at 07:37 AM

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. 

0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 25, 2023 at 10:14 AM

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.

0