Hi, I am building a model that I am trying to 'automate' as well as possible
to reduce running time of scenario work. The data I utilise is in monthly
buckets, so for example I have a January Forecast, February Forecast, March,
April, May etc etc etc

What I would like to do is have a HLOOKUP table with the various forecasts
in, that will drive the lookup cells to have the forecast in for THIS month..

So for example: ("March" being produced by an =TODAY or equivalent)

"March" | January February March April
May
3 | 1 2 3
4 5

So I have got a sheet in which I have an =TODAY formula, with the cell
formatted to mmmm-yy then the reference table with the dates in 01/01/2010,
01/02/2010, 01/03/2010 but formatted mmmm-yy as well - but for some reason
the Reference cell isn't picking anything up? Is this formatting or is there
another way round it?

The caveat, by the way, is my excel based optimisation tool won't operate a
linear solve with =IF statements controlling the data - so the solution can't
involve an IF

Any ideas?

Eureka - I've got it working. I had to convert the output of an =TODAY cell
by doing the =TEXT formula in another, then running the reference of the
HLOOKUP to the name of that cell, brings the correct results.

Thanks for the help

GD

"GD" wrote:

Very strange result from this, in principle it works however even though this
month is March the results are pulling out data for August?

"stumac" wrote:

Hi GD, formatting the cell does not change the value of the today() function,
instead try changing it with the text function,

i.e.

=HLOOKUP(TEXT(TODAY(),"mmmm"),myrange,2,FALSE)

Hth
Stu



"GD" wrote:

*sigh*

You get the idea. The reference is 'March' and within the table the result
for March is 3. It's getting the reference of 'March' from an =TODAY
statement that will bring a result from the reference table.

Thanks

"GD" wrote:

Formatting got messed up...

The example should read:

"March" | January February March
3 | 1 2
3



"GD" wrote:

Hi, I am building a model that I am trying to 'automate' as well as possible
to reduce running time of scenario work. The data I utilise is in monthly
buckets, so for example I have a January Forecast, February Forecast, March,
April, May etc etc etc

What I would like to do is have a HLOOKUP table with the various forecasts
in, that will drive the lookup cells to have the forecast in for THIS month..

So for example: ("March" being produced by an =TODAY or equivalent)

"March" | January February March April
May
3 | 1 2 3
4 5

So I have got a sheet in which I have an =TODAY formula, with the cell
formatted to mmmm-yy then the reference table with the dates in 01/01/2010,
01/02/2010, 01/03/2010 but formatted mmmm-yy as well - but for some reason
the Reference cell isn't picking anything up? Is this formatting or is there
another way round it?

The caveat, by the way, is my excel based optimisation tool won't operate a
linear solve with =IF statements controlling the data - so the solution can't
involve an IF

Any ideas?

Hi

Try
=INDEX(myrange,2,MONTH(TODAY()+1))
assuming myrange is something like B1:M2

--
Regards
Roger Govier

GD wrote:
Very strange result from this, in principle it works however even though this
month is March the results are pulling out data for August?

"stumac" wrote:

Hi GD, formatting the cell does not change the value of the today() function,
instead try changing it with the text function,

i.e.

=HLOOKUP(TEXT(TODAY(),"mmmm"),myrange,2,FALSE)

Hth
Stu



"GD" wrote:

*sigh*

You get the idea. The reference is 'March' and within the table the result
for March is 3. It's getting the reference of 'March' from an =TODAY
statement that will bring a result from the reference table.

Thanks

"GD" wrote:

Formatting got messed up...

The example should read:

"March" | January February March
3 | 1 2
3



"GD" wrote:

Hi, I am building a model that I am trying to 'automate' as well as possible
to reduce running time of scenario work. The data I utilise is in monthly
buckets, so for example I have a January Forecast, February Forecast, March,
April, May etc etc etc

What I would like to do is have a HLOOKUP table with the various forecasts
in, that will drive the lookup cells to have the forecast in for THIS month..

So for example: ("March" being produced by an =TODAY or equivalent)

"March" | January February March April
May
3 | 1 2 3
4 5

So I have got a sheet in which I have an =TODAY formula, with the cell
formatted to mmmm-yy then the reference table with the dates in 01/01/2010,
01/02/2010, 01/03/2010 but formatted mmmm-yy as well - but for some reason
the Reference cell isn't picking anything up? Is this formatting or is there
another way round it?

The caveat, by the way, is my excel based optimisation tool won't operate a
linear solve with =IF statements controlling the data - so the solution can't
involve an IF

Any ideas?