Search results

@bosco_yip Your recent comments on other posts have really been a good challenge for me to avoid unnecessary array formulas. (appreciate the N(G7) too...) Mine isn't as short as yours, but I did come up with one: =IF(N(G7),LOOKUP(2,1/($E$3:E7="AC_NO"),$E$1:E7),"")

Array formula: =IF(OR(ISBLANK(I16),I16="Period Code"),"",INDEX($E$1:E16,MAX(IF($E$1:E16="AC_NO",ROW($E$1:E16)-2)))) See attached. I hope somebody else can do this without an array...

Is a macro a necessity? What if you paste in B4: =IF(LEN(B2)>0,A4,"") And in C4: =IF(LEN(C2)>0,A5,"") And drag down...(see attached).

sure. Line 1-2: Establish a variable (called TargetMonth) and assign a value to it from Cell A4 Line 3-4: Establish a variable (called TargetColumn) to define the final column in the range that will be hidden, and assign it a numerical value using a Match() function to find the position of...

Dr., The only way I know to do this is with VBA. See attached; I think the button will do what you want... Sub HideColumns() Dim TargetMonth As Integer TargetMonth = Sheets(1).Range("$A$4").Value Dim TargetColumn As Double TargetColumn =...

Welcome to the forum! Try this formula in cell E3, and drag to fill: =INDEX($C$3:$C$7,1+INT((ROWS($E$2:E13)-1)/3))

Welcome to the forum! See attached, using this formula: =IF(B3="Yes",IF(COUNTIF(C3:E3,"No")=3,"SS Only",IF(COUNTIF(C3:E3,"Yes")>0,"Partial SS")),IF(COUNTIF(C3:E3,"Yes")>0,"No SS"))

Posky -- I don't understand your desired output. Perhaps you can copy/paste the values you want into the Final Tab for illustration -- to give a clear vision of the output you want.

I think this line of your code... ActiveSheet.Protect Password = "BB" should instead be: ActiveSheet.Protect Password:= "BB" See if that fixes anything.

It looks like your date formats on the target sheet may be inconsistent...sometimes mm.dd.yyyy and other times dd.mm.yyyy Before trying to troubleshoot the vba, can you confirm that the dates are formatted consistently? (original file attached)

@excelnub: @bosco_yip's is better -- for several reasons, but especially if you are going to have thousands of rows. Array formulas are important, but they can really cripple your data processing time; particularly if you have a huge data set.

Hey @Chihiro, I have heard that the IF(ISERROR()) combo adds processing time -- I don't actually know if it's true...If so, here's an array entered alternative using your method: =IFERROR(INDEX($D$4:$D$8,MAX(IFERROR(FIND($D$4:$D$8,B4)^0,-1)*(ROW($D$4:$D$8)-3))),"NOT FOUND") Does anybody know...

What about this array formula in C4: =INDEX($D$4:$D$8,MATCH(1,(LEN(B4)<>LEN(SUBSTITUTE(B4,$D$4:$D$8,"")))+0,0)) See attached.

When you right click on the slicer and select properties, there should be a check box indicating whether the slicer is 'locked' -- when I 'un-check' the box, I am able to change a slicer on a protected sheet. If the pivot table is on the protected sheet, you have a different problem...

Welcome to the forums! You didn't indicate whether you want to use VBA or formulas...so I've done both. The VBA uses this code: Sub BuildTab() Dim No_Cycles As Integer No_Cycles = Sheets("Sheet2").Range("$A$2").Value Dim Days_Per As Integer Days_Per =...

Yep. Use the Indirect(). Keep in mind, you are titling your sheets with numerical values, so in the reference cell where you type the name of the target spreadsheet, you'll have to key two apostrophes for it to work...and don't substitute a quotation mark: ''-700'...

Take a look at the attached... I'm producing the target output that you requested (with one exception, which I think is a typo in your sample), but I have some reservations about the structure of your input data...We may need to rethink these formulas after you apply them to the real data set...

Thomas, I think you can double click on the data label and hover your mouse over a corner (the mouse indicator will change to a double-pointed arrow)...then resize the data label text box. See attached -- is this what you mean?

Formula: =SUMPRODUCT($I$9:$P$14*(--($I$8:$P$8=I$19))*(--($H$9:$H$14=$H20))) See attached.

Welcome to the Forum! I've modeled up something that may be what you are looking for...See attached. Not fancy, but perhaps it will be a helpful starting point. All best.

I think this formula will do what you want: =SUMPRODUCT((--(B5:B12=H5))*(--(C5:C12<=H6))*(--(D5:D12>=H6))*E5:E12)

An array formula like so? {=IFERROR(INDEX(RawData2016[Jan FTE Sum],MATCH(1,($F17=RawData2016[Employee '#])*($E17=RawData2016[Jan Title FTE])*($B17=RawData2016[Jan Location FTE]),0)),"")} NOTE: The text in cell C12 has an extra space: "Staff ", which I've corrected in the attached file...

YPJ, Like @Chihiro, I prefer the index(match()) construction over the vlookup, but assuming that we keep the vlookup pattern from your original formula... What about using this formula in K15...

I replied to a duplicate thread? http://chandoo.org/forum/threads/using-criteria-to-create-list-of-data-avoiding-duplicates-in-each-order.27775/

Greetings --- and welcome to the Forum! I propose that the following formula will achieve Task 1 (allocate items from Sample tab) and Task 2 (avoid duplicates on a given order). =INDEX(OFFSET('Sample items'!$A$2,,MATCH(Orders!B3,'Sample items'!$A$2:$F$2,0),COUNTIF('Sample...