IF statement to return a single digit based on the info in 5 cells

[Cemslie87]

Hi,

I'm afraid i've reached the limit of my understanding in Excel and in need of some help.

Basically i have a trend analysis that takes information from 20 worksheets (5 per week over 4 weeks) within a workbook. At the end of the week i have a weekly report that (stupidly) requires a different trend analysis that gets sent off to the bosses in the format they want it - not the better format we have, but i digress.

Long story short, I need the weekly report, more specifically a single cell to look at 5 cells within the trend analysis. If any of those 5 cells have a ' ü ' in it then the cell in the weekly is to show a 1 to denote this was achieved in week one.

I have managed to learn a lot to make my trend analysis show what i want it too based on the information in the 5 reports within a week. However, this one is testing my abilities and winning so far. What i have been using follows, but doesn't give me the true solution i need.

In Cell within weekly report
=IF('Trend Analysis'!$U7:$Y$7 = "ü", "1", "")

You'll notice that i'm using the wingding font for a (tick), similar to the problem above, I also cant figure out how to get the trend analysis to not insert the "ü" and insert a O. If anyone can help with that, you'll be an absolute star!

Thanks in advance for any hints or tips.

C

 

[PATSYS]

=IF(ISNUMBER(MATCH("ü",'Trend Analysis'!$U$7:$Y$7,0)),1,"")

 

[bobsan42]

Try this:

=IF(COUNTIF('Trend Analysis'!$U7:$Y$7, "ü")>0,"1", "")

For inserting the O - how do you insert ü in the first place?

 

[L. Howard]

Maybe this?

=IF(COUNTIF('Trend Analysis'!U7:Y7,"ü"),1,"")

Howard

 

[Cemslie87]

Try this:

=IF(COUNTIF('Trend Analysis'!$U7:$Y$7, "ü")>0,"1", "")

For inserting the O - how do you insert ü in the first place?

Its literally a direct "= cell blah, from sheet Bloggs" But because the font is a wingding it returns the ü. in the training reports the (tick) denotes that they are performing the task at standard. and on the trend analysis, i need the cell to produce a O denoting (Operational standard)

But to all of the replies, they all work. Thank you very much for your quick response.

If one of you wouldn't mind, i would like to learn further. In plain english (rather than Microsoft speak) can you break down everything your asking it to do so i may understand?

Thank you once again! saving me from a lot of stress!

 

[AliGW]

I'm not quite sure what you are asking, but in order to insert the ü character you need to hold down the ALT key and type 0252 on your numeric keypad (with numlock applied, of course).

 

[bobsan42]

My English is too plain, it seems
I am totally lost - I cannot understand what you expect as a reply.
Could you elaborate with simple words?

 

[Cemslie87]

One final question, and thanks in advance;
I have 4 weekly reports, as stated above, inputs to these are usually cumulative, e.g. if I input a 1 in the cell in week one report, it must remain there throughout the 4 weeks worth of reports. This indicates that a trainee has completed this learning point on week 1 even on weeks 4 report.

Hopefully if i write a formula in plain english it might express my desire;

'In sheet two, cell A1' =the number in CellA1, sheet 1, UNLESS, IF(COUNTIF('Trend Analysis'!$U7:$Y$7, "ü")>0,"1", "")

basically unless the formula in sheet two produces a result then copy the weekly report prior.


The end result in week 4 will be 25+ cells that have sporadic numbers ranging through 1-4 to highlight where a trainee has achieved that learning point

I really hope this makes sense...


Thanks again folks!

 

[Cemslie87]

Basically so my weekly's may look like this;

Week 1
Training Objective
1.1 -
1.2 - 1
1.3 -

Week 2
Training Objective
1.1 - 2
1.2 - 1
1.3 -

Week 3
Training Objective
1.1 - 2
1.2 - 1
1.3 - 3

Hope this helps