IF and AND Statement

December 14, 2011 at 00:20:12
Specs: Windows 7, Intel Core2Duo/8GB
Hi, how would I write a formula for A2:F4 on Sheet 1 to yield a "Y" in B2:F4 and Status (Work, Sick, Vac or blank) in A2:A4 if there are data in A1:B7 on Sheet 2?

Sheet 1

A B C D E F
1 12/19 12/20 12/21 12/22 12/23
2 Work Y Y blank Y Y
3 Sick Y blank blank blank blank
4 Vac blank blank blank blank Y

Sheet 2

1 12/19 Work
2 12/19 Sick
3 12/20 Work
4 12/21 blank
5 12/22 Work
6 12/23 Work
7 12/23 Vac

If column B on sheet 2 has another status, then it would be displayed in column A of sheet 1.

Thank you,

Mike

Ps. Sorry I forgot how to line up the data in the columns.


See More: IF and AND Statement

Report •

#1
December 14, 2011 at 12:43:54
Hi, please disregard the first scenario.

How would I write a formula for A2:I7 on Sheet 1 to yield a "Y" in C2:I7 and Status (Work, Work OT, Comp Time Leave, Sick Leave, Holiday, Vacation) in A2:B7 (otherwise leave blanks) if there are data in A1:C8 on Sheet 2?

Sheet 1

- A B C D E F G H I
1 - - 12/18 12/19 12/20 12/21 12/22 12/23 12/24
2 Work - - - Y - Y - -
3 Work OT Y - Y - - - Y
4 Sick - - Y - - - - -
5 Vac - - - - - - Y -
6 CT - - - - Y - - -
7 Hol - - - - - - - -

Sheet 2

- A B C
1 12/18 Work OT
2 12/19 Sick -
3 12/20 Work -
4 12/20 Work OT
4 12/21 CT -
5 12/22 Work -
7 12/23 Vac -
8 12/24 Work OT

If B1:C8 on sheet 2 have other statuses (i.e. Leave with Pay-LWP or Comp Time-CT), then they would be displayed in B1:B8 of sheet 1.

Thank you,

Mike

Ps. Sorry I forgot how to line up the data in the columns.


Report •
Related Solutions


Ask Question