Excel Questions

[andoireabu]

Problem:

In cell F9 I have a date that I would like to change dependent on the value in another cell.  That cell will be in the range I9:JR9 and will contain the number 1.  In row 6 there are dates in sequential order in cells I6:JR6.  So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9.  If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.

Near solution:

=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))

This works if I manually type in the number but not when I drag it left to right.

Any ideas?

If I understand what you are trying to do right, your formula worked for me... By drag it left to right I assume you mean there is still only one "1" entry in row 9 rather than copying?

Did you try fully fixing the ranges?

=INDEX($I$6:$JR$6, MATCH(1,$J$9:$JR$9,0))

Aye there will only ever be one number 1 in the row.  Even when I add the extra $ signs, as soon as I drag the cell to one side the formula changes in F9.

Think I have a work around with HLOOKUP instead that seems to be doing what I want it to.

[Maiden1]

Problem:

In cell F9 I have a date that I would like to change dependent on the value in another cell.  That cell will be in the range I9:JR9 and will contain the number 1.  In row 6 there are dates in sequential order in cells I6:JR6.  So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9.  If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.

Near solution:

=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))

This works if I manually type in the number but not when I drag it left to right.

Any ideas?

If I understand what you are trying to do right, your formula worked for me... By drag it left to right I assume you mean there is still only one "1" entry in row 9 rather than copying?

Did you try fully fixing the ranges?

=INDEX($I$6:$JR$6, MATCH(1,$J$9:$JR$9,0))

Aye there will only ever be one number 1 in the row.  Even when I add the extra $ signs, as soon as I drag the cell to one side the formula changes in F9.

Think I have a work around with HLOOKUP instead that seems to be doing what I want it to.

=LOOKUP(1,$I$9:$JR$10,$I$6:$JR$6)

Should work

HLOOKUP will work if you put the 1 above the date as it does the lookup for the match on the highest row

=HLOOKUP(1,$I$5:$JR$6,2,FALSE)

[johnnycool]

any excel experts on here should be on the phone to Dido Harding and Serco offering their services to their world class test and trace system which they've based on an excel database.........

"Public Health England's interim chief executive Michael Brodie said a "technical issue" was identified overnight on Friday, 2 October in the process that transfers Covid-19 positive lab results into reporting dashboards. He said the majority of the unreported cases had occurred in the "most recent days".

It was caused by some data files reporting positive test results exceeding the maximum file size.

Mr Brodie said they worked with NHS Test and Trace to "quickly resolve the issue and transferred all outstanding cases immediately into the NHS Test and Trace contact tracing system".

"We fully understand the concern this may cause and further robust measures have been put in place as a result," he said."


https://www.bbc.co.uk/news/uk-54412581

[imtommygunn]

that would be funny except how much would have been spent on that? this was/is not a difficult piece of software to engineer and that is a schoolboy mistake for a "live" application.

[Tony Baloney]

Probably a basic enough question for anyone who knows what they are doing but I want to compare cell colours and highlight mismatches.
I've combined the output from 2 risk assessments onto one sheet however
I dont want to change the cell values as Column C was performed using one methodology and Column D by another. Where they haven't reached the same conclusion I want to highlight those rows and then discuss with the people involved.

Column B is the item under assessment, Column C is Risk Assessment Type 1 which is conditional formatted where H = Red, M = Amber and L = Green. Column D is Risk Assessment Type 2 but based on numbers i.e.>8 = Red, 5-7 = Amber and 2-4 = Green.

[An Watcher]

Not 100% if this is what you need Baloney but how about forgetting the colours and creating a further column based on the values in B and C.  For example, High in Column B and 10 in C could be an A value, High in Column B and 9 in C could be a B value and so on and so forth until eventually you'll have all your values however anything that doesn't fit in ie High and 1 wouldn't have a value applied and you could sort it based on this.  Don't know if that makes sense!!!!

[armaghniac]

Not 100% if this is what you need Baloney but how about forgetting the colours and creating a further column based on the values in B and C.  For example, High in Column B and 10 in C could be an A value, High in Column B and 9 in C could be a B value and so on and so forth until eventually you'll have all your values however anything that doesn't fit in ie High and 1 wouldn't have a value applied and you could sort it based on this.  Don't know if that makes sense!!!!

There doesn't seem to be anyway of using a function to identify the colour of cell, so you can either do as Watcher suggests and access the original data or else do a custom function in VB.

[Tony Baloney]

Not 100% if this is what you need Baloney but how about forgetting the colours and creating a further column based on the values in B and C.  For example, High in Column B and 10 in C could be an A value, High in Column B and 9 in C could be a B value and so on and so forth until eventually you'll have all your values however anything that doesn't fit in ie High and 1 wouldn't have a value applied and you could sort it based on this.  Don't know if that makes sense!!!!

There doesn't seem to be anyway of using a function to identify the colour of cell, so you can either do as Watcher suggests and access the original data or else do a custom function in VB.

Cheers lads I'll take a look at matching them. On Monday obviously!

[seafoid]

Probably a basic enough question for anyone who knows what they are doing but I want to compare cell colours and highlight mismatches.
I've combined the output from 2 risk assessments onto one sheet however
I dont want to change the cell values as Column C was performed using one methodology and Column D by another. Where they haven't reached the same conclusion I want to highlight those rows and then discuss with the people involved.

Column B is the item under assessment, Column C is Risk Assessment Type 1 which is conditional formatted where H = Red, M = Amber and L = Green. Column D is Risk Assessment Type 2 but based on numbers i.e.>8 = Red, 5-7 = Amber and 2-4 = Green.

The problem seems to be that you can't play atound with the colours cokumn. Could you set up another cokumn and apply the same colour scheme to.Column D so the 2 are on the same basis?.