How to return a cell value from a last occurring header

S

sono1

New Member
Joined
Jun 15, 2011
Messages
3
Hi
I am giving an example to explain my question:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<TABLE style="WIDTH: 386pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=514 border=0 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=66 height=17>Operator</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: aqua" width=64>Operator 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #cc99ff" width=64>Operator 2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: aqua" width=64>Operator 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #cc99ff" width=64>Operator 2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: aqua" width=64>Operator 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #cc99ff" width=64>Operator 2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: aqua" width=64>Operator 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Station</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: lime">Station1</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: #00ccff">Station2</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: yellow">Station3</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: #ffcc00">Station4</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: #ff99cc">Station5</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: silver">Station6</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: yellow">Station3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Cycle time</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>10</TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>15</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>18</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>14</TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>13</TD><TD class=xl84 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Start Time</TD><TD class=xl81 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="0.27083333333333331">6:30</TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="0.27083333333333331" x:fmla="=B4">6:30</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="0.28125" x:fmla="=C5">6:45</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="0.28125" x:fmla="=B5">6:45</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="0.28472222222222221" x:fmla="=D5">6:50</TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="0.28472222222222221" x:fmla="=E5">6:50</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="0.30902777777777779" x:fmla="=MIN(F5:G5)">7:25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>End time</TD><TD class=xl86 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.28125">6:45</TD><TD class=xl87 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.28125">6:45</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.28472222222222221">6:50</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.28472222222222221">6:50</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.30902777777777779">7:25</TD><TD class=xl77 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.30902777777777779">7:25</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.31597222222222221">7:35</TD></TR></TBODY></TABLE>

Let’s assume that the first cell in the table above is A1. This means that the start time value of the last column is in H4. I want to use a formula that tells me that “if the header of this column (i.e. H1) is “Operator1” then go to column where this header last occurred (in this case it will be F1) and select the End time value of that column (i.e. F5) then return it in to H4. This means that the end time value of operator 1 at station 5 will become the start time value of the same operator at station 3.<o:p></o:p>
<o:p></o:p>
I have been trying to use arbitrary lookups but not getting the desired results.

I will really appreciate any help,<o:p></o:p>
<o:p></o:p>
Thanks<o:p></o:p>
<o:p></o:p>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Try

=INDEX($A$5:G$5,MAX(IF($A$1:G$1=H1,COLUMN($A$1:G1))))

Array confirmed with Shift Ctrl Enter.

Note that I've left a deliberate "error" in the formula, if there is no previous record then the result will default to "End time"
 
Upvote 0
H4. formula copy across :

=LOOKUP(2,1/($B1:G1=H1),$B5:G5)

Regards
 
Upvote 0
jasonb75 said:
Try

=INDEX($A$5:G$5,MAX(IF($A$1:G$1=H1,COLUMN($A$1:G1))))

Array confirmed with Shift Ctrl Enter.

Note that I've left a deliberate "error" in the formula, if there is no previous record then the result will default to "End time"
Click to expand...

Hi Jason,

I tried to use the formula and it worked fine for few columns then it started picking the last entered value of the "End time" (from the previous column).

The example that i gave had operator 1and 2 in alternate columns but as i moved further on that sheet this sequence changed (i.e. same operators in two consecutive columns).

The formula that bosco sent after your post seems to be working. Anyway thank you so much for your help.

I really appreciate it
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,557
Messages
6,179,510
Members
452,918
Latest member
Davion615

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