vlookup and dates

I have two problems,

First I need to compare a month in a column with a date in a row.

For example: column b has dates like this 7/06, 8/06, etc.

In another sheet, i have the months across the top like January,
February. (these are in different columns).

I need to compare column b month with the month in the other sheet. If
it is true, then I need to take a value from column k (on the same
sheet as column b) and put it in that corresponding column. Like the
column for july would have an amount in it.

Second, I have column c that has number of months.

What I basically need is an if statement and lookup that will look at a
column with dates and put an amount from column k in the corresponding
column in another worksheet. At that point, it will look at the number
of months column and insert the amount in however many columns it says.


For example: column b, is 7/06. Column J (another sheet) is July, and
column k is 87,500 and column c is 6. It will look something like this:

January, February, March, April, May, June, July, Aug, Sept,
Oct, Nov, Dec
87500
87500 87500, 87500, 87500,87500


My lookup is like this:


=VLOOKUP(Sheet2!D4,startdate,1,FALSE)

but this gives me '38904'.

any help would be appreciated.

Try something like this:

=OFFSET(Sheet1!$A$2,,MATCH(I$3,Sheet1!$B$33:$M$33))

MATCH will help you determine the column the data is in.
OFFSET will grab the cell that is a defined number of rows and columns from
A2 in this case.

"trobinson" wrote:

> I have two problems,
>
> First I need to compare a month in a column with a date in a row.
>
> For example: column b has dates like this 7/06, 8/06, etc.
>
> In another sheet, i have the months across the top like January,
> February. (these are in different columns).
>
> I need to compare column b month with the month in the other sheet. If
> it is true, then I need to take a value from column k (on the same
> sheet as column b) and put it in that corresponding column. Like the
> column for july would have an amount in it.
>
> Second, I have column c that has number of months.
>
> What I basically need is an if statement and lookup that will look at a
> column with dates and put an amount from column k in the corresponding
> column in another worksheet. At that point, it will look at the number
> of months column and insert the amount in however many columns it says.
>
>
> For example: column b, is 7/06. Column J (another sheet) is July, and
> column k is 87,500 and column c is 6. It will look something like this:
>
> January, February, March, April, May, June, July, Aug, Sept,
> Oct, Nov, Dec
> 87500
> 87500 87500, 87500, 87500,87500
>
>
> My lookup is like this:
>
>
> =VLOOKUP(Sheet2!D4,startdate,1,FALSE)
>
> but this gives me '38904'.
>
> any help would be appreciated.
>
>

Thanks Barb,
I think I understand how to grab the number from the column, but how do
I put this in a macro to insert it into the next columns (up to the
amount of column k). I have this to pull in my date, but don't know how
to connect the two statements.

=IF(J2=MONTH(Sheet2!D4),Sheet2!K4,0)


And this to pull the amount of number of months.
=OFFSET(Sheet4!$D$4,0,MATCH(Sheet2!J4,devmos,Sheet4!$D$2:$O$2))

How do I combine the two together in a macro?


Barb Reinhardt wrote:
> Try something like this:
>
> =OFFSET(Sheet1!$A$2,,MATCH(I$3,Sheet1!$B$33:$M$33))
>
> MATCH will help you determine the column the data is in.
> OFFSET will grab the cell that is a defined number of rows and columns from
> A2 in this case.
>
> "trobinson" wrote:
>
> > I have two problems,
> >
> > First I need to compare a month in a column with a date in a row.
> >
> > For example: column b has dates like this 7/06, 8/06, etc.
> >
> > In another sheet, i have the months across the top like January,
> > February. (these are in different columns).
> >
> > I need to compare column b month with the month in the other sheet. If
> > it is true, then I need to take a value from column k (on the same
> > sheet as column b) and put it in that corresponding column. Like the
> > column for july would have an amount in it.
> >
> > Second, I have column c that has number of months.
> >
> > What I basically need is an if statement and lookup that will look at a
> > column with dates and put an amount from column k in the corresponding
> > column in another worksheet. At that point, it will look at the number
> > of months column and insert the amount in however many columns it says.
> >
> >
> > For example: column b, is 7/06. Column J (another sheet) is July, and
> > column k is 87,500 and column c is 6. It will look something like this:
> >
> > January, February, March, April, May, June, July, Aug, Sept,
> > Oct, Nov, Dec
> > 87500
> > 87500 87500, 87500, 87500,87500
> >
> >
> > My lookup is like this:
> >
> >
> > =VLOOKUP(Sheet2!D4,startdate,1,FALSE)
> >
> > but this gives me '38904'.
> >
> > any help would be appreciated.
> >
> >

The match part of the formula will find what column in the worksheet that the
date is in. Review the documentation on MATCH.

Then the OFFSET part will help get the data from the worksheet from that
location.

"trobinson" wrote:

> Thanks Barb,
> I think I understand how to grab the number from the column, but how do
> I put this in a macro to insert it into the next columns (up to the
> amount of column k). I have this to pull in my date, but don't know how
> to connect the two statements.
>
> =IF(J2=MONTH(Sheet2!D4),Sheet2!K4,0)
>
>
> And this to pull the amount of number of months.
> =OFFSET(Sheet4!$D$4,0,MATCH(Sheet2!J4,devmos,Sheet4!$D$2:$O$2))
>
> How do I combine the two together in a macro?
>
>
> Barb Reinhardt wrote:
> > Try something like this:
> >
> > =OFFSET(Sheet1!$A$2,,MATCH(I$3,Sheet1!$B$33:$M$33))
> >
> > MATCH will help you determine the column the data is in.
> > OFFSET will grab the cell that is a defined number of rows and columns from
> > A2 in this case.
> >
> > "trobinson" wrote:
> >
> > > I have two problems,
> > >
> > > First I need to compare a month in a column with a date in a row.
> > >
> > > For example: column b has dates like this 7/06, 8/06, etc.
> > >
> > > In another sheet, i have the months across the top like January,
> > > February. (these are in different columns).
> > >
> > > I need to compare column b month with the month in the other sheet. If
> > > it is true, then I need to take a value from column k (on the same
> > > sheet as column b) and put it in that corresponding column. Like the
> > > column for july would have an amount in it.
> > >
> > > Second, I have column c that has number of months.
> > >
> > > What I basically need is an if statement and lookup that will look at a
> > > column with dates and put an amount from column k in the corresponding
> > > column in another worksheet. At that point, it will look at the number
> > > of months column and insert the amount in however many columns it says.
> > >
> > >
> > > For example: column b, is 7/06. Column J (another sheet) is July, and
> > > column k is 87,500 and column c is 6. It will look something like this:
> > >
> > > January, February, March, April, May, June, July, Aug, Sept,
> > > Oct, Nov, Dec
> > > 87500
> > > 87500 87500, 87500, 87500,87500
> > >
> > >
> > > My lookup is like this:
> > >
> > >
> > > =VLOOKUP(Sheet2!D4,startdate,1,FALSE)
> > >
> > > but this gives me '38904'.
> > >
> > > any help would be appreciated.
> > >
> > >
>
>

I guess I need to rephrase my question. However, I don't think I need a
macro now. I have looked at Chip Pearson's examples and I found a
similar item using the dates.

I need to modify his example so that instead of putting the amount of
days in the each months column, I would put the cost (some other cell)
that would stretch out until the end of the project (enddate)..

Here is the example I found:
http://www.cpearson.com/excel/distribdates.htm

I now have a column that computes the end date of the project. This is
the cell I want the month column to reference.

Thanks again

Barb Reinhardt wrote:
> The match part of the formula will find what column in the worksheet that the
> date is in. Review the documentation on MATCH.
>
> Then the OFFSET part will help get the data from the worksheet from that
> location.
>
> "trobinson" wrote:
>
> > Thanks Barb,
> > I think I understand how to grab the number from the column, but how do
> > I put this in a macro to insert it into the next columns (up to the
> > amount of column k). I have this to pull in my date, but don't know how
> > to connect the two statements.
> >
> > =IF(J2=MONTH(Sheet2!D4),Sheet2!K4,0)
> >
> >
> > And this to pull the amount of number of months.
> > =OFFSET(Sheet4!$D$4,0,MATCH(Sheet2!J4,devmos,Sheet4!$D$2:$O$2))
> >
> > How do I combine the two together in a macro?
> >
> >
> > Barb Reinhardt wrote:
> > > Try something like this:
> > >
> > > =OFFSET(Sheet1!$A$2,,MATCH(I$3,Sheet1!$B$33:$M$33))
> > >
> > > MATCH will help you determine the column the data is in.
> > > OFFSET will grab the cell that is a defined number of rows and columns from
> > > A2 in this case.
> > >
> > > "trobinson" wrote:
> > >
> > > > I have two problems,
> > > >
> > > > First I need to compare a month in a column with a date in a row.
> > > >
> > > > For example: column b has dates like this 7/06, 8/06, etc.
> > > >
> > > > In another sheet, i have the months across the top like January,
> > > > February. (these are in different columns).
> > > >
> > > > I need to compare column b month with the month in the other sheet. If
> > > > it is true, then I need to take a value from column k (on the same
> > > > sheet as column b) and put it in that corresponding column. Like the
> > > > column for july would have an amount in it.
> > > >
> > > > Second, I have column c that has number of months.
> > > >
> > > > What I basically need is an if statement and lookup that will look at a
> > > > column with dates and put an amount from column k in the corresponding
> > > > column in another worksheet. At that point, it will look at the number
> > > > of months column and insert the amount in however many columns it says.
> > > >
> > > >
> > > > For example: column b, is 7/06. Column J (another sheet) is July, and
> > > > column k is 87,500 and column c is 6. It will look something like this:
> > > >
> > > > January, February, March, April, May, June, July, Aug, Sept,
> > > > Oct, Nov, Dec
> > > > 87500
> > > > 87500 87500, 87500, 87500,87500
> > > >
> > > >
> > > > My lookup is like this:
> > > >
> > > >
> > > > =VLOOKUP(Sheet2!D4,startdate,1,FALSE)
> > > >
> > > > but this gives me '38904'.
> > > >
> > > > any help would be appreciated.
> > > >
> > > >
> >
> >