Which is the most recently edited column?

mlopes1

Board Regular
Joined
Jul 14, 2002
Messages
114
I have a workbook with 6 worksheets. Each of the first 5 worksheets represent one year. Wksht1 = 2001, wksht2 = 2002, etc.

On these worksheets, there are 13 columns. Column 1 lists products and columns 2-13 represent January through December. Prices of each product are updated monthly in these columns.

I then have a 6th worksheet called "Current", which I would like to list the most recent/current price. In other words, if its November 1st of 2002 and new prices are entered, I would like wksht "Current" to display those prices.

I aam having a hard time coming up with a way to do this! Any ideas? If anything was unclear please let me know. Thanks in advance.
This message was edited by mlopes1 on 2002-10-17 10:11
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
I have a workbook with 6 worksheets. Each of the first 5 worksheets represent one year. Wksht1 = 2001, wksht2 = 2002, etc.

On these worksheets, there are 13 columns. Column 1 lists products and columns 2-13 represent January through December. Prices of each product are updated monthly in these columns.

I then have a 6th worksheet called "Current", which I would like to list the most recent/current price. In other words, if its November 1st of 2002 and new prices are entered, I would like wksht "Current" to display those prices.

I aam having a hard time coming up with a way to do this! Any ideas? If anything was unclear please let me know. Thanks in advance.

In your example above (with the November column having the most recent data), is there any data at all in the December column? If they are blanks, it sounds like you can use this little handy formula Aladin and a few others have been bouncing around for a while.

=INDEX(B2:M2,1,MATCH(9.99999999999999E+307,B2:M2,1))

HTH
 

mlopes1

Board Regular
Joined
Jul 14, 2002
Messages
114
Any field beyond the current month IS blank like you guessed. The formula works great. I need to adjust it so that it can check across multiple worksheets but thats a good start. Thanks as always
This message was edited by mlopes1 on 2002-10-17 13:46
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-17 13:27, mlopes1 wrote:
Any field beyond the current month IS blank like you guessed. The formula works great. I need to adjust it so that it can check across multiple worksheets but thats a good start. Thanks as always
This message was edited by mlopes1 on 2002-10-17 13:46

In order not to repeat that big number, use a defined name instead...

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum as name in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

Now use BigNum in the MATCH bit whenever needed.
This message was edited by Aladin Akyurek on 2002-10-17 14:04
 

brownsugar

New Member
Joined
Oct 16, 2002
Messages
2
Hi Mark,

Have you tried adding macros that link back to the previous worksheets? You will have to list macros though for each row in each column listed from all five worksheets.

Also, you can insert comments....by using your review toolbar.

Hope this helps you.

Brown Sugar

B
 

Forum statistics

Threads
1,144,059
Messages
5,722,272
Members
422,419
Latest member
Havok390

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
Top