Excel Look Up Question

[madab]

Hi,

Hoping some one might be able to help me with this one:

Is it possible to use some form of look up formula that will return the value of the last populated cell in each row in a table, when the number of populated cells in each row in that table is not the same?

Is it also possible to include conditions to this formula e.g. return the last value in row 2 if condition A and conditionB are True else return blank

Thanks!

 

[Joe4]

Are you looking for formula solutions or VBA?

Can you also give us some specific details, and maybe an example (it is hard to write formulas for very generic explanations, I mean we could do it, but then it would be left to you to adjust to work for you)?

 

[madab]

Hi,

Looking for a formula solution.
Example below, hopefully this will be of some help
I have a calendar for the next 6 months, which has a list of tasks in column A and dates going across in row 1. Each task on the calendar needs to be updated with either Completed, In Progress or Outstanding. Items are updated daily, weekly or monthly. To avoid having to scroll across to check each item, I was hoping to be able to use a formula that will help me identify tasks that have a status of either In Progress or Outstanding on a particular date.

Any suggestions?

Thanks

 

[Joe4]

Have a look here: https://www.extendoffice.com/docume...-first-last-non-blank-cell-in-row-column.html

So, the formula to find the value of last populated cell in row 3 would look like:
=LOOKUP(2,1/(3:3<>""),3:3)

 

[madab]

Joe – thanks for that. That’s got me most of the way.

The last part is this … There are future dated items thatcan be updated on the calendar. That formula will show the last status that waspopulated however that could be for a date 2/3/4 months into the future. I’dlike to be able to see the status of the current task in the month current. Sofor example, if the task is weekly, show the status of the task that is duethis week, if the task is monthly, show the status of the task that is due thismonth and so on. There is a frequency field in column B that identifies thefrequency of each task.

Could the formula be tweaked so that it would look along thecalendar dates in row 1, and only look at cells up to today’s date or today’sdate + 1 and the return the status of those items? Finding it difficult totweak the formula to do that though!

 

[Joe4]

I do not know how to do that with a formula. It would be rather complex, especially since you have different frequencies.
Even with VBA, I think it would be a challenge (I think it can be done, but it would be a challenge!).

 

[madab]

Ok no worries Joe, thanks for the help, much appreciated!

 

[Joe4]

You are welcome.