Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that brings in daily input from a range of cells.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that brings in daily input from a range of cells.
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) "jmort1" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that brings in daily input from a range of cells.
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())) -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... 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) "jmort1" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that brings in daily input from a range of cells.
It sounds like what you want is to return the *last* (rightmost) number in
the range. =IF(COUNT(C2:AG2),LOOKUP(1E100,C2:AG2),"") -- Biff Microsoft Excel MVP "jmort1" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
indirect referencing cells with hyperlinks brings text not link- any solution? | Excel Worksheet Functions | |||
Input Boxes 4 Inserting A Range In A Formula | Excel Discussion (Misc queries) | |||
specify a range of cells for data input, down then over | Excel Discussion (Misc queries) | |||
I need to input daily cost codes would exceli calculate my totals | Excel Worksheet Functions | |||
NEED A FORMULA THAT BRINGS OUT NAME OF COLUMN (HEADER) A VALUE BE. | Excel Worksheet Functions |