How do I pull a date from a separate worksheet using the IF functi
 

I'm working on a budget system using Excel 2003. I have one page set up as a
monthly summary, and another set up as a transaction list. I want to view
the date I paid a particular bill on the Monthly Summary worksheet, taking
that data from the Transaction List. I'm using a 2 digit numerical system to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first part was
"B:B=10", the second part was "A:A" and the third part was left blank. I
couldn't get it to work....am I approaching this incorrectly? Here's what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of that
transaction across to a column in my Monthly Summary worksheet to indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in advance.
As I'm sure you can tell, I've next to no experience with spreadsheets in
general and Excel in particular, and this has been quite and adventure.

Hi, Jerrod. You'll need INDEX and MATCH. If you move your Code column to be
column A instead, then you can use VLOOKUP, which is (IMHO) much easier.
http://www.officearticles.com/excel/...soft_excel.htm

And here's a "tutorial" on vlookup:
http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" <Jerrod wrote in message
...
I'm working on a budget system using Excel 2003. I have one page set up
as a
monthly summary, and another set up as a transaction list. I want to view
the date I paid a particular bill on the Monthly Summary worksheet, taking
that data from the Transaction List. I'm using a 2 digit numerical system
to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first part was
"B:B=10", the second part was "A:A" and the third part was left blank. I
couldn't get it to work....am I approaching this incorrectly? Here's what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of that
transaction across to a column in my Monthly Summary worksheet to indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in
advance.
As I'm sure you can tell, I've next to no experience with spreadsheets in
general and Excel in particular, and this has been quite and adventure.

Hi!

Here's something that you may not have thought about. You said you have 2
sheets, a Summary and one for transactions. If you're paying electric bills
wisdom tells me you're getting a bill and making a payment every month. So,
there will be more than 1 entry in the transaction list for electric bills.
Which one do you want?

Biff

"Anne Troy" wrote in message
...
Hi, Jerrod. You'll need INDEX and MATCH. If you move your Code column to
be
column A instead, then you can use VLOOKUP, which is (IMHO) much easier.
http://www.officearticles.com/excel/...soft_excel.htm

And here's a "tutorial" on vlookup:
http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" <Jerrod wrote in message
...
I'm working on a budget system using Excel 2003. I have one page set up
as a
monthly summary, and another set up as a transaction list. I want to
view
the date I paid a particular bill on the Monthly Summary worksheet,
taking
that data from the Transaction List. I'm using a 2 digit numerical
system
to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first part was
"B:B=10", the second part was "A:A" and the third part was left blank. I
couldn't get it to work....am I approaching this incorrectly? Here's
what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of that
transaction across to a column in my Monthly Summary worksheet to
indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in
advance.
As I'm sure you can tell, I've next to no experience with spreadsheets in
general and Excel in particular, and this has been quite and adventure.

Hey Biff,

Thanks for taking the time to respond. I didn't explain my system
thoroughly enough. I have two separate workbooks, one with monthly
"Transaction List" worksheets, and one with monthly "Monthly Summary"
worksheets. That way the transactions link only to their respective monthly
summary and I can montor individual monthly summaries over an extended period
of time. Did that explain your question?

Jerrod

"Biff" wrote:

Hi!

Here's something that you may not have thought about. You said you have 2
sheets, a Summary and one for transactions. If you're paying electric bills
wisdom tells me you're getting a bill and making a payment every month. So,
there will be more than 1 entry in the transaction list for electric bills.
Which one do you want?

Biff

"Anne Troy" wrote in message
...
Hi, Jerrod. You'll need INDEX and MATCH. If you move your Code column to
be
column A instead, then you can use VLOOKUP, which is (IMHO) much easier.
http://www.officearticles.com/excel/...soft_excel.htm

And here's a "tutorial" on vlookup:
http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" <Jerrod wrote in message
...
I'm working on a budget system using Excel 2003. I have one page set up
as a
monthly summary, and another set up as a transaction list. I want to
view
the date I paid a particular bill on the Monthly Summary worksheet,
taking
that data from the Transaction List. I'm using a 2 digit numerical
system
to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first part was
"B:B=10", the second part was "A:A" and the third part was left blank. I
couldn't get it to work....am I approaching this incorrectly? Here's
what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of that
transaction across to a column in my Monthly Summary worksheet to
indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in
advance.
As I'm sure you can tell, I've next to no experience with spreadsheets in
general and Excel in particular, and this has been quite and adventure.

Yep!

Biff

"Jerrod Mason" wrote in message
...
Hey Biff,

Thanks for taking the time to respond. I didn't explain my system
thoroughly enough. I have two separate workbooks, one with monthly
"Transaction List" worksheets, and one with monthly "Monthly Summary"
worksheets. That way the transactions link only to their respective
monthly
summary and I can montor individual monthly summaries over an extended
period
of time. Did that explain your question?

Jerrod

"Biff" wrote:

Hi!

Here's something that you may not have thought about. You said you have 2
sheets, a Summary and one for transactions. If you're paying electric
bills
wisdom tells me you're getting a bill and making a payment every month.
So,
there will be more than 1 entry in the transaction list for electric
bills.
Which one do you want?

Biff

"Anne Troy" wrote in message
...
Hi, Jerrod. You'll need INDEX and MATCH. If you move your Code column
to
be
column A instead, then you can use VLOOKUP, which is (IMHO) much
easier.
http://www.officearticles.com/excel/...soft_excel.htm

And here's a "tutorial" on vlookup:
http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" <Jerrod wrote in
message
...
I'm working on a budget system using Excel 2003. I have one page set
up
as a
monthly summary, and another set up as a transaction list. I want to
view
the date I paid a particular bill on the Monthly Summary worksheet,
taking
that data from the Transaction List. I'm using a 2 digit numerical
system
to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first part
was
"B:B=10", the second part was "A:A" and the third part was left blank.
I
couldn't get it to work....am I approaching this incorrectly? Here's
what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of that
transaction across to a column in my Monthly Summary worksheet to
indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in
advance.
As I'm sure you can tell, I've next to no experience with spreadsheets
in
general and Excel in particular, and this has been quite and
adventure.

Anne,

Thanks for responding...I looked into the VLOOKUP function, and it looks
like my transaction list would have to be sorted by Code in ascending order
if I wanted this to work. I'm hoping to sort it by Date of Purchase instead.
If I use the INDEX and MATCH functions, will that allow my Codes to be out
of order? If so, how would I go about implementing that? As I said, I'm
pretty new at this, and I looked up the functions, but they're a little
beyond my comprehension. Thanks again for your time.

Jerrod

"Anne Troy" wrote:

Hi, Jerrod. You'll need INDEX and MATCH. If you move your Code column to be
column A instead, then you can use VLOOKUP, which is (IMHO) much easier.
http://www.officearticles.com/excel/...soft_excel.htm

And here's a "tutorial" on vlookup:
http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" <Jerrod wrote in message
...
I'm working on a budget system using Excel 2003. I have one page set up
as a
monthly summary, and another set up as a transaction list. I want to view
the date I paid a particular bill on the Monthly Summary worksheet, taking
that data from the Transaction List. I'm using a 2 digit numerical system
to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first part was
"B:B=10", the second part was "A:A" and the third part was left blank. I
couldn't get it to work....am I approaching this incorrectly? Here's what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of that
transaction across to a column in my Monthly Summary worksheet to indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in
advance.
As I'm sure you can tell, I've next to no experience with spreadsheets in
general and Excel in particular, and this has been quite and adventure.

Please see the 4th argument. If you USE the 4th argument, there's no need to
sort.
I'm terrible with Index & Match, but I'll take a shot using Guerilla Data
Analysis Using Microsoft Excel (I edited it, I didn't write it, LOL). Here's
an excerpt. Let's see if it helps.

__________________________________________

=INDEX(A2:C99,3,2)

The above formula returns the third row and the second column from the range
A2:C99. This is one of those functions that seems really useless, right?

But let's see if we can use this to return a customer from our example:

=INDEX(C2:C7,OurRow#,1)

This goes through the revenue figures in C2:C7 and return a value from the
first column.

There is a MATCH function, which is similar to VLOOKUP. The MATCH function
finds a matching value in a range of values. Instead of returning the
results from another column like VLOOKUP, the MATCH function returns the
relative position of the match within the range.

=MATCH(12349,D2:D99,FALSE)

This formula returns 5 because 12349 is found in cell D6, which is the
fourth row of D2:D99.

The MATCH function is a great way to identify the OurRow# parameter for the
INDEX function above. If you combine these two functions, you get this
formula:

=INDEX(C2:C7,MATCH(B1,D2:D7,FALSE),1)


In English, this formula tells Excel to search through the order numbers in
D2:D7 looking for a match to the order number found in cell B10. When a
match is found, Excel should go to the same relative row number of C2:C7 to
return the result.

So, the answer is:

=INDEX(A2:A99,MATCH(12345,D2:D99,FALSE),1)

_________________________________________________



*******************
~Anne Troy

www.OfficeArticles.com



"Jerrod Mason" wrote in message
...
Anne,

Thanks for responding...I looked into the VLOOKUP function, and it looks
like my transaction list would have to be sorted by Code in ascending
order
if I wanted this to work. I'm hoping to sort it by Date of Purchase
instead.
If I use the INDEX and MATCH functions, will that allow my Codes to be
out
of order? If so, how would I go about implementing that? As I said, I'm
pretty new at this, and I looked up the functions, but they're a little
beyond my comprehension. Thanks again for your time.

Jerrod

"Anne Troy" wrote:

Hi, Jerrod. You'll need INDEX and MATCH. If you move your Code column to
be
column A instead, then you can use VLOOKUP, which is (IMHO) much easier.

http://www.officearticles.com/excel/...soft_excel.htm

And here's a "tutorial" on vlookup:

http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" <Jerrod wrote in message
...
I'm working on a budget system using Excel 2003. I have one page set
up
as a
monthly summary, and another set up as a transaction list. I want to
view
the date I paid a particular bill on the Monthly Summary worksheet,
taking
that data from the Transaction List. I'm using a 2 digit numerical
system
to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first part
was
"B:B=10", the second part was "A:A" and the third part was left blank.
I
couldn't get it to work....am I approaching this incorrectly? Here's
what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of that
transaction across to a column in my Monthly Summary worksheet to
indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in
advance.
As I'm sure you can tell, I've next to no experience with spreadsheets
in
general and Excel in particular, and this has been quite and
adventure.

Anne,

I used the VLOOKUP function with the 4th argument, and it worked perfectly.
Thanks so much for your help.

"Anne Troy" wrote:

Please see the 4th argument. If you USE the 4th argument, there's no need to
sort.
I'm terrible with Index & Match, but I'll take a shot using Guerilla Data
Analysis Using Microsoft Excel (I edited it, I didn't write it, LOL). Here's
an excerpt. Let's see if it helps.

__________________________________________

=INDEX(A2:C99,3,2)

The above formula returns the third row and the second column from the range
A2:C99. This is one of those functions that seems really useless, right?

But let's see if we can use this to return a customer from our example:

=INDEX(C2:C7,OurRow#,1)

This goes through the revenue figures in C2:C7 and return a value from the
first column.

There is a MATCH function, which is similar to VLOOKUP. The MATCH function
finds a matching value in a range of values. Instead of returning the
results from another column like VLOOKUP, the MATCH function returns the
relative position of the match within the range.

=MATCH(12349,D2:D99,FALSE)

This formula returns 5 because 12349 is found in cell D6, which is the
fourth row of D2:D99.

The MATCH function is a great way to identify the OurRow# parameter for the
INDEX function above. If you combine these two functions, you get this
formula:

=INDEX(C2:C7,MATCH(B1,D2:D7,FALSE),1)


In English, this formula tells Excel to search through the order numbers in
D2:D7 looking for a match to the order number found in cell B10. When a
match is found, Excel should go to the same relative row number of C2:C7 to
return the result.

So, the answer is:

=INDEX(A2:A99,MATCH(12345,D2:D99,FALSE),1)

_________________________________________________



*******************
~Anne Troy

www.OfficeArticles.com



"Jerrod Mason" wrote in message
...
Anne,

Thanks for responding...I looked into the VLOOKUP function, and it looks
like my transaction list would have to be sorted by Code in ascending
order
if I wanted this to work. I'm hoping to sort it by Date of Purchase
instead.
If I use the INDEX and MATCH functions, will that allow my Codes to be
out
of order? If so, how would I go about implementing that? As I said, I'm
pretty new at this, and I looked up the functions, but they're a little
beyond my comprehension. Thanks again for your time.

Jerrod

"Anne Troy" wrote:

Hi, Jerrod. You'll need INDEX and MATCH. If you move your Code column to
be
column A instead, then you can use VLOOKUP, which is (IMHO) much easier.

http://www.officearticles.com/excel/...soft_excel.htm

And here's a "tutorial" on vlookup:

http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" <Jerrod wrote in message
...
I'm working on a budget system using Excel 2003. I have one page set
up
as a
monthly summary, and another set up as a transaction list. I want to
view
the date I paid a particular bill on the Monthly Summary worksheet,
taking
that data from the Transaction List. I'm using a 2 digit numerical
system
to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first part
was
"B:B=10", the second part was "A:A" and the third part was left blank.
I
couldn't get it to work....am I approaching this incorrectly? Here's
what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of that
transaction across to a column in my Monthly Summary worksheet to
indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in
advance.
As I'm sure you can tell, I've next to no experience with spreadsheets
in
general and Excel in particular, and this has been quite and
adventure.

I think you'll also find yourself using VLOOKUP much more than index/match,
too. :) So happy I could help.
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" wrote in message
...
Anne,

I used the VLOOKUP function with the 4th argument, and it worked
perfectly.
Thanks so much for your help.

"Anne Troy" wrote:

Please see the 4th argument. If you USE the 4th argument, there's no
need to
sort.
I'm terrible with Index & Match, but I'll take a shot using Guerilla
Data
Analysis Using Microsoft Excel (I edited it, I didn't write it, LOL).
Here's
an excerpt. Let's see if it helps.

__________________________________________

=INDEX(A2:C99,3,2)

The above formula returns the third row and the second column from the
range
A2:C99. This is one of those functions that seems really useless, right?

But let's see if we can use this to return a customer from our example:

=INDEX(C2:C7,OurRow#,1)

This goes through the revenue figures in C2:C7 and return a value from
the
first column.

There is a MATCH function, which is similar to VLOOKUP. The MATCH
function
finds a matching value in a range of values. Instead of returning the
results from another column like VLOOKUP, the MATCH function returns the
relative position of the match within the range.

=MATCH(12349,D2:D99,FALSE)

This formula returns 5 because 12349 is found in cell D6, which is the
fourth row of D2:D99.

The MATCH function is a great way to identify the OurRow# parameter for
the
INDEX function above. If you combine these two functions, you get this
formula:

=INDEX(C2:C7,MATCH(B1,D2:D7,FALSE),1)


In English, this formula tells Excel to search through the order numbers
in
D2:D7 looking for a match to the order number found in cell B10. When a
match is found, Excel should go to the same relative row number of C2:C7
to
return the result.

So, the answer is:

=INDEX(A2:A99,MATCH(12345,D2:D99,FALSE),1)

_________________________________________________



*******************
~Anne Troy

www.OfficeArticles.com



"Jerrod Mason" wrote in message
...
Anne,

Thanks for responding...I looked into the VLOOKUP function, and it
looks
like my transaction list would have to be sorted by Code in ascending
order
if I wanted this to work. I'm hoping to sort it by Date of Purchase
instead.
If I use the INDEX and MATCH functions, will that allow my Codes to
be
out
of order? If so, how would I go about implementing that? As I said,
I'm
pretty new at this, and I looked up the functions, but they're a
little
beyond my comprehension. Thanks again for your time.

Jerrod

"Anne Troy" wrote:

Hi, Jerrod. You'll need INDEX and MATCH. If you move your Code
column to
be
column A instead, then you can use VLOOKUP, which is (IMHO) much
easier.


http://www.officearticles.com/excel/...soft_excel.htm

And here's a "tutorial" on vlookup:


http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Jerrod Mason" <Jerrod wrote in
message
...
I'm working on a budget system using Excel 2003. I have one page
set
up
as a
monthly summary, and another set up as a transaction list. I want
to
view
the date I paid a particular bill on the Monthly Summary
worksheet,
taking
that data from the Transaction List. I'm using a 2 digit
numerical
system
to
classify different bill categories. Example:

Date Code Desc. Price
14-Jun-04 10 Electricity 300.00

I tried an IF function to bring the date across where the first
part
was
"B:B=10", the second part was "A:A" and the third part was left
blank.
I
couldn't get it to work....am I approaching this incorrectly?
Here's
what
I'm looking for: when I enter a payment for a specific bill in my
Transaction List worksheet, is there a way to bring the date of
that
transaction across to a column in my Monthly Summary worksheet to
indicate
(1) that I paid the bill and (2) when I sent the check? Thanks in
advance.
As I'm sure you can tell, I've next to no experience with
spreadsheets
in
general and Excel in particular, and this has been quite and
adventure.