=IF(Q5>Data!L10,"Green",IF(ABS(Q5-Data!$L$10)<=0.5,"Amber", "Red"))]]>

=IF(Q5>Data!L10,"Green",IF(ABS(Q5-Data!$L$10)<=0.5,"Amber", "Red"))

]]>Second Look. Maybe your spacing on the header for discount % is not exactly as it seems. Make sure that there are no leading or trailing spaces. But to be sure, read my…]]>

Second Look. Maybe your spacing on the header for discount % is not exactly as it seems. Make sure that there are no leading or trailing spaces. But to be sure, read my first sentence above.

]]>Any assistance would be greatly appreciated.

]]>Thank you as i have been working this for so long it became a quest. It works perfect and cant thank you enough. I unfortunately have some gaps in my working with formulas and could visualize working SUMPRODUCT. More work to on my end and…]]>

Thank you as i have been working this for so long it became a quest. It works perfect and cant thank you enough. I unfortunately have some gaps in my working with formulas and could visualize working SUMPRODUCT. More work to on my end and again very thankful for for your effort.

]]>(Code, 1 line)

]]>

Code

`=INDEX(IF(COLUMN(A$1)=1,$E:$E,$D:$D),-MOD(AGGREGATE(14,6,($C$2:$C$13=$G2)*$D$2:$D$13*1000-ROW($2:$13),1),-1000))`

(Code, 1 line)

]]>

Cell B2 formula , Drag down

Code

`=TEXTJOIN(",",,IF(ISNUMBER(FIND($A2,Sheet2!$B$2:$B$4)),Sheet2!$A$2:$A$4,""))`

(Code, 1 line)

]]>

Code

`=SUMPRODUCT(($A$10:$A$21=$A25)*(YEAR($B$1)=--$C$3:$N$3)*(MONTH($B$1)=MONTH($C$7:$N$7))*(--$B$10:$B$21<=DAY($B$1))*$C$10:$N$21)`

If the latter select the date range Click Data - Text to columns - Finish. If…]]>

please post a sample with some representative data. Are your dates real dates ( right aligned ) or text looking like dates ( left aligned). (assuming no manual alignment)

If the latter select the date range Click Data - Text to columns - Finish. If right aligned you are OK.

]]>Funtion like this, but the formula look the value in other book, I put this formula in so many cell, this cause when I open o when I down or up the…]]>

=LOOK(2^15;SEARCH(G20;D:D;1);E:E)

Funtion like this, but the formula look the value in other book, I put this formula in so many cell, this cause when I open o when I down or up the sheet, the procces of calculation of excel its so slow

forum.ozgrid.com/index.php?attachment/1233427/ say calculate 4 subprocess and its so slow

How can I improve that?

]]>=BUSCARV(

I select the word of the cell L395 to vlookup in other book, example I look for the value

I have one code like this, its in spanish

=BUSCARV(**L395**;'[FACTURAS POR PAGAR (CEMENTO).xlsx]FACTURAS CEMENTO'!D:H;1;FALSO)

I select the word of the cell L395 to vlookup in other book, example I look for the value **1500 **but in the other book say **a1500, **I need put * in the formula like =VLOOKUP("*"&**L395....**

But when I find the value like only **1500 **with the same formula this formula =VLOOKUP("*"&**L395.... **it doesn't work for me cause the formula search the value after other value, cause have "*", how can I make the formula for look the value 1500 for example, before, after or between other value?? I want to have in differents cell a1500, 1500, 1500a, a1500a and find 1500 with VLOOKUP, I have this code for this actually but this slows down my excel because have one "*" like condition for one VLOOKUP

=SI.ERROR(SI(L404=0;"SIN INFO";SI.ERROR(BUSCARV(L404;'[FACTURAS POR PAGAR (CEMENTO).xlsx]FACTURAS CEMENTO'!D:H;5;FALSO);SI.ERROR(BUSCARV("*"&L404;'[FACTURAS POR PAGAR (CEMENTO).xlsx]FACTURAS CEMENTO'!D:H;5;FALSO);SI.ERROR(BUSCARV(L404&"*";'[FACTURAS POR PAGAR (CEMENTO).xlsx]FACTURAS CEMENTO'!D:H;5;FALSO);BUSCARV("*"&L404&"*";'[FACTURAS POR PAGAR (CEMENTO).xlsx]FACTURAS CEMENTO'!D:H;5;FALSO)))));"")

Good luck!]]>

Code

`=TRIM(MID(SUBSTITUTE($A1,", ",REPT(" ",LEN($A1))),(COLUMNS($A$1:A$1)-1)*LEN($A1)+1, LEN($A1)))`

Good luck!

]]>Is it…]]>

Is it possible through conditional formatting alone?

I have got an Excel problem that I am not really able to solve by myself and therefore kindly request your support.

I would like to find out on how many parallel projects our employees are staffed.

In doing so, I have the start and end…]]>

I have got an Excel problem that I am not really able to solve by myself and therefore kindly request your support.

I would like to find out on how many parallel projects our employees are staffed.

In doing so, I have the start and end date of each project and on which project which employee is staffed in which role (PM = project manager, CT = core team, ET = extended team).

I am able to find out which projects overlap by using the SUMPRODUCT- formula (SUMPRODUCT((I9<projectend)*(I10>=projectstart))>1. Unfortunately, this formula only compares all projects with each other and provides the information if a project overlaps with with another project in the list.

Do you have any ideas? I would really appreciate if someone could provide a solution approach to this problem .

I have attached a dummy file visualizing the structure of my Excel sheet.

Thank you so much!

Best,

Philipp]]>

=IF($A23="Month Ending",SUBTOTAL(9,K$2:K23)]]>