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.
Will that date appear once or more times on the other sheet?
Maybe something like
=COUNTA(OFFSET(Sheet2!A1,MATCH(TODAY(),Sheet2!A1:A1000,0)-1,0,1,256))
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"gary m" <gary m@discussions.microsoft.com> wrote in message
news:A50BD8CE-2A20-4844-B1C4-6BCC082A2057@microsoft.com...
> 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.
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.
if you know the names of the other sheets
=COUNT(OFFSET(Sheet2!C1,MATCH(TODAY(),Sheet2!B2:B22),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
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:B39,0)-1,0,1,255))
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"gary m" <garym@discussions.microsoft.com> wrote in message
news:25D52CD2-AF18-4906-BCE9-6E35AC58D062@microsoft.com...
> 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.
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.
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:B39,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" <garym@discussions.microsoft.com> wrote in message
news:2824511C-48F8-4FAF-8DA8-643BE1C19DC7@microsoft.com...
> 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.
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:B39,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" <garym@discussions.microsoft.com> wrote in message
news:2824511C-48F8-4FAF-8DA8-643BE1C19DC7@microsoft.com...
> 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.
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:B39,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" <garym@discussions.microsoft.com> wrote in message
news:2824511C-48F8-4FAF-8DA8-643BE1C19DC7@microsoft.com...
> 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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks