I have two workbooks that contain some of the same information that I would
like to partially combine.

My first workbook has account number, account name, and billing month as the
column headings. Each row contains the account number & name, and day of the
month that I received each report for billing.
ie:
Acct num | acct name | jan billing | feb billing
123456 | abc company | 12/8 | 1/26
And so on for the whole year.

My other sheet lists the account number and name and a column titled "last
report received", as well as other information, not specifically related to
the report.

I'd like a formula to take the most recent report date from the 1st book and
plug it into the second book, corresponding to the same row. I'd also like
it to automatically update itself (if it can...). So when I enter a report
date in December in the first book, it would list 12/04 as the most recent
report date in the second book.

I hope that made sense.... Thank you in advance for your help.
Darcie

Hi
try
=OFFSET('sheet1'!$A$1,MATCH(A1,'sheet1'!$A$1:$A$10 0,0),COUNTA(OFFSET('s
heet1'!$A$1:$X$1,MATCH(A1,'sheet1'!$A$1:$A$100,0))-1)

to return the relevant last reporting date value



--
Regards
Frank Kabel
Frankfurt, Germany

"o1darcie1o" schrieb im
Newsbeitrag ...
I have two workbooks that contain some of the same information that I
would
like to partially combine.

My first workbook has account number, account name, and billing month
as the
column headings. Each row contains the account number & name, and
day of the
month that I received each report for billing.
ie:
Acct num | acct name | jan billing | feb billing
123456 | abc company | 12/8 | 1/26
And so on for the whole year.

My other sheet lists the account number and name and a column titled
"last
report received", as well as other information, not specifically
related to
the report.

I'd like a formula to take the most recent report date from the 1st
book and
plug it into the second book, corresponding to the same row. I'd
also like
it to automatically update itself (if it can...). So when I enter a
report
date in December in the first book, it would list 12/04 as the most
recent
report date in the second book.

I hope that made sense.... Thank you in advance for your help.
Darcie

Frank:
I tried that formula; I'm not great with them, and there's a ) missing, so I
tried adding one in. Didn't work.
Column headers are in Row 1. My sheet with Jan-Dec dates is named Summary.
Dates are in E2-P2, E103-P103 (all January is down; per account is across).
The cell I'm trying to fill is D2 on sheet named Active.
Here's what I used:
=OFFSET('Summary'!$A$2,MATCH(A2,'Summary'!$A$2:$A$ 103,0),COUNTA(OFFSET('Summary'!$A$2:$P$2,1,1),MATC H(A1,'Summary'!$A$2:$A$103,0))-1)

Please help!!!
Thanks
Darcie

"Frank Kabel" wrote:

Hi
try
=OFFSET('sheet1'!$A$1,MATCH(A1,'sheet1'!$A$1:$A$10 0,0),COUNTA(OFFSET('s
heet1'!$A$1:$X$1,MATCH(A1,'sheet1'!$A$1:$A$100,0))-1)

to return the relevant last reporting date value



--
Regards
Frank Kabel
Frankfurt, Germany

"o1darcie1o" schrieb im
Newsbeitrag ...
I have two workbooks that contain some of the same information that I
would
like to partially combine.

My first workbook has account number, account name, and billing month
as the
column headings. Each row contains the account number & name, and
day of the
month that I received each report for billing.
ie:
Acct num | acct name | jan billing | feb billing
123456 | abc company | 12/8 | 1/26
And so on for the whole year.

My other sheet lists the account number and name and a column titled
"last
report received", as well as other information, not specifically
related to
the report.

I'd like a formula to take the most recent report date from the 1st
book and
plug it into the second book, corresponding to the same row. I'd
also like
it to automatically update itself (if it can...). So when I enter a
report
date in December in the first book, it would list 12/04 as the most
recent
report date in the second book.

I hope that made sense.... Thank you in advance for your help.
Darcie

Hi
sorry,
try:
=OFFSET('sheet1'!$A$1,MATCH(A1,'sheet1'!$A$1:$A$10 0,0),COUNTA(OFFSET('s
heet1'!$A$1:$X$1,MATCH(A1,'sheet1'!$A$1:$A$100,0)-1,0)))

--
Regards
Frank Kabel
Frankfurt, Germany

"o1darcie1o" schrieb im
Newsbeitrag ...
Frank:
I tried that formula; I'm not great with them, and there's a )
missing, so I
tried adding one in. Didn't work.
Column headers are in Row 1. My sheet with Jan-Dec dates is named
Summary.
Dates are in E2-P2, E103-P103 (all January is down; per account is
across).
The cell I'm trying to fill is D2 on sheet named Active.
Here's what I used:

=OFFSET('Summary'!$A$2,MATCH(A2,'Summary'!$A$2:$A$ 103,0),COUNTA(OFFSET(
'Summary'!$A$2:$P$2,1,1),MATCH(A1,'Summary'!$A$2:$ A$103,0))-1)

Please help!!!
Thanks
Darcie

"Frank Kabel" wrote:

Hi
try

=OFFSET('sheet1'!$A$1,MATCH(A1,'sheet1'!$A$1:$A$10 0,0),COUNTA(OFFSET('s
heet1'!$A$1:$X$1,MATCH(A1,'sheet1'!$A$1:$A$100,0))-1)

to return the relevant last reporting date value



--
Regards
Frank Kabel
Frankfurt, Germany

"o1darcie1o" schrieb im
Newsbeitrag
...
I have two workbooks that contain some of the same information
that I
would
like to partially combine.

My first workbook has account number, account name, and billing
month
as the
column headings. Each row contains the account number & name,
and
day of the
month that I received each report for billing.
ie:
Acct num | acct name | jan billing | feb billing
123456 | abc company | 12/8 | 1/26
And so on for the whole year.

My other sheet lists the account number and name and a column
titled
"last
report received", as well as other information, not specifically
related to
the report.

I'd like a formula to take the most recent report date from the
1st
book and
plug it into the second book, corresponding to the same row. I'd
also like
it to automatically update itself (if it can...). So when I
enter a
report
date in December in the first book, it would list 12/04 as the
most
recent
report date in the second book.

I hope that made sense.... Thank you in advance for your help.
Darcie

Hi Frank:
We have two posts going here!! I tried this formula also, and its returning
all kinds of weird numbers & I can't figure where its pulling them from!! I'm
also getting #N/A on lines where there is definitely a date on the sheet its
referencing.
Any other ideas?
Thanks!

"Frank Kabel" wrote:

Hi
sorry,
try:
=OFFSET('sheet1'!$A$1,MATCH(A1,'sheet1'!$A$1:$A$10 0,0),COUNTA(OFFSET('s
heet1'!$A$1:$X$1,MATCH(A1,'sheet1'!$A$1:$A$100,0)-1,0)))

--
Regards
Frank Kabel
Frankfurt, Germany

"o1darcie1o" schrieb im
Newsbeitrag ...
Frank:
I tried that formula; I'm not great with them, and there's a )
missing, so I
tried adding one in. Didn't work.
Column headers are in Row 1. My sheet with Jan-Dec dates is named
Summary.
Dates are in E2-P2, E103-P103 (all January is down; per account is
across).
The cell I'm trying to fill is D2 on sheet named Active.
Here's what I used:

=OFFSET('Summary'!$A$2,MATCH(A2,'Summary'!$A$2:$A$ 103,0),COUNTA(OFFSET(
'Summary'!$A$2:$P$2,1,1),MATCH(A1,'Summary'!$A$2:$ A$103,0))-1)

Please help!!!
Thanks
Darcie

"Frank Kabel" wrote:

Hi
try

=OFFSET('sheet1'!$A$1,MATCH(A1,'sheet1'!$A$1:$A$10 0,0),COUNTA(OFFSET('s
heet1'!$A$1:$X$1,MATCH(A1,'sheet1'!$A$1:$A$100,0))-1)

to return the relevant last reporting date value



--
Regards
Frank Kabel
Frankfurt, Germany

"o1darcie1o" schrieb im
Newsbeitrag
...
I have two workbooks that contain some of the same information
that I
would
like to partially combine.

My first workbook has account number, account name, and billing
month
as the
column headings. Each row contains the account number & name,
and
day of the
month that I received each report for billing.
ie:
Acct num | acct name | jan billing | feb billing
123456 | abc company | 12/8 | 1/26
And so on for the whole year.

My other sheet lists the account number and name and a column
titled
"last
report received", as well as other information, not specifically
related to
the report.

I'd like a formula to take the most recent report date from the
1st
book and
plug it into the second book, corresponding to the same row. I'd
also like
it to automatically update itself (if it can...). So when I
enter a
report
date in December in the first book, it would list 12/04 as the
most
recent
report date in the second book.

I hope that made sense.... Thank you in advance for your help.
Darcie