I hope this can be done?

alfonsoto1

Board Regular
Joined
Nov 25, 2004
Messages
104
I have a sheet that column B thru M equals January thru December, in row 589 for each month is a dollar figure that I would like to reflect in column P row 589 for each month as the appear. Is this possible?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi, Your requirement was not very clear from description but are you looking for something like this?


Book1
BCDEFGHIJKLMNOP
588JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberSeptember
589$ 9,137.00$ 16,706.00$ 6,022.00$ 4,872.00$ 19,466.00$ 15,000.00$ 8,734.00$ 7,877.00$ 4,522.00$ 4,522.00
Sheet2
Cell Formulas
RangeFormula
P589{=INDEX($B589:$M589,SMALL(IF($B$589:$M$589=0,COLUMN($B$589:$M$589)-COLUMN($B$589)+1),1)-1)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Errant post
 
Last edited:
Upvote 0
Hi,

Perhaps this might also help:


Book1
BCDEFGHIJKLMNOP
1JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberSeptember
2$9,137.00$16,706.00$6,022.00$4,872.00$19,466.00$15,000.00$8,734.00$7,877.00$4,522.00$4,522.00
Sheet704
Cell Formulas
RangeFormula
P1=LOOKUP(2,1/FIND(P2,B2:M2),B1:M1)
P2=LOOKUP(9.99999999999999E+307,B2:M2)


Change/adjust cell references/range according to your data.
 
Upvote 0
I hope this also helps you

=HLOOKUP(99^9,B2:M2,1)
 
Upvote 0
That did not work, I used Aryatect's answer and it worked perfect. Thanks to all of you.

Sorry, it worked for me.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:14.26px;" /><col style="width:70.34px;" /><col style="width:77.94px;" /><col style="width:70.34px;" /><col style="width:70.34px;" /><col style="width:77.94px;" /><col style="width:77.94px;" /><col style="width:70.34px;" /><col style="width:70.34px;" /><col style="width:75.09px;" /><col style="width:17.11px;" /><col style="width:17.11px;" /><col style="width:17.11px;" /><col style="width:130.22px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >P</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">1</td><td >January</td><td >February</td><td >March</td><td >April</td><td >May</td><td >June</td><td >July</td><td >August</td><td >September</td><td >O</td><td >N</td><td >D</td><td >September</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">$9,137.00</td><td style="text-align:right; ">$16,706.00</td><td style="text-align:right; ">$6,022.00</td><td style="text-align:right; ">$4,872.00</td><td style="text-align:right; ">$19,466.00</td><td style="text-align:right; ">$15,000.00</td><td style="text-align:right; ">$8,734.00</td><td style="text-align:right; ">$7,877.00</td><td style="text-align:right; ">$4,522.00</td><td > </td><td > </td><td > </td><td style="text-align:right; ">$4,522.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">$4,522.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">$4,522.00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >P2</td><td >=HLOOKUP(99^9,B2:M2,1)</td></tr><tr><td >P3</td><td >=LOOKUP(9.99999999999999E+307,B2:M2)</td></tr><tr><td >P4</td><td >{=INDEX($B2:$M2,SMALL(IF($B$2:$M$2=0,COLUMN($B$2:$M$2)-COLUMN($B$2)+1),1)-1)}</td></tr></table></td></tr></table>
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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