Formula that brings in daily input from a range of cells.

[jmort1]

Can someone help me with creating a summary worksheet that extracts the data
from a secondary daily input worksheet? On the daily input worksheet, there
is a column for every day of the month, 1 thru 31. Eack day we input the
number of rooms our hotel sells. Ex.) Column C is day 1, a row under it is a
manual input cell for rooms sold that day (50). So from left to right each
day has an input of the rooms sold. What I'm trying to do is create a
"summary" worksheet that will automatically pull the newest days data. I
thought of If statements, but I'd have a string a mile long and I'm not
knowledgable enough in excel to even begin this. Should state that there are
about a dozen stats I wish to do the same thing with, all of the info is on
the data worksheet, but I need this summary information to cycle forward to
the next days amounts.

 

[Sean Timmons]

Presuming the numbers 1 - 31 are in row 1, starting in A.

=INDEX(Data!C1:AG2,2,MATCH(DAY(TODAY()),C1:AG1))

C1:AG2 is your range.. you can change to, say, C1:AG50 to capture 50 rows.

,2, says look in the 2nd row for this stat.

DAY(TODAY()) assumes you want to pull current day's stats. you can do
DAY(TODAY())-1 to get yesterday's.. or you can just enter the date in, say,
cell A1 of your summary sheet, then it's just
MATCH(A1,C1:AG1)

 

[T. Valko]

It sounds like what you want is to return the *last* (rightmost) number in
the range.

=IF(COUNT(C2:AG2),LOOKUP(1E100,C2:AG2),"")

 

[T. Valko]

Presuming the numbers 1 - 31 are in row 1, starting in A.

=INDEX(Data!C1:AG2,2,MATCH(DAY(TODAY()),C1:AG1))

If there are 31 columns and you're pulling from the column that corresponds
to the day number then you don't need the MATCH function:

=INDEX(Data!C1:AG2,2,DAY(NOW()))