Finding the rightmost value for a non-continuous range (cntd.)

[dotrooster]

I posted this in the original thread but I didn't receive any response. I thought that maybe I had done something wrong and that it would be better if I posted a new topic. Here is a link to the original thread.

My original question was as follows:

Hi, I'm not sure if I'm going to be using the right terminology, so please bear with me.

I have a set of data that has repeating column headers. So, on a sheet called "January" I have "Forecast" and "Actual" headers that repeat over and over underneath "Week 1", "Week 2", etc. On a different sheet (called "2014"), I'm trying to write a formula that will produce the latest or rightmost number underneath the "forecast" header. What I want is to be able to input data into the "January" sheet every week so that I have a record of progress and I want the "2014" sheet to show the latest data for every month. I really hope that makes sense.

I really appreciate anyone that might be able to lend a hand. Thank you!

I very quickly received a response and everything was great until I decided to make things a bit more complicated.

I decided that I needed a second set of formulas that would also report "Last Week". So, a formula that would only run if there are two weeks worth of data, that would return the data from the week prior to the one currently being reported in the "Latest" columns. I tried to do it myself, but apprently, I'm missing something. Currently, if there is something in the FC, column but not in the ACT column, the latest FC and ACT columns both report the data in the latest FC column. I just can't seem to get this to work! Any help that you guys can give me would be really greatly appreciated.

Side note: Currently, I have 0's in all of the cells, but I can use blank cells if that makes it easier. Thanks again

[ajryan88]

Hi,

Does this do what you want?

I hope this helps

[bebo021999]

These formulas may help:
D7:

Please Login or Register  to view this content.

Copy to E7.
F7:

Please Login or Register  to view this content.

Copy to G7.

[dotrooster]

Thank you so much.

Worked like a charm

[ajryan88]

No problems

Please don't forget to mark this thread as solved, and please click on the * next to any post(s) that have been helpful to you to say thanks to the poster(s)

Have a great day