Hi. dear friends i have one worksheet. it has 11 sheets. in sheets 1 to 10 i have just 1 column wich has some values.

in sheet 11 wich i named it "allsheets" , i want to copy values of those other sheets (sheets 1 to 10) in 10 rows (first 10 rows for first values next 10 rows for second values and so on ....)

now can you help me what formula should i use ? maybe i should use VLOOKUP or MATCH functions but i dont know how to use them here. ( please see my attached worksheet ) thanks a lot

in sheet 11 wich i named it "allsheets" , i want to copy values of those other sheets (sheets 1 to 10) in 10 rows (first 10 rows for first values next 10 rows for second values and so on ....)

now can you help me what formula should i use ? maybe i should use VLOOKUP or MATCH functions but i dont know how to use them here. ( please see my attached worksheet ) thanks a lot

This is probably going to be an easy one.

I have read through several postings and tried to build a formula using SUMPRODUCT but its not working as expected. I am trying to sumproduct 2 columns based on the date value in a third column. I've tried using an embedded DATE, DATEVALUE and YEAR function but nothing seems to work right. I've attached an example of what I've tried so far. I'm trying to multiply the "Unit" column by the "Cost" column if the "Date" column value is from the year 2018. The output is not even close and i cant seem to get it to work right.

Thanks in advance for all the help!

I have read through several postings and tried to build a formula using SUMPRODUCT but its not working as expected. I am trying to sumproduct 2 columns based on the date value in a third column. I've tried using an embedded DATE, DATEVALUE and YEAR function but nothing seems to work right. I've attached an example of what I've tried so far. I'm trying to multiply the "Unit" column by the "Cost" column if the "Date" column value is from the year 2018. The output is not even close and i cant seem to get it to work right.

Thanks in advance for all the help!

I am attempting to reference another sheet within excel which i know is the function =sheet1!(cell)

But I wish to automate the possess using from a match function.

I would like it to look something like this = sheet1!$B(=MATCH(2, Sheet1!A1:A55, 0))

where the =MATCH(2, Sheet1!A1:A55, 0) is the row reference.

Is there any way to do this.

I have attached an example of what I have tried to do.

But I wish to automate the possess using from a match function.

I would like it to look something like this = sheet1!$B(=MATCH(2, Sheet1!A1:A55, 0))

where the =MATCH(2, Sheet1!A1:A55, 0) is the row reference.

Is there any way to do this.

I have attached an example of what I have tried to do.

Hi,

Had some help on this spreadsheet a while back but now its expanded.

So the problem is this

On the EM Ranking tab I need E3 to pull through the player numbers from the triallists tab.

The array formula on E3 currently reads as this

=IFERROR(INDEX(Trialists!$E$2:$E$44, SMALL(IF((E$2=Trialists!$B$2:$B$44)*(Trialists!$A$2:$A$44="Yes"), ROW(Trialists!$E$2:$E$44)-1,""), ROW()-2)),"")

which works fine.

The OR condition I need now that is considered E$2=Trialists!$B$2:$B$44 or (Trialists!$C$2:$C$44="Yes")

So the first one for E2 is whether B2:B44 = EM on the triallist tab

or

Whether C2:C44 = Yes (they wouldnt have EM associated with it)

I will still need (Trialists!$A$2:$A$44="Yes") in the formula

Hope that makes sense?

Thanks in advance

Andrew

Had some help on this spreadsheet a while back but now its expanded.

So the problem is this

On the EM Ranking tab I need E3 to pull through the player numbers from the triallists tab.

The array formula on E3 currently reads as this

=IFERROR(INDEX(Trialists!$E$2:$E$44, SMALL(IF((E$2=Trialists!$B$2:$B$44)*(Trialists!$A$2:$A$44="Yes"), ROW(Trialists!$E$2:$E$44)-1,""), ROW()-2)),"")

which works fine.

The OR condition I need now that is considered E$2=Trialists!$B$2:$B$44 or (Trialists!$C$2:$C$44="Yes")

So the first one for E2 is whether B2:B44 = EM on the triallist tab

or

Whether C2:C44 = Yes (they wouldnt have EM associated with it)

I will still need (Trialists!$A$2:$A$44="Yes") in the formula

Hope that makes sense?

Thanks in advance

Andrew

I've got some complicated conditional formatting to do. As I have software that adds data to one of the sheets I am only looking for a conditional formatting solution as to not brick the sheet for the future. Below I will explain whats on the sheet, what i'd like to have help with achieving and finally what I have thought about, tried and subsequently failed. To anybody reading this thanks.

Here I will describe whats on the workbook. To better understand you can look at the example sheet I have attached.

**Logs**This is the sheet I am afraid of editing. Operations are added here. Column B shows what operator has performed an operation. Column D shows what equipment said operator used.

**Equipment**This sheet I am not afraid of editing as long as additions are added from column S and beyond. Here is a list of equipment. Column A has the equipment identifier. Column R shows what the minimum driving license level that is required to use said equipment.

**Operators**This sheet I am not afraid of editing as long as additions are added from column J and beyond. Here is a list of operators. Column A has the ID of an operator. Column E shows what level license they have. Columns J-Q shows the license qualifications.

Here I will describe what I want to get done.

I want to add a conditional formatting which turns the cell red if the operator in question (column B) is not licensed to operate the equipment (column D). It is not as simple as performing a VLOOKUP on what the Operators license is and what the requirement for the equipment is. We need to keep in mind that a license might qualify you for several types of equipment.

Example 1: In row 4 in the "Logs" sheet we have "Alfons" which operated "Hyster E". "Alfons" has an "A4" license (shown in the "Operators" sheet) and "Hyster E" requires a minimum of a "B2" license (shown in the "Equipment" sheet). Then in the "Logs" sheet I would want the cell B4 ("Alfons") to be filled red as he operated equipment outside of his license range.

Example 2: In row 2 in the "Logs" sheet we have "Ashley" which operated "Hyster A". "Ashley" has a "B2" license (shown in the "Operators" sheet) and "Hyster A" requires a minimum of a "A3" license (shown in the "Equipment" sheet). Then in the "Logs" sheet I would want the cell B2 ("Ashley") to not be filled as she operated equipment within her license range. Although "Ashley" doesnt have an "A3" license but her "B2" license qualifies her for operating "A3" equipment.

What have I tried?

I have tried using VLOOKUP to find the required license for the equipment and then using a mix of HLOOKUP and VLOOKUP to find in the grid J-Q in the "Operators" sheet if the license is TRUE or FALSE. I couldnt make it work properly.

If you have read this far thank you very much for spending the time reading this. I hope it has been clear enough to understand with the excel attachment.

Here I will describe whats on the workbook. To better understand you can look at the example sheet I have attached.

Here I will describe what I want to get done.

I want to add a conditional formatting which turns the cell red if the operator in question (column B) is not licensed to operate the equipment (column D). It is not as simple as performing a VLOOKUP on what the Operators license is and what the requirement for the equipment is. We need to keep in mind that a license might qualify you for several types of equipment.

Example 1: In row 4 in the "Logs" sheet we have "Alfons" which operated "Hyster E". "Alfons" has an "A4" license (shown in the "Operators" sheet) and "Hyster E" requires a minimum of a "B2" license (shown in the "Equipment" sheet). Then in the "Logs" sheet I would want the cell B4 ("Alfons") to be filled red as he operated equipment outside of his license range.

Example 2: In row 2 in the "Logs" sheet we have "Ashley" which operated "Hyster A". "Ashley" has a "B2" license (shown in the "Operators" sheet) and "Hyster A" requires a minimum of a "A3" license (shown in the "Equipment" sheet). Then in the "Logs" sheet I would want the cell B2 ("Ashley") to not be filled as she operated equipment within her license range. Although "Ashley" doesnt have an "A3" license but her "B2" license qualifies her for operating "A3" equipment.

What have I tried?

I have tried using VLOOKUP to find the required license for the equipment and then using a mix of HLOOKUP and VLOOKUP to find in the grid J-Q in the "Operators" sheet if the license is TRUE or FALSE. I couldnt make it work properly.

If you have read this far thank you very much for spending the time reading this. I hope it has been clear enough to understand with the excel attachment.

Hi,

suppose my range in cells A1:A4 is 0,1,2,#DIV/0!

I would like to avoid a solution involving AGGREGATE (or SMALL(IF())- or MINIFS())

Apart from errors also zeroes should be excluded (or whatever other criteria added).

What would a solution involving MIN look like since =MIN(IF(ISNUMBER(A1:D1)*(A1:D1>0),A1:D1)) doesn't work and I am looking for a fairly sleek solution without unecessarily bloating the formula.

I also want to avoid nested IF-statements within the MIN-function ( e.g.: =MIN(IF(ISNUMBER(A1:D1),IF(A1:D1>0,A1:D1))) ) because the number of conditions can become quite long and I would like to keep the formula as readable as possible.

Thanks

]]>suppose my range in cells A1:A4 is 0,1,2,#DIV/0!

I would like to avoid a solution involving AGGREGATE (or SMALL(IF())- or MINIFS())

Apart from errors also zeroes should be excluded (or whatever other criteria added).

What would a solution involving MIN look like since =MIN(IF(ISNUMBER(A1:D1)*(A1:D1>0),A1:D1)) doesn't work and I am looking for a fairly sleek solution without unecessarily bloating the formula.

I also want to avoid nested IF-statements within the MIN-function ( e.g.: =MIN(IF(ISNUMBER(A1:D1),IF(A1:D1>0,A1:D1))) ) because the number of conditions can become quite long and I would like to keep the formula as readable as possible.

Thanks

Index Match v1.png

Hi,

Does Index Match and Xlookup support 3 criteria?

In cell E10 i try Xlookup for 3 criteria but come out with an error. Can assist to fix it with Xlookup and Index Match formula? Please note that one of the Match Mode i using is -1 because i looking the date is below 30/3/2020. The result expected is 98.

What else formula if both does not support.

Thanks dude.

Hi,

Does Index Match and Xlookup support 3 criteria?

In cell E10 i try Xlookup for 3 criteria but come out with an error. Can assist to fix it with Xlookup and Index Match formula? Please note that one of the Match Mode i using is -1 because i looking the date is below 30/3/2020. The result expected is 98.

What else formula if both does not support.

Thanks dude.

I download time-sheets showing (among other things) Date, Start & End times. Overtime is paid if >40hrs/week.

Goal:

Create 2 additional columns (Weekly Hrs. & Weekly O/T) to automate straight-time & overtime totals for each work-week

(see yellow hi-lite area w/ manually entered results).

Setup:

- Added column A to show actual weekday names & underlined each end of week section to make it easier to see irregular work-weeks.

- Range-names (Date, Start, & End) limited to each worksheet in "Name Manager" (12 work-sheets per workbook have identical column headings).

- Some entries pay flat-rate (daily rate) not included in O/T calculation (hi-lite in red w/ conditional format, "OR507" column F).

- Downloaded spreadsheet show these 24hr periods of daily rate as "1" in "Hours" column (actually 1 unit not 1 hour) so I created 2 additional columns with formulas to show "Hour Units" & "Day Units" hi-lite in green.

Challenges:

- Weeks are irregular (i.e. work-week can be anywhere form 0-7 days/week)

- Work-weeks carry from one month worksheet to the next month worksheet

Thank you in advance for any thoughts or work done to advance my goal!

Goal:

Create 2 additional columns (Weekly Hrs. & Weekly O/T) to automate straight-time & overtime totals for each work-week

(see yellow hi-lite area w/ manually entered results).

Setup:

- Added column A to show actual weekday names & underlined each end of week section to make it easier to see irregular work-weeks.

- Range-names (Date, Start, & End) limited to each worksheet in "Name Manager" (12 work-sheets per workbook have identical column headings).

- Some entries pay flat-rate (daily rate) not included in O/T calculation (hi-lite in red w/ conditional format, "OR507" column F).

- Downloaded spreadsheet show these 24hr periods of daily rate as "1" in "Hours" column (actually 1 unit not 1 hour) so I created 2 additional columns with formulas to show "Hour Units" & "Day Units" hi-lite in green.

Challenges:

- Weeks are irregular (i.e. work-week can be anywhere form 0-7 days/week)

- Work-weeks carry from one month worksheet to the next month worksheet

Thank you in advance for any thoughts or work done to advance my goal!

if I have 2 names in the same cell. e.g. John Smith.

Which formula can help me return - JSmith?

I tried concatenate but seems to work best when names are in separate cells.

]]>Which formula can help me return - JSmith?

I tried concatenate but seems to work best when names are in separate cells.

I have have 4 columns (E, G, I, & K - note each have a column in-between). Cell in Row 7 in these columns contain a text as follow:

E7=Complete G7=Complete I7=Complete K7=Complete

All I want to do is to check is the text in all the four cells is the same (i.e. "Complete").

If its TRUE, then in cell (L7 = Complete) is displayed.

Can someone help, thx!

]]>E7=Complete G7=Complete I7=Complete K7=Complete

All I want to do is to check is the text in all the four cells is the same (i.e. "Complete").

If its TRUE, then in cell (L7 = Complete) is displayed.

Can someone help, thx!

Index Match.png

Hi,

Index Match for Match type (Less than) in Column section is there any wrong with the formula? i want the result is before 31/3/2020. therefore, the answer i expected should be 98. I thought just put 1 for Match type should be able to get the result directly?

For Match type's Greater than and Exact is working fine.

Hope someone can guide and fix the formula for me.

Thanks.

Hi,

Index Match for Match type (Less than) in Column section is there any wrong with the formula? i want the result is before 31/3/2020. therefore, the answer i expected should be 98. I thought just put 1 for Match type should be able to get the result directly?

For Match type's Greater than and Exact is working fine.

Hope someone can guide and fix the formula for me.

Thanks.

Hi

I am trying to write an if statement that checks if a cell is blank and then if its not concatenates some text and a cell to populate the cell. I have got this far

=IF(AD2<>"","CONCAT(Planned, ,AD2)","")

Any help appreciated.

]]>I am trying to write an if statement that checks if a cell is blank and then if its not concatenates some text and a cell to populate the cell. I have got this far

=IF(AD2<>"","CONCAT(Planned, ,AD2)","")

Any help appreciated.

Good Morning

I have an Issue with the Index, Mode & Match Functions Array Formulas. In the example worksheet attached, in single comuns D375, E375, F375 & G375 (highlighted in Green) you can see it works fine for a single column,

In C375 (You can see my Array Formula &, that it is currently returning returning "N/A" & is highlighted in Red to help), it should be pulling back the Most Occuring Text/String value for the Range D2:G372 but, flatly refuses to work? I believe that this is because any string value may or may not be present in any of the Columns D - G & it is perfectly possible that the most common string in Column D is not present at all in Column G (for example).

I've tried all sorts of ways of doing it on single colmuns but it won't work because it has to take all four columns into consideration for example the string "YRRRRRY" appears as the most frequently ocurring text in column D (four times), it may only appear once in Column E but that would be five overall which may well be more than any different text string in Column E & could be the most ocurring value Over all four columns even it didn't then appear in F & G - I hope this makes sense?

I have spent weeks trying to find the solution to this &, am willing to pay for the answer if you wish to Contact me?

Best regards

Lycanthrope74

I have an Issue with the Index, Mode & Match Functions Array Formulas. In the example worksheet attached, in single comuns D375, E375, F375 & G375 (highlighted in Green) you can see it works fine for a single column,

In C375 (You can see my Array Formula &, that it is currently returning returning "N/A" & is highlighted in Red to help), it should be pulling back the Most Occuring Text/String value for the Range D2:G372 but, flatly refuses to work? I believe that this is because any string value may or may not be present in any of the Columns D - G & it is perfectly possible that the most common string in Column D is not present at all in Column G (for example).

I've tried all sorts of ways of doing it on single colmuns but it won't work because it has to take all four columns into consideration for example the string "YRRRRRY" appears as the most frequently ocurring text in column D (four times), it may only appear once in Column E but that would be five overall which may well be more than any different text string in Column E & could be the most ocurring value Over all four columns even it didn't then appear in F & G - I hope this makes sense?

I have spent weeks trying to find the solution to this &, am willing to pay for the answer if you wish to Contact me?

Best regards

Lycanthrope74

I have a dummy file attached, I want to return a value depending on which list the cell value is in.

as a scenario , if the value in row " BARANGAY" is in "LIST", row "DISTRICT" will be equal to "DISTRICT 1".

if the value in row " BARANGAY" is in "LIST 2", row "DISTRICT" will be equal to "DISTRICT 2" and if if the value in row " BARANGAY" is in "LIST 3", row "DISTRICT" will be equal to "DISTRICT 3".

As a result A2:A6 should be equal to "DISTRICT 1", A7:A11 will be ""DISTRICT 2" and A12:A16 will be qual to "DISTRICT 3"

Screenshot_189.jpg

as a scenario , if the value in row " BARANGAY" is in "LIST", row "DISTRICT" will be equal to "DISTRICT 1".

if the value in row " BARANGAY" is in "LIST 2", row "DISTRICT" will be equal to "DISTRICT 2" and if if the value in row " BARANGAY" is in "LIST 3", row "DISTRICT" will be equal to "DISTRICT 3".

As a result A2:A6 should be equal to "DISTRICT 1", A7:A11 will be ""DISTRICT 2" and A12:A16 will be qual to "DISTRICT 3"

Screenshot_189.jpg

Hi all,

I have data extracted from backend through IT and as a result the data set columns are in different sequence(sheet 2) than the actual files (sheet 1) . I copied column headers from correct files in another tab(sheet2) . I want to populate data under columns in sheet 2 as per data in sheet 1. I cant just copy each column one after another as the data set is huge. Its very manual and repetitive task. Wanted to know if there is an easier way to do it.

Thanks in advance.

I have data extracted from backend through IT and as a result the data set columns are in different sequence(sheet 2) than the actual files (sheet 1) . I copied column headers from correct files in another tab(sheet2) . I want to populate data under columns in sheet 2 as per data in sheet 1. I cant just copy each column one after another as the data set is huge. Its very manual and repetitive task. Wanted to know if there is an easier way to do it.

Thanks in advance.