Which is the most recently edited column?

[mlopes1]

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

 

[Asala42]

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]

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]

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]

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