conditional formulas with 12 conditions

[SPB2009]

This particular issue is a comparison of sorts...I want a column of formulas
to change with the month referenced in another cell.

I have a "now()" function formatted to show a two-digit number for the
month. I have 12 columns with month headings (Jan, Feb, etc.); underneath the
month headings, I'll store the reconciled quantities that get compared to the
current quantities (if the change between the current & reconciled quantities
is greater than 10%, a format change occurs & the cell displays the lower of
the two quantities). I have a list of what could be hundreds of items with a
corresponding current quantity. I want to reconcile between the current
quantity & the reconciled quantity each month but I want to keep the history
of the reconciled quantities too. I'm envisioning a column with formulas that
reference the now() function & pull in the appropriate value from the columns
with current month's reconcile data.

Basically, if month = 02, use reconciledata from column Feb for specific
item (continue to fill this formula for all items; change each month)

 

[SPB2009]

And, for the record, I could just insert a new month column each month, but I
enjoy complication sometimes. If the replies aren't fast & furious, the
former might be just what I'll do.

 

[Pete_UK]

Can you just sketch out what your data looks like? I think you have
something like this:

Name Jan Feb Mar Apr
item1 x1 y1 z1
item2 x2 y2 z2
item3 x3 y2 z3

etc.

Is this on one sheet for one year, a similar sheet for another year,
with another sheet where you want the results? Can you describe again
what you want to achieve with this data?

I'm a bit confused.

Pete

 

[SPB2009]

I never realized how difficult it is to verbalize these sheets (much truth in
a picture being worth a bunch of words).
Yes, this is one sheet for the entire year.

now()
Name current qty "if" january feb
item1 x1 y1 z1
item2 x2 y2 z2
item3 x3 y3 z3

the "if" column: if now()=1 use y(1,2,3...), if now()=2 use z(1,2,3...)

and the if column's formulas should change with the now function's value
(downward fill for the column). also, the if column compare's the current
quantity to the reconcile quantity; if column will display lowest of two
quantities if difference between current & reconcile (monthlys) is >10%of
current qty.

 

[Pete_UK]

Okay, according to my mock-up you have a NOW() function in A1
(although I've use =TODAY() as you don't seem to be interested in the
time element), and also in column A you have the names. Your current
quantity is in column B, so that your "IF" column is C. You then have
columns D to O for your monthly quantities, with the headers on row 2.
Put this formula in C3:

=MIN(INDEX(D3:O3,MONTH(A$1)),B3)

and copy it down. It will give you the lower of the two values from
the current quantity and the quantity in the appropriate month column.
I didn't really understand what your last sentence meant - do you want
to display the lowest or not?

Hope this helps.

Pete

 

[SPB2009]

That worked perfectly; I'd send u $5 if I had your address.
And, yes, I wanted/want the lowest quant displayed.

 

[Pete_UK]

Glad it worked for you - thanks for feeding back.

Pete