Author Topic: Excel Questions  (Read 39687 times)

andoireabu

  • Hero Member
  • *****
  • Posts: 764
    • View Profile
Re: Excel Questions
« Reply #405 on: April 23, 2020, 03:02:17 PM »
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.
Private Cowboy: Don't shit me, man!
Private Joker: I wouldn't shit you. You're my favorite turd!

Maiden1

  • Hero Member
  • *****
  • Posts: 864
    • View Profile
Re: Excel Questions
« Reply #406 on: April 23, 2020, 10:27:03 PM »
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)
« Last Edit: April 23, 2020, 10:35:45 PM by Maiden1 »
There are no proofs, only opinions.

johnnycool

  • Hero Member
  • *****
  • Posts: 2592
    • View Profile
Re: Excel Questions
« Reply #407 on: October 05, 2020, 02:37:00 PM »
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

  • Hero Member
  • *****
  • Posts: 12056
    • View Profile
Re: Excel Questions
« Reply #408 on: October 05, 2020, 03:04:30 PM »
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

  • Hero Member
  • *****
  • Posts: 15074
    • View Profile
Re: Excel Questions
« Reply #409 on: April 16, 2021, 05:55:30 PM »
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

  • Hero Member
  • *****
  • Posts: 778
    • View Profile
Re: Excel Questions
« Reply #410 on: April 16, 2021, 07:12:12 PM »
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

  • Hero Member
  • *****
  • Posts: 14959
    • View Profile
Re: Excel Questions
« Reply #411 on: April 16, 2021, 08:33:25 PM »
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.
If at first you don't succeed, then goto Plan B

Tony Baloney

  • Hero Member
  • *****
  • Posts: 15074
    • View Profile
Re: Excel Questions
« Reply #412 on: April 17, 2021, 09:21:35 AM »
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

  • Hero Member
  • *****
  • Posts: 25593
    • View Profile
Re: Excel Questions
« Reply #413 on: April 18, 2021, 08:02:18 AM »
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?.
Lookit