# Posts by Ingo_Ingo

• ## excel formula to find quarter

Use this:

=IF(A3<=3,"Q1",IF(A3<=6,"Q2",IF(A3<=9,"Q3","Q4")))

• ## VLookup

Then in sheet A, cell C10 then drag till C13:

=VLOOKUP("*"&\$A10&"*",B!\$A:\$G,6,0)

and

in D10 then drag till D13:

=VLOOKUP("*"&\$A10&"*",B!\$A:\$G,7,0)

• ## VLookup

Quote

Unfortunately, I need to have 2 values populated in Cell C10 and C11 each, which are reffered to By A10 and A11 on sheet B

So, if it works, it needs to look like this:

 5,6 120 Receiption, Cleaner 7,8 125 Gardener, Maintenance Manager

in C10 sheet B:

=VLOOKUP(--LEFT(A10),A!\$A\$6:\$D\$13,2,0)&", "&VLOOKUP(--RIGHT(A10),A!\$A\$6:\$D\$13,2,0)

and drag to C11

• ## VLookup

Hi,

In sheet B in cell A10 you have 5,6 and in cell A11 you have 7,8.

Put 5 an 6 in separate cell

Same for 7 and 8

• ## +8192 formule: can i make a custom function to replace the function?

Hi,

2. Try to use Name_Range.

3. Cut your formula in small pieces. Depend of your version of Excel you can reduce formula.

Hi,

Thank you.

• ## Collecting data from multiple rows in one cell

Thank you RoyUK.

VBA code working perfect.

• ## Collecting data from multiple rows in one cell

I get this file from a program. I have no acces to this program.

This is the file. With some data.

Working time.xlsx

• ## Collecting data from multiple rows in one cell

No.

Is like in post 18

Say in A2:A54 are data like this:

06:30 cell A2

15:25

---------

21:45 cell A3

06:30

--------

22:30 cell A4

05:45

-------

------=

13:35 cell A54

23:20

and result will be in cells (B2:B54), like 08:15, 12:10, 07:45....

I need also to calculate working hour like this: start time 22:30 and end time - say 05:45

Thank you.

• ## Collecting data from multiple rows in one cell

My data is in column A from A2:A54 and need answer in column B

• ## Calculate working hours, if start time and end time are in same cell

Hi,

I need little help with a VBA code to calculate working hour.

Data is like here:

Start hour and end hour are in same cell (Alt+Enter)

Say in A2:A54 are data like this:

06:30 cell A2

15:25

---------

21:45 cell A3

06:30

--------

22:30 cell A4

05:45

-------

------=

13:35 cell A54

23:20

and result will be in cells (B2:B54) something like: 8:20, 11:15, 9:35 etc

Similar with this post but need to calculate hour passing hour 00:00

Thank you.

• ## Collecting data from multiple rows in one cell

This calculates the time for one cell- C6.

RoyUK,

Can you modify your VBA code to work with hour after 23:59.

Say in A2:A54 are data like this:

06:30 A2

15:25

---------

21:45 A3

06:30

--------

22:30 A4

05:45

-------

------=

13:35 A54

23:20

and result will be in cells (B2:B54)

Thank you.

• ## Based on value, find next highest value in range and return it's adjacent cell

If you want use VLOOKUP then use this formula:

=VLOOKUP(SMALL(I8:I17,COUNTIF(I8:I17,"<"&B5)+1),CHOOSE({1\2},I8:I17,H8:H17),2,0)

• ## Collecting data from multiple rows in one cell

If in A1 are that time and you want to copy in E1, then

Code
``````Sub Iancox01()
[e3] = [a1]
[e3].WrapText = False
End Sub``````
• ## 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.