Solved: To reflect latest data updated in a row range

[rajagopal]

Hi,

I've updating the daily temperature data in R1C1, R1C2, R1C3...

In cell B1 (latest temp), i want to reflect the latest temp updated in the above range (R1C1, R1C2, R1C3...).

Is there any formula to get this done?

[Bob Phillips]

Isn't B1 and R1C2 the same cell?

[rajagopal]

Sorry. It should be B2

[Bob Phillips]

When you say latest in row 1, do you mean the max? Otjerwise, how do we know when it isa latest?

[rajagopal]

It's not max value.
Above the first row, the row headers will have dates from 01st Jul to 31st Jul. Day wise temp will be entered below each dates (shown in R1C1, R1C2...)

On 01st July, the value entered in R1C1 should reflect in B2. On 02nd July, the value entered in R1C2 should reflect in B2. So by looking at B2, the user will come to know the latest temp.

This excel will be used to capture 1 year temperature data and hence the requirement.

[Bob Phillips]

I am getting confused by what is in each row, you seem to be saying the values are in row 2 now. Best if you post a sample workbook.

[shrivallabha]

If the layout is:
In Row 1 : Dates are written
In Row 2 : Corressponding temperature for date is written then
In Cell C1: formula below will work

=INDIRECT(ADDRESS(2,MATCH(TODAY(),1:1,0)))

And on second thoughts, I forgot that fairly simpler HLOOKUP will also work:

=HLOOKUP(TODAY(),1:2,2,0)

[rajagopal]

It picks up the data as of current day but at most of the times we get yesterday's or day before yesterday's data on the current day.

I've attached a sample file and provided examples on my requirement.

--thanks

[rajagopal]

Attached a sample workbook in latest post. Thanks

[shrivallabha]

The following formula can do it:

=OFFSET($A2,0,COUNT($B2:$AF2))

I am attaching the workbook. Have a look in the green, thick bordered cells where the formula is inserted.

[rajagopal]

Hi,

I tried to place the result (latest temp data) in the same row of the temp data and used the formula with changes in row reference but didn't show the latest data.

What has to be changed in the formula? PFA.

[shrivallabha]

Paste the following formula in cell B2:

=IFERROR(OFFSET($C1,1,COUNT($C2:$AG2)-1),"")

Please note: IFERROR is added to handle the case of circular reference.

[rajagopal]

Works fine but the same formula doesn't work in another worksheet having similar data. In the attached file, row 35 is like the temp data in the previous example.

Cell B35 has to reflect the latest % updated in the same row. Using the given formula, it returns value of 0%

[shrivallabha]

If this is the actual data where you wanted to implement formula then why not post it at the first place as:
1. There are merged cells so the COUNT will land up at the wrong place.
2. Cell H35 is merged and blank and your posted sample showed continuous data.

[rajagopal]

The solution for the first one was was very helpful. We received this data set a day before and wanted to try it on my own but couldn't.
Sorry for it.
Is there any solution for this?

[shrivallabha]

The problem with my formula in general is that they tend to be local i.e. case specific. Following ARRAY formula will work if:
1. The cell where you apply formula is not merged. The rest you may keep as they are!
2. You enter the formula using CTRL+SHIFT+ENTER. Correct entry will show braces {} around the formula.

=INDIRECT(ADDRESS(ROW(),LARGE(IF(E35:BO35<>"",COLUMN(E35:BO35)),1)))