# Posts by Ingo_Ingo

• ## Matching a name across 2 sheets and then pulling a value from sheet2 and marking sheet1 with a yes based on partial text

,

When I posted formula, you were online ...

Was it so hard to tell if it was OK or not?

A simple thank you would have been enough.

• ## Matching a name across 2 sheets and then pulling a value from sheet2 and marking sheet1 with a yes based on partial text

Try this:

Instead Tech Grade 1 in B1, use Custom Formatting "Tech Grade "# then in cell B1 put just number (1 or 2 or ...)

In Sheet1, cell B2, use this formula:

=IF(SUM(ISNUMBER(FIND("TG" & B\$1,Sheet2!\$B\$2:\$C\$7))*(Sheet2!\$A\$2:\$A\$7=\$A2))=1,"y","") than drag down till last name

and if you put in C1 number 2, then you can drag formula from B1 to C1 then drag down and so on.

 Name Tech Grade 1 Tech Grade 2 Tech Grade 3 Joe y y y Dave y y Karen y y Bob y y
• ## Matching a name across 2 sheets and then pulling a value from sheet2 and marking sheet1 with a yes based on partial text

It' NOT the same problem....but

"Please put in your file (without formula) how you want to be."

• ## Matching a name across 2 sheets and then pulling a value from sheet2 and marking sheet1 with a yes based on partial text

Hi,

Please put in your file (without formula) how you want to be.

 Name TG1 TG2 TG3 Joe Y Y Dave Y Y Karen Y Y Bob Y Y
• ## Turn cell a color if cell content does not equal cell content of a range of values on another sheet

In Conditional formatting you can use this formula:

=SUM(COUNTIF(A2,ProjectCode))=0 where "ProjectCode" is NameRange

• ## Sort Multi-Column List Box by Clicking Header / Date Format Issue

Hi,

I'm not a programmer....

Try to format all txt......(who have data)...

In button UPDATE

Sheets("Worksheet").Cells(y, 3).Value = Format(txtReceived, "dd.mmm.YYYY") put data in your format mm/dd/yy or mmm/dd/yyyy ...

Sheets("Worksheet").Cells(y, 4).Value = Format(txtMoved, "dd.mmm.YYYY")

Sheets("Worksheet").Cells(y, 5).Value = Format(txtCompleted, "dd.mmm.yyyy")

Change everywhere you need.

• ## HOW TO FIX DATE SHOWING AS NUMBER SONLY

Hi,

for this:

In Private Sub lstProformaDisplay_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

replace:

with:

• ## WEEKNUM system 1 and 2

Quote

=IF(Specific cell="","",Original formula), etc.

Try:

=If(a687="",0,IF(WEEKNUM(A687,21)=WEEKNUM(B687,21),WEEKNUM(A687,21),WEEKNUM(A687,21)&-WEEKNUM(B687,21)))

or

=If(or(a687="",b687=""),0,IF(WEEKNUM(A687,21)=WEEKNUM(B687,21),WEEKNUM(A687,21),WEEKNUM(A687,21)&-WEEKNUM(B687,21)))

• ## Formula for finding Retirement Age

Hi,

If your DOB 13-06-1973 is in A2, then in B2 put this formula:

=DATE(YEAR(A2)+59,MONTH(A2),DAY(EOMONTH(A2,0)))

• ## Expand multilevel information

Common sense is not so common!

• ## How to find previous quarter and display as Q3 or Q4 etc

Or

="Q"&INT(MONTH(TODAY())/3)

Code
1. Sub TestPreviousQuarter()
2. MsgBox Evaluate("=""Q""&INT(MONTH(TODAY())/3)")
3. End Sub
• ## Help with sum, product and aggregate function

Hi,

use =IFERROR(B2*C2,0)+IFERROR(D2*E2,0)+IFERROR(F2*G2,0)+IFERROR(H2*I2,0)

• ## VBA from R1C1 to A1

Hi,

Try this:

Code
1. Sub Loop2()
2. Dim Rw As Long, Cl As Long
3. Range("C2").Select
4. Do
5. ActiveCell.Formula = WorksheetFunction.Average(ActiveCell.Offset(, -1), ActiveCell.Offset(, -2))
6. ActiveCell.Offset(1, 0).Select
7. Loop Until IsEmpty(ActiveCell.Offset(0, -1))
8. Range("A22").Select
9. End Sub
• ## Create a list from selections made in a column without blanks.

The formulas given by me do not produce that zero.

However, those zeros can be removed with conditional formatting or Custom Format.

• ## Create a list from selections made in a column without blanks.

Hi,

In sheet SETUP

In E4 use this formula:

=IF(\$B\$4="","",INDIRECT(\$B\$4&"!G"&ROW()+3)) and drag down

and in D4 use this formula:

=IF(\$E4="","",IF(LEN(\$B\$4),INDIRECT(\$B\$4&"!"&"F"&ROW()+3),"")) and drag down

In sheet Material1, Material2, Material3

In F7, then drag to G7 then drag down, this ARRAY FORMULA:

=IFERROR(INDEX(B\$7:B\$26,SMALL(IF(\$D\$7:\$D\$26="y",ROW(B\$7:B\$26)-ROW(B\$7)+1),ROWS(\$F\$6:F6))),"")