Search results

In this specific example, the single quotes are not necessary. This is because the sheetnames in this case do not have spaces or special characters. If a sheetname has space, e.g PP 1, then the single quotes are necessary. It is, however, good practice to always use them in this type of...

If the sheets are named correspondingly to the PP # as per your sample, then you could easily use a simple formula in H16: =INDIRECT("'PP"&H13&"'!I22")

I didn't mention it, and you probably figured it out, but that is an array formula, which means you need to confirm it with CTRL+SHIFT+ENTER so you get { } brackets around the formula, then you can copy it down and to next column(s).

See attached file. It includes a UDF macro to concatenate the matches.

Yes that formula is used to get Text strings or numbers... but only the first match (if there are more than one). I am not sure if this relates to the other thread you recently started. If it does, continue there please so we don't confuse things.

In your example you only show values of 1 in any given cell. Is it always going to be blank or 1? If not, what would your result look like if the count number was 2 or 3?

The dates in the Date Picked column of the database sheet are text, since you use the function TEXT(). You need to either convert that to a real date by adding a +0 to the end of the formula, or changing the formula to the more appropriate: =INT(S5) copied down. Either way, you will need to...

It might help if you attach the workbook, replacing any confidential data. Are you sure about the dates in the source? Try selecting the column of dates in the source data, then go to Data|Text to Columns, skip to 3rd dialogue and in the column data format area, select Date, then select MDY in...

Brian, I think you have an extra &c in there.... should it be: =sum(previous!A12:Indirect("previous!"& current!$aa$1&current!$aa$3))

Actually, I think you want: =INDIRECT("'M Summary'!"&Current!$AG14)

You can use SUMPRODUCT. If all you have is x's in those cells, then: =SUMPRODUCT(--(LEN(F6:F74))) if you may have other characters in some of those cells, but only want to count the "x', then: =SUMPRODUCT(--(LEN(F6:F74)-LEN(SUBSTITUTE(F6:F74,"x",""))))

Is there a space right after the colon? If so, use: =LEFT(MID(A1,SEARCH("bytes:",A1)+7,255),FIND(" ",MID(A1,SEARCH("bytes:",A1)+7,255))) otherwise try: =LEFT(MID(A1,SEARCH("bytes:",A1)+6,255),FIND(" ",MID(A1,SEARCH("bytes:",A1)+6,255)))

You are welcome ;)

Try this, with same assumptions as above in B2: =IFERROR(INDEX($G$2:$G$6,SMALL(IF(ISNUMBER(SEARCH($G$2:$G$6,$A2)),ROW($G$2:$G$6)-ROW($G$2)+1),COLUMNS($A$1:A$1))),"")this is to be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across 5 columns and down.

Assuming your comments start in A2, and that your bucket of 5 key words are in G2:G6, then use this formula in B2: =IFERROR(LOOKUP(9.999E+307,SEARCH($G$2:$G$6,A2), $G$2:$G$6),"") copied down.

The problem with the SUMPRODUCT() is that you need to have commas separating the arguments before the --.. e.g. =SUMPRODUCT(--($C$1:$C$500>=X46),--($C$1:$C$500<=Y46),--($A$1:$A$500=$R$61)) but also, as mentioned you can use COUNTIFS =COUNTIFS($C$1:$C$500,">="&X46...

Are you confirming the formula using CTRL+SHIFT+ENTER not just ENTER since it is an Array formula? You can avoid the CSE confirmation by adapting formula as: =INDEX($A$1:$A$478,MATCH(0,INDEX(COUNTIF($S$1:S1,$A$1:$A$478),0),0)) and also note that the formula must be in S2, copied down...

Try these: =AND(E2<>"",E2<>0,$D2=1) =AND(E2<>"",E2<>0,$D2>1,$D2<=5) =AND(E2<>"",E2<>0,$D2>5)

Are you sure that you don't have any macros in the workbook? Is it possible to post the workbook?

Try this: first, copy the formula in that cell elsewhere.. then, right click on the "bad" cell and choose Delete, then choose Shift Cells Up. Then right, click again and choose Insert, and then Shift Cells Down. and recopy the formula to the cell....