So - 01/01/2019 then 30/60 days later the next box would be highlighted in red and showing "DUE-30 Days".

Thanks for you're help

Column1 | Home | Column2 | Away | Total Goals |

Apr 1, 7:00pm | Chambly | 6-2 | Belfort | 8 |

Apr 1, 7:00pm | Bourg-en-Bresse | 3 - 0 | Ajaccio | 3 |

Apr 1, 7:00pm | Créteil | 0 - 3 | Clermont | 3 |

Apr 1, 7:00pm | Tours | 2-1 | Brest | 3 |

Apr 1, 7:00pm | Lens | 3 - 0 | Auxerre | 3 |

Apr 1, 7:00pm | Colmar | 1-1 | Dunkerque | 3 |

Apr 1, 7:00pm | Consolat Marseille | 0 - 3 | Avranches | 3 |

Apr 1, 7:00pm | Luçon | 0 - 3 | Amiens SC | 3 |

Apr 1, 7:00pm | Le Havre | 2 - 0 | Laval | 2 |

In the table above I am looking to highlight the teams whose matches have scored more than 3 goals. The only teams this would apply to would be Chambly and Belfort.

What is the best formula to use - basically I need something that goes likes

There is probably a very simple way of doing this apologies if so!

DESCRIPTION:

Hi, I need some help to read number and output to a summary table with multiple criterion using an excel formula. It is easily done using pivot tables but my audience is not tech savvy and need a formula to extract the values from Tab1.

Tab 1: Raw data with information

Tab 2: Example of dummy data. Basically, need to read text from column C, D, E in Raw Data

Tab 1 based on 2 criterion (Pillar and Group - columns A, B in Tab 1) and then spit out results in Tab 2 - Desired output. Note that in some cases there is more than one date/value to extract out based on the criterion.

Criteria should be based on Column A and B (Group and Pillar) ....

and when the criteria meets, it may or may not have more than 1 value (for example: Group 1, Pillar 1 might have 2 dates/values in column C for example. so I have to pull both dates in my summary table.

Please see below the data and excel file ("example.xlsx"). Also attached.

Thank you!

Hi, I need some help to read number and output to a summary table with multiple criterion using an excel formula. It is easily done using pivot tables but my audience is not tech savvy and need a formula to extract the values from Tab1.

Tab 1: Raw data with information

Tab 2: Example of dummy data. Basically, need to read text from column C, D, E in Raw Data

Tab 1 based on 2 criterion (Pillar and Group - columns A, B in Tab 1) and then spit out results in Tab 2 - Desired output. Note that in some cases there is more than one date/value to extract out based on the criterion.

Criteria should be based on Column A and B (Group and Pillar) ....

and when the criteria meets, it may or may not have more than 1 value (for example: Group 1, Pillar 1 might have 2 dates/values in column C for example. so I have to pull both dates in my summary table.

Please see below the data and excel file ("example.xlsx"). Also attached.

Thank you!

Hi. I need to create a formula where I say sum down a column until it finds the next "HEADER" entry in another column.

i.e.

]]>i.e.

HEADER | 5 |

Bob | 2 |

Jane | |

Peter | 3 |

Sue | |

HEADER | 1 |

Alan | |

Dave | 1 |

HEADER | |

I'd like to automatically divide the amount in a column over # of quarters (a parameter in column O) starting the quarter specified in column B without macro.

Note, the text specifying the quarters in column B matches the column headers in columns C through M.

Screenshot is provided below and the excel file is attached. Currently I have just divided by 4 manually to show as an example.

I'd appreciate any help you can provide. Thanks, Nick.

Note, the text specifying the quarters in column B matches the column headers in columns C through M.

Screenshot is provided below and the excel file is attached. Currently I have just divided by 4 manually to show as an example.

I'd appreciate any help you can provide. Thanks, Nick.

Hi

In the attached file you have yellow and green boxes.

I need the totals from the green and yellow boxes to be updated in their relevant boxes on the right.

I might have to change the number of green and yellow boxes as I progress through the year.

Hope someone can help.

Thanks

In the attached file you have yellow and green boxes.

I need the totals from the green and yellow boxes to be updated in their relevant boxes on the right.

I might have to change the number of green and yellow boxes as I progress through the year.

Hope someone can help.

Thanks

Hi folks,

I am trying to create this workbook project. Having spent some time researching a solution to this, I 'enjoy' the power and use of spreadsheets but as a novice, when it comes to this level, I'm feeling slightly overwhelmed by all the potential functions and syntax etc.. I'm also approaching an imminent 'window of opportunity' to present this to our department's Mgmt team, so time isn't on my side..

I'm not expecting anyone to provide me with an entire solution (while that would be awesome) but would be grateful if I could be pointed in the right direction as to the best way to build this e.g. which functions and syntax examples I should be focusing on etc..

Some project background….

We have a large, very mixed, fleet of industrial equipment all requiring frequent planned maintenance. I am trying to create a sort of dedicated in-house, quick look up, 'cheat sheet' for the common maintenance items required for each piece of equipment.

The equipment's fleet numbers are the 'second language' of the company operation, so predominantly everything is geared around this number. Alternatively, the 'serial number prefix' (aka spec. code) of said equipment is also commonly referenced.

While each fleet# is unique, some have the same spec code, thereby using the same maintenance items. Furthermore, a spec code can occasionally use some of the same items as another spec code.

I have already built lists of all the common items used for each spec code, one per worksheet, each labelled by the spec code prefix.

My objective…

Ideally I would like to create a 'front end' summary sheet whereby, when a user keys in a fleet# *or* s/n prefix into dedicated cells in that sheet, it will auto populate that sheet with all the part numbers and descriptions, required only for that particular piece of equipment, 'pulled' from within the corresponding data associated to that equipment, within the s/n prefix sheets which I have already built. This sheet could then be printed to pdf to submit as an order for the items.

Naturally I could restructure the data in these sheets according to any instructions and guidance I am given to make this work. (e.g. move all data to one sheet and create named ranges?). I also want to protect all the 'back-end' data sheet(s) from being altered in any way.

Once created, I would save the workbook to our company department's OneDrive share, then share it from/via Excel Online (and possibly GSheets down the road).

I have attached a smaller sample of the sheet as it currently stands. Any guidance would be much appreciated as well as helping me to learn more using real world data. A feather in my employment cap wouldn't go amiss either :)

Thanks for taking the time to read!

Brian

I am trying to create this workbook project. Having spent some time researching a solution to this, I 'enjoy' the power and use of spreadsheets but as a novice, when it comes to this level, I'm feeling slightly overwhelmed by all the potential functions and syntax etc.. I'm also approaching an imminent 'window of opportunity' to present this to our department's Mgmt team, so time isn't on my side..

I'm not expecting anyone to provide me with an entire solution (while that would be awesome) but would be grateful if I could be pointed in the right direction as to the best way to build this e.g. which functions and syntax examples I should be focusing on etc..

Some project background….

We have a large, very mixed, fleet of industrial equipment all requiring frequent planned maintenance. I am trying to create a sort of dedicated in-house, quick look up, 'cheat sheet' for the common maintenance items required for each piece of equipment.

The equipment's fleet numbers are the 'second language' of the company operation, so predominantly everything is geared around this number. Alternatively, the 'serial number prefix' (aka spec. code) of said equipment is also commonly referenced.

While each fleet# is unique, some have the same spec code, thereby using the same maintenance items. Furthermore, a spec code can occasionally use some of the same items as another spec code.

I have already built lists of all the common items used for each spec code, one per worksheet, each labelled by the spec code prefix.

My objective…

Ideally I would like to create a 'front end' summary sheet whereby, when a user keys in a fleet# *or* s/n prefix into dedicated cells in that sheet, it will auto populate that sheet with all the part numbers and descriptions, required only for that particular piece of equipment, 'pulled' from within the corresponding data associated to that equipment, within the s/n prefix sheets which I have already built. This sheet could then be printed to pdf to submit as an order for the items.

Naturally I could restructure the data in these sheets according to any instructions and guidance I am given to make this work. (e.g. move all data to one sheet and create named ranges?). I also want to protect all the 'back-end' data sheet(s) from being altered in any way.

Once created, I would save the workbook to our company department's OneDrive share, then share it from/via Excel Online (and possibly GSheets down the road).

I have attached a smaller sample of the sheet as it currently stands. Any guidance would be much appreciated as well as helping me to learn more using real world data. A feather in my employment cap wouldn't go amiss either :)

Thanks for taking the time to read!

Brian

I want to populate column E (Answer) with the results of multiplying column C (Factor) by column D (Input), rows 5-16, as per attached file.

Column D now shows a 10 at row 8 so the 10 should be multiplied by each factor of column C and the answers should be shown in column E. Then when I input another number anywhere in column D that number should again be multiplied by each factor from column C. There is no need to save the results in column E

The formula shown on the same sheet which for some unexplained reason when I write it on the VB Editor does not stay there after I exit the file although I save it. This formula I found it on the web but it does not work, perhaps I’m missing something?

Thank you

Column D now shows a 10 at row 8 so the 10 should be multiplied by each factor of column C and the answers should be shown in column E. Then when I input another number anywhere in column D that number should again be multiplied by each factor from column C. There is no need to save the results in column E

The formula shown on the same sheet which for some unexplained reason when I write it on the VB Editor does not stay there after I exit the file although I save it. This formula I found it on the web but it does not work, perhaps I’m missing something?

Thank you

I am trying to look at a cell value, find it in a A and B column of range.

A1=24

a2=10 B2-19 c2=A

a3=20 B3= 29 B3=B

A4=30 b4=39 c4=C

D1= the answer

I want to look up A1, find which range it is, and return whatever in in the corresponding C cell. In this example, a "B" would be returned to Cell D1.Compare Formula.xlsx

A1=24

a2=10 B2-19 c2=A

a3=20 B3= 29 B3=B

A4=30 b4=39 c4=C

D1= the answer

I want to look up A1, find which range it is, and return whatever in in the corresponding C cell. In this example, a "B" would be returned to Cell D1.Compare Formula.xlsx

Are there any ways to rank this to show all 65 as 1, all 7 as 2 and all 6 as 3 etc...?

Thanks in advance

]]>Thanks in advance

65 |

65 |

65 |

65 |

7 |

7 |

7 |

7 |

7 |

7 |

7 |

6 |

6 |

6 |

6 |

6 |

6 |

6 |

6 |

6 |

6 |

G'day,

Very much an Excel novice, but i am learning. I'm a bit stuck on this one though. It's for a group of mates who go in a footy pools comp each year. Each player is allocated at least 3 numbers, if if those numbers coincide with the winning score of a footy game that week, they get $10, There are 9 games a week, and 23 rounds in a season. I have been able to get a single cell from a single match to work, but I want to multiple several matches if possible.

For example, Player A is allocated 10, 20 and 30. If one of the matches is a 10 point result, then great, he wins $10. If in three of the games though, the result is 10 points, 20 points and 30 points, he gets $30. I want the cell to reflect that total amount without a ton of hidden cells etc. I the attachment we have:

**Sheet 1 - BUSHFOOTY**

Column A - List of players

Column B to F - the allocated numbers

Column H - Round 1 (Rd1)

Column I - Round 2 (Rd2)

Column J - Round 3 (Rd 3)

**Sheet 2 - Rd1**

Column A - the matches

Column B - the results

As an example I have done =IF(COUNTIF(B3:F3,'RD1'!B2),10,"") in Cell H3, but that was sort of guessing. It only deals with one match though. I'd like it to calculate all 9 matches each week

Any help would be greatly appreciated

Cheers

DH

Very much an Excel novice, but i am learning. I'm a bit stuck on this one though. It's for a group of mates who go in a footy pools comp each year. Each player is allocated at least 3 numbers, if if those numbers coincide with the winning score of a footy game that week, they get $10, There are 9 games a week, and 23 rounds in a season. I have been able to get a single cell from a single match to work, but I want to multiple several matches if possible.

For example, Player A is allocated 10, 20 and 30. If one of the matches is a 10 point result, then great, he wins $10. If in three of the games though, the result is 10 points, 20 points and 30 points, he gets $30. I want the cell to reflect that total amount without a ton of hidden cells etc. I the attachment we have:

Column A - List of players

Column B to F - the allocated numbers

Column H - Round 1 (Rd1)

Column I - Round 2 (Rd2)

Column J - Round 3 (Rd 3)

Column A - the matches

Column B - the results

As an example I have done =IF(COUNTIF(B3:F3,'RD1'!B2),10,"") in Cell H3, but that was sort of guessing. It only deals with one match though. I'd like it to calculate all 9 matches each week

Any help would be greatly appreciated

Cheers

DH

Hello everyone!

I'm hoping you can help me with this.

**Problem:**

In Column A I have multiple strings, for example:

1. 20D Fun 0L

2. 10D Fun 0L

3. 0L Fun 20D

As you can see, 1. and 2. have the same information.

Solution:

Ideally I would like the formula to search Column A and find cells which have 3 matching strings. In Column B, the result should output the matching cell. For example, in the data I presented, the result for 1. would be 3.

I appreciate any help.

]]>I'm hoping you can help me with this.

In Column A I have multiple strings, for example:

1. 20D Fun 0L

2. 10D Fun 0L

3. 0L Fun 20D

As you can see, 1. and 2. have the same information.

Solution:

Ideally I would like the formula to search Column A and find cells which have 3 matching strings. In Column B, the result should output the matching cell. For example, in the data I presented, the result for 1. would be 3.

I appreciate any help.

Say I have a workbook with 3 sheets shown as below:

**Sheet1**

**Sheet2**

**Sheet3**

Now, I want to fill the missing data in sheet2 from sheet1 using vlookup. I've tried the below formula after selecting the blank cells in sheet2

and it gets the job done but it does not work for sheet3.

How can I do that? Can I use**COLUMNS()** formula instead of **COLUMN()** in some way

BTW, I was asked this in an interview.

Now, I want to fill the missing data in sheet2 from sheet1 using vlookup. I've tried the below formula after selecting the blank cells in sheet2

Code:

`=VLOOKUP(Sheet1!$A$2:$A$51,Sheet1!$A$2:$F$51,COLUMN(),0)`

How can I do that? Can I use

BTW, I was asked this in an interview.

Use the IF function to complete the "Comments" column of table 1. Display "Good Job" if both the "Hours Worked" are less than or equal to the "Estimated Hours" for a project and the assessed "Quality" of that project is greater than 1. Display "Too Much Time" if the "Hours Worked" on a project exceed the "Estimated Hours" for that project; otherwise, display "Poor Quality."

]]>Hi

I've attached a sample data, on the '**Data**' tab, which shows in each cell a person's name, in short form or code, and a number, in** bold**, which denotes a placing number for each pairing of names.

Beside each pairing, highlighted in pink, shown on the '**Pairs**' tab, I wanted a formula to show the total number of times that they came **1**=1st,** 2**=2nd, **3**=3rd only, ignoring the **0**=no placings.

Just so you know, each person's name for each placing is listed with their own unique code on the '**Codes**' tab which I would like to be incorporated in the formula. The reason being is that the listed names on the attached sample data only shows a small list of names and the formula(s) will eventually be used in a data with a vast number of names being approximately over 300 names.

So I guess using**Countif** formula would not be suitable for this vast data.

I hope this all makes sense and let me know if you have any queries about the attached data.

Kind regards

Chi

I've attached a sample data, on the '

Beside each pairing, highlighted in pink, shown on the '

Just so you know, each person's name for each placing is listed with their own unique code on the '

So I guess using

I hope this all makes sense and let me know if you have any queries about the attached data.

Kind regards

Chi