Hlookup and match or index usage -- incrementing to next cell

[knox5731]

I have a formula that works great, I just can't figure out how to get
it to move dynamically to the next cell


=HLOOKUP(startmo,PipelineNA,15)
so it looks for the start month of october 2008, in the PipelineNA
data range, adn then goes down 15 rows and displays the value.

However, I need to take this formula and copy it out for 12 months....
So it needs to show the November 2008 data in row 15....How do I get
it to increment dynamically (without having to setup/hard code a range
for every month value)?

Thoughts?
Thanks!

 

[tsides]

Remove the "startmo" named range and replace it with the cell
reference (A2, or whatever cell October 2008 is in). Then copy down
or across. For example, month in column A and corresponding formula
in column B (which you can enter in B2 and copy down, and Excel will
update the references for you):

Month Formula
October 2008 =HLOOKUP(A2,PipelineNA,15)
November 2008 =HLOOKUP(A3,PipelineNA,15)
December 2008 =HLOOKUP(A4,PipelineNA,15)
January 2009 =HLOOKUP(A5,PipelineNA,15)

 

[knox5731]

Remove the "startmo" named range and replace it with the cell
reference (A2, or whatever cell October 2008 is in).  Then copy down
or across.  For example, month in column A and corresponding formula
in column B (which you can enter in B2 and copy down, and Excel will
update the references for you):

Month   Formula
October 2008    =HLOOKUP(A2,PipelineNA,15)
November 2008   =HLOOKUP(A3,PipelineNA,15)
December 2008   =HLOOKUP(A4,PipelineNA,15)
January 2009    =HLOOKUP(A5,PipelineNA,15)

Any way to do it dynamically? As I go from month-to month my startmo
will change, and I don't want to have to change all my formulas every
month.....

 

[tsides]

I don't understand the structure of your spreadsheet, then. Can you
tell me what the cells look like where you're storing this month that
needs to change dynamically? Or send me the spreadsheet: tsides At
intelligentsystemsconsulting dotcom

 

[knox5731]

I don't understand the structure of your spreadsheet, then.  Can you
tell me what the cells look like where you're storing this month that
needs to change dynamically?  Or send me the spreadsheet:  tsides At
intelligentsystemsconsulting dotcom

a b c d e f g
1 start mo 8-Jan
2
3 PipelineNA range 8-Jan 8-Feb 8-Mar 8-Apr 8-May 8-Jun
4 Data values 3 4 5 6 7 8

So B1 is the range "startmo"
a3:h4 is defined as range "PipelineNA"
The formula in c4 is = hlookup(startmo,PipelineNA,1)
However, I don't know how to copy that across to d4, d5, d6 while
keeping a range value for the startmo. I do not want to hard code
hlookup starting points as next month my startmo range will say Feb
-08 and I won't want to view Jan 08 at all at that point......

 

[tsides]

I think the description of the problem is confusing, or you might have
the whole structure of your spreadsheet wrong. Let's get that right,
and then we can fix the formula.

In cell B1 you enter the start month (08-Jan).
In cell C1, you have your HLOOKUP function, where you want to read a
value from your data table. (You said it's in C4, but I'm guessing
that was a mistake).
The data is stored in columns A through H and it has lots of rows
(since you mentioned you were reading from row 15 in your original
post).
Column A is January, Column B is February, Column C is March, etc.
through Column H.
What is in the different rows, then?

Please confirm that you want to be able to change the date in B1 and
have C1 show the proper value based on the data, right? The HLOOKUP
you have will do that... but you're saying that you want it to pick
data from a different row, too?

 

[knox5731]

I think the description of the problem is confusing, or you might have
the whole structure of your spreadsheet wrong.  Let's get that right,
and then we can fix the formula.

In cell B1 you enter the start month (08-Jan).
In cell C1, you have your HLOOKUP function, where you want to read a
value from your data table.  (You said it's in C4, but I'm guessing
that was a mistake).
The data is stored in columns A through H and it has lots of rows
(since you mentioned you were reading from row 15 in your original
post).
Column A is January, Column B is February, Column C is March, etc.
through Column H.
What is in the different rows, then?

Please confirm that you want to be able to change the date in B1 and
have C1 show the proper value based on the data, right?  The HLOOKUP
you have will do that... but you're saying that you want it to pick
data from a different row, too?

Sorry for the Offset in the rows in what I tried to describe--
basically I would like a modified formula of
hlookup(startmo, pipelineNA, 15) in one cell, then the next cell I'd
liek to have =hlookup(startmo+1, pipelineNA, 15).....however that
doesn't work...I want it to increment the startmonth by one, and then
show the row 15 data incremented by one column as well..

 

[tsides]

That makes a lot more sense. If, in the next cell, you want to
increment the MONTH by one, then use:
HLOOKUP(DATE(YEAR(startmo),MONTH(startmo)+1,DAY(startmo)),PipelineNA,
2)

This is going to be a problem when you want to copy it over again, and
change +1 to +2. You could manually make that change, or you could
complicate the formula and calculate the number of columns you are
away from startmo....

Other though: What about structuring your spreadsheet like this:
1 a b c d e f g
2 Row 8-Jan 8-Feb 8-Mar 8-Apr 8-May 8-Jun
3 1 3 4 5 6 7 8
4
5 8-Jan 8-Feb 8-Mar 8-Apr 8-May 8-Jun
6 3 4 5 6 7 8

put "15" in cell a3 instead of my "1"
cell B3 is =OFFSET(C5,$A3,0), and then copied across the row, and then
that'll give you the same row's data for each month.
also, if cell b1 is 08-Jan, I'd make cell c1
=DATE(YEAR(C2),MONTH(C2)+1,DAY(C2)) and copy across the row