VBA Teachers FTE

A

anthonyexcel

Active Member
Joined
Jun 10, 2011
Messages
258
Office Version
  1. 365
Platform
  1. Windows
I need some help. I am trying to caluate a teachers FTE. Basically if a teacher is at a school for the whole day then tthe teacher gets 0.2 FTE, if the teacher is at the school for a half a day then the teacher gets 0.1 FTE. In the example below if there is 1 school in a cell then it is worth 0.2 for that school, if there are 2 schools in a cell then each school is worth 0.1.

******** ******************** src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js%22%3E%3C/SCRIPT%3E%3CCENTER%3E%3Ctable" target=_blank>http://www.interq.or.jp/sun/puremis/colo/popup.js">*********><CENTER>

<TBODY>
[TD="bgcolor: #0c266b, colspan: 7"][TABLE="width: 100%, align: center"]
<TBODY>[TR]
[TD="align: left"] Microsoft Excel - FTE Example.xlsx
[/TD]
[TD="align: right"]___Running: 14.0 : OS = Windows XP
[/TD]

</TBODY>

[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 7"][TABLE="width: 100%, align: center"]
<TBODY>[TR]
[TD](F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
[/TD]
[TD]<FORM name=formCb059465><INPUT value="Copy Formula" type=button name=btCb290334 *******='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);'></FORM>
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 7"]

<TBODY>
[TD="bgcolor: white"]<SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION selected value=Ford>A1</OPTION></SELECT>
[/TD]
[TD="bgcolor: #d4d0c8, align: right"] =
[/TD]
[TD="bgcolor: white, align: left"]<INPUT value="Emp ID" size=80 name=txbFb965317>
[/TD]

</TBODY>

[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8"][/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>A</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>B</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>C</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>D</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>E</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>F</CENTER>[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8"]
<CENTER>1</CENTER>[/TD]
[TD="bgcolor: #ffffff"]Emp ID
[/TD]
[TD="bgcolor: #ffffff, align: left"]Mon
[/TD]
[TD="bgcolor: #ffffff, align: left"]Tue
[/TD]
[TD="bgcolor: #ffffff, align: left"]Wed
[/TD]
[TD="bgcolor: #ffffff, align: left"]Thu
[/TD]
[TD="bgcolor: #ffffff, align: left"]Fri
[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]
<CENTER>2</CENTER>[/TD]
[TD="bgcolor: #ffffff"]103
[/TD]
[TD="bgcolor: #ffffff, align: left"]Washington
[/TD]
[TD="bgcolor: #ffffff, align: left"]Madison
[/TD]
[TD="bgcolor: #ffffff, align: left"]Washington
[/TD]
[TD="bgcolor: #ffffff, align: left"]Barnum
[/TD]
[TD="bgcolor: #ffffff, align: left"]Barnum,Washington
[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]
<CENTER>3</CENTER>[/TD]
[TD="bgcolor: #ffffff"]104
[/TD]
[TD="bgcolor: #ffffff, align: left"]Nixon,Madison
[/TD]
[TD="bgcolor: #ffffff, align: left"]Madison,Nixon
[/TD]
[TD="bgcolor: #ffffff, align: left"]Barnum,Nixon
[/TD]
[TD="bgcolor: #ffffff, align: left"]Nixon,Barnum
[/TD]
[TD="bgcolor: #ffffff, align: left"]Jackson
[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]
<CENTER>4</CENTER>[/TD]
[TD="bgcolor: #ffffff"]105
[/TD]
[TD="bgcolor: #ffffff, align: left"]Harrison
[/TD]
[TD="bgcolor: #ffffff, align: left"]Harrison
[/TD]
[TD="bgcolor: #ffffff, align: left"]Harrison
[/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: left"]Harrison
[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]
<CENTER>5</CENTER>[/TD]
[TD="bgcolor: #ffffff"]106
[/TD]
[TD="bgcolor: #ffffff, align: left"]Lincoln, Harrison
[/TD]
[TD="bgcolor: #ffffff, align: left"]Harrison
[/TD]
[TD="bgcolor: #ffffff, align: left"]Lincoln
[/TD]
[TD="bgcolor: #ffffff, align: left"]Ford, Harrison
[/TD]
[TD="bgcolor: #ffffff, align: left"]Ford
[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 7"][TABLE="width: 100%, align: left"]
<TBODY>[TR]
[TD="bgcolor: #ffffff, align: left"]Sheet1
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>


This is what I am looking for on sheet 2:

******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********><CENTER>

<TBODY>
[TD="bgcolor: #0c266b, colspan: 9"][TABLE="width: 100%, align: center"]
<TBODY>[TR]
[TD="align: left"] Microsoft Excel - FTE Example.xlsx
[/TD]
[TD="align: right"]___Running: 14.0 : OS = Windows XP
[/TD]

</TBODY>

[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 9"][TABLE="width: 100%, align: center"]
<TBODY>[TR]
[TD](F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp [URL="http://www.mrexcel.com/forum/#javascript<strong></strong>:void(0)"](A)bout
[/TD]
[TD]<FORM name=formCb658016><INPUT value="Copy Formula" type=button name=btCb074442 *******='window.clipboardData.setData("Text",document.formFb431795.sltNb305190.value);'></FORM>
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 9"]

<TBODY>
[TD="bgcolor: white"]<SELECT onchange="document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value" name=sltNb305190><OPTION selected value=0.3>A1</OPTION></SELECT>
[/TD]
[TD="bgcolor: #d4d0c8, align: right"] =
[/TD]
[TD="bgcolor: white, align: left"]<INPUT value=Washington size=80 name=txbFb428726>
[/TD]

</TBODY>

[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8"][/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>A</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>B</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>C</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>D</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>E</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>F</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>G</CENTER>[/TD]
[TD="bgcolor: #d4d0c8"]
<CENTER>H</CENTER>[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8"]
<CENTER>1</CENTER>[/TD]
[TD="bgcolor: #ffffff"]Washington
[/TD]
[TD="bgcolor: #ffffff"]Madison
[/TD]
[TD="bgcolor: #ffffff"]Barnum
[/TD]
[TD="bgcolor: #ffffff"]Nixon
[/TD]
[TD="bgcolor: #ffffff"]Jackson
[/TD]
[TD="bgcolor: #ffffff"]Harrison
[/TD]
[TD="bgcolor: #ffffff"]Lincoln
[/TD]
[TD="bgcolor: #ffffff"]Ford
[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]
<CENTER>2</CENTER>[/TD]
[TD="bgcolor: #ffffff"]0.5
[/TD]
[TD="bgcolor: #ffffff"]0.2
[/TD]
[TD="bgcolor: #ffffff"]0.3
[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]
<CENTER>3</CENTER>[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"]0.2
[/TD]
[TD="bgcolor: #ffffff"]0.2
[/TD]
[TD="bgcolor: #ffffff"]0.4
[/TD]
[TD="bgcolor: #ffffff"]0.2
[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]
<CENTER>4</CENTER>[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"]0.8
[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]
<CENTER>5</CENTER>[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"]0.4
[/TD]
[TD="bgcolor: #ffffff"]0.3
[/TD]
[TD="bgcolor: #ffffff"]0.3
[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 9"][TABLE="width: 100%, align: left"]
<TBODY>[TR]
[TD="bgcolor: #ffffff, align: left"]Sheet2
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>


Thank in advance.[/URL]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Formula in B8 copied down and across:

Excel 2010
A
B
C
D
E
F
G
H
I
Emp ID
Mon
Tue
Wed
Thu
Fri
Washington
Madison
Washington
Barnum
Barnum,Washington
Nixon,Madison
Madison,Nixon
Barnum,Nixon
Nixon,Barnum
Jackson
Harrison
Harrison
Harrison
Harrison
Lincoln,Harrison
Harrison
Lincoln
Ford,Harrison
Ford
Washington
Madison
Barnum
Nixon
Jackson
Harrison
Lincoln
Ford

<TBODY>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]103
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]104
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]105
[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]106
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]103
[/TD]
[TD="align: right"]0.5
[/TD]
[TD="align: right"]0.2
[/TD]
[TD="align: right"]0.3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]104
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.2
[/TD]
[TD="align: right"]0.2
[/TD]
[TD="align: right"]0.4
[/TD]
[TD="align: right"]0.2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]105
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"]106
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.4
[/TD]
[TD="align: right"]0.3
[/TD]
[TD="align: right"]0.3
[/TD]

</TBODY>
Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B8
[/TH]
[TD="align: left"]=SUM(COUNTIF($B2:$F2,B$7),COUNTIF($B2:$F2,"*"&B$7&"*"))*0.1
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA Complex Lookup
Replies
3
Views
438
JLGWhiz
JLGWhiz
A
  • Question Question
VBA ConcatIf
Replies
3
Views
1K
anthonyexcel
A
T
  • Question Question
If statement or Macro?
Replies
4
Views
406
tygrl510
T
P
  • Question Question
Renaming Sheet tabs VBA
Replies
0
Views
455
pujo
P
P
  • Question Question
imported file
Replies
4
Views
508
philmatth
P

Forum statistics

Threads
1,224,798
Messages
6,181,038
Members
453,013
Latest member
Shubashish_Nandy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top