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?

*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?

The thing with your approach is that formatting does not alter the actual
value stored in the cell. A Lookup uses the actual value, ignoring the
formatting.

But here is how it could work:

Assuming you got "Today" in A1 and the months Jan - Dec in B1:M1

Introduce a helper row (in this example row 2) to limit the dates to their
actual months: formula in A2 =Month(A1), copy to M2

Your forecasts are now in row 3

your HLOOKUP in A3 would then read
=HLOOKUP(A2,B2:M3,2,FALSE)

"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?