Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up date on another sheet and do count of active cells (column
I have an Excel summary sheet that retrieves daily data from departmental
sheets based on date search (eg. TODAY()-1,etc.. I need a formula that will go to the sheet, search for the date and then "Count" the cells in columns next to the date that have data. I have used vlookup to find specific cells but can't seem to 'count' multiple cells/columns next to the date. As you can see, I am somewhat an inexperienced user. Thanks for the help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up date on another sheet and do count of active cells (column
Will that date appear once or more times on the other sheet?
Maybe something like =COUNTA(OFFSET(Sheet2!A1,MATCH(TODAY(),Sheet2!A1:A 1000,0)-1,0,1,256)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "gary m" <gary wrote in message ... I have an Excel summary sheet that retrieves daily data from departmental sheets based on date search (eg. TODAY()-1,etc.. I need a formula that will go to the sheet, search for the date and then "Count" the cells in columns next to the date that have data. I have used vlookup to find specific cells but can't seem to 'count' multiple cells/columns next to the date. As you can see, I am somewhat an inexperienced user. Thanks for the help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up date on another sheet and do count of active cells (column
Bob:
Date only appears once. Dates are in column b only, b8 thru b39 for the days of the month. The sales by item for each date is next to it in columns C thru H, so for Jul1 the date would be in b8 and the numbers would be in c8 thru H8. Below is how structured with total of six item columns. The formula needs to find the date and then count, in this case C8..H8 for July 1. B C D E F G H Date Item 1 Item 2 7/1/06 322.00 300.00 7/2/06 312.00 Tried to apply your formula to this without success. Thanks for the help. "gary m" wrote: I have an Excel summary sheet that retrieves daily data from departmental sheets based on date search (eg. TODAY()-1,etc.. I need a formula that will go to the sheet, search for the date and then "Count" the cells in columns next to the date that have data. I have used vlookup to find specific cells but can't seem to 'count' multiple cells/columns next to the date. As you can see, I am somewhat an inexperienced user. Thanks for the help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up date on another sheet and do count of active cells (column
if you know the names of the other sheets =COUNT(OFFSET(Sheet2!C1,MATCH(TODAY(),Sheet2!B2:B2 2),0,1,200)) if the other sheet is called sheet2 i have the rows may need adjusting as may rhe 200 maximum columns that could contain values regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560380 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up date on another sheet and do count of active cells (column
It was probably the 256 that caused the problem if you start in column B
against column a as I did, so reduce it =COUNT(OFFSET(Sheet2!B8,MATCH(TODAY(),Sheet2!B8:B3 9,0)-1,0,1,255)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "gary m" wrote in message ... Bob: Date only appears once. Dates are in column b only, b8 thru b39 for the days of the month. The sales by item for each date is next to it in columns C thru H, so for Jul1 the date would be in b8 and the numbers would be in c8 thru H8. Below is how structured with total of six item columns. The formula needs to find the date and then count, in this case C8..H8 for July 1. B C D E F G H Date Item 1 Item 2 7/1/06 322.00 300.00 7/2/06 312.00 Tried to apply your formula to this without success. Thanks for the help. "gary m" wrote: I have an Excel summary sheet that retrieves daily data from departmental sheets based on date search (eg. TODAY()-1,etc.. I need a formula that will go to the sheet, search for the date and then "Count" the cells in columns next to the date that have data. I have used vlookup to find specific cells but can't seem to 'count' multiple cells/columns next to the date. As you can see, I am somewhat an inexperienced user. Thanks for the help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up date on another sheet and do count of active cells (column
Bob:
The correction you provided re 255 columns was right, however the correct string turned out to be -1,1,1,255 - using 1 instead of 0. Using this string and testing it, the answers are correct. Still trying to understand whole string so can't tell why it works yet but that is correct string. Appreciate all the help. Thanks, Gary "gary m" wrote: I have an Excel summary sheet that retrieves daily data from departmental sheets based on date search (eg. TODAY()-1,etc.. I need a formula that will go to the sheet, search for the date and then "Count" the cells in columns next to the date that have data. I have used vlookup to find specific cells but can't seem to 'count' multiple cells/columns next to the date. As you can see, I am somewhat an inexperienced user. Thanks for the help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up date on another sheet and do count of active cells (column
Gary,
That is because my formula counted the date as well (yes I know, that is dumb <g), so you need to offset the start point by 1 column, which is what you mod did. The OFFSET arguments are - start cell - number of rows to offset - number of columns to offset - number of rows to reference - number of columns to reference so the formula =COUNT(OFFSET(Sheet2!B8,MATCH(TODAY(),Sheet2!B8:B3 9,0)-1,1,1,255)) starts at B8, MATCHes the date against the dates B8:B39 and uses that as the number of rows to offset, so it effectively starts at that number of rows past B8, offset 1 column (so as to not count the date), and then counts within a range of 1 row and 255 columns from that point. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "gary m" wrote in message ... Bob: The correction you provided re 255 columns was right, however the correct string turned out to be -1,1,1,255 - using 1 instead of 0. Using this string and testing it, the answers are correct. Still trying to understand whole string so can't tell why it works yet but that is correct string. Appreciate all the help. Thanks, Gary "gary m" wrote: I have an Excel summary sheet that retrieves daily data from departmental sheets based on date search (eg. TODAY()-1,etc.. I need a formula that will go to the sheet, search for the date and then "Count" the cells in columns next to the date that have data. I have used vlookup to find specific cells but can't seem to 'count' multiple cells/columns next to the date. As you can see, I am somewhat an inexperienced user. Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
Sum up columns in different sheet with error check | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Copying a column to a new sheet so that cells match. | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |