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)

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.

"SPB2009" wrote:

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)

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

On Feb 3, 8:31*pm, SPB2009 wrote:
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.



"SPB2009" wrote:
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)- Hide quoted text -

- Show quoted text -

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" wrote:

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

Name Jan Feb Mar
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

On Feb 3, 8:31 pm, SPB2009 wrote:
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.



"SPB2009" wrote:
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)- Hide quoted text -

- Show quoted text -

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


On Feb 4, 9:29*pm, SPB2009 wrote:
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" wrote:
Can you just sketch out what your data looks like? I think you have
something like this:

Name * * Jan * *Feb * *Mar
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

On Feb 3, 8:31 pm, SPB2009 wrote:
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.

"SPB2009" wrote:
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)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

"Pete_UK" wrote:

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


On Feb 4, 9:29 pm, SPB2009 wrote:
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" wrote:
Can you just sketch out what your data looks like? I think you have
something like this:

Name Jan Feb Mar
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

On Feb 3, 8:31 pm, SPB2009 wrote:
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.

"SPB2009" wrote:
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)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Glad it worked for you - thanks for feeding back.

Pete

On Feb 5, 4:12*pm, SPB2009 wrote:
That worked perfectly; I'd send u $5 if I had your address.
And, yes, I wanted/want the lowest quant displayed.



"Pete_UK" wrote:
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

On Feb 4, 9:29 pm, SPB2009 wrote:
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" wrote:
Can you just sketch out what your data looks like? I think you have
something like this:

Name * * Jan * *Feb * *Mar
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

On Feb 3, 8:31 pm, SPB2009 wrote:
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.

"SPB2009" wrote:
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)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -