Modify Last_Cell_Value Module

No. This code works off of a selected range. It does not check for date in any way whatsoever. So, there is no way to "modify" it to "find" the entry for "last week".

To find "last week" ... if your spreadsheet is set up so that dates are in a column and each new entry is a new row, then you need code that will:
+ find the column containing "date"
+ search the column for an entry with a date that is about 7 days less than "today"
+ then do whatever it is you want to do once you've found that row.

I am sure that you will get the help you need if you explain a bit more about how your workbook / worksheet are laid out.

Thanks for your help MSP77079,

My workbook contains thirty-five worksheets with market data going back to 1988. Column A in each worksheet is the date column. Each day I manually update each worksheet by adding a new row of data corresponding to the most recent market date.

The last worksheet in the workbook contains six columns of data. The first two columns are "Current Status" and "Current Value." I use the last_cell_value module to automatically update the data in those two columns with the most recent data. An example of the formula used is: last_cell_value('Bullish Percentages'!E1:E50000)

The next two columns are "One Week Ago Status" and "One Week Ago Value." I'm trying to find a formula that will automatically update those columns as well. The same thing applies to "One Month Ago Status" and "One Month Ago Value."

The three points that you mentioned are exactly what I need, but don't know how to do.

Thanks Again!

Let me be sure I understand this ... you type in "last_cell_value('Bullish Percentages'!E1:E50000)", or something very similar to this, into 35 worksheets?

What remains in the cell after you hit "Enter"?

OK ... I get it now. You do not type in the formula every day. The formula stays put; you update the 34 or 35 history sheets and the formula merely summarizes the most recent data.

You have two choices. You could modify the "last_cell_value" function to offset by 5 rows (assuming there are never any holidays) to get last week, and offset by 20 rows (same assumption) to get last month.

Or, do as I indicated earlier. I have something like this already. Let me modify it a bit and I'll post it later today.

Below are 3 user-defined-functions (UDFs).

The first, I would use to replace your current "last_cell_value". It is slightly less flexible than "last_cell_value" in that is assumes you are always interested in a single column. (If I understand your needs, this is correct.) On the other hand, it is more flexible in that it allows for more than 50,000 rows of data; so it will be useful when you move to Excel2007.

The next two UDFs build off of the first. They find the date associated with the last row of data (for a given column), then back up 7 days or 1 month. If there is no exact match (for example, if that date was a holiday), it returns the data from the previous row. (If you want to make that the next row down instead of up, change the 1 to -1 in the "Match" statement.)

Please Login or Register  to view this content.

Examples:
=Last_Row_value(Sheet2!$B:$B)
=Last_week_value(Sheet2!$B:$B)
=Last_month_value(Sheet2!$B:$B)

Thanks a lot! I'll incorporate the UDF this weekend and let you know how it works.

The last_row_value works well, but I'm getting an Ambiguous Name Detected message with the last_week_value and last_month_value UDF's....

Ambiguous error means that the subroutine or function is in two places at once.

Any possibility that you hit "paste" more than once?

Do a search in the VB Editor (Ctrl+F). Be sure to select "Entire Project" for the search.

It's working now. Thanks a lot for the assistance!