Variable equations help please

C

Custermd

Could someone help me with this? On my XL 2003 wb I have two of several
sheets. On one of the sheets (C"Monthly inv") it has my facilities
monthly inventory. On the other sheet (Monthly order req forms) it has
a series of columns that contain formulas specific to the current date
and the date that the data on (C"Monthly inv") was entered.
So what I am really trying to ask is there a way in xl for a formula in
(Monthly order req forms) to search and find a specific column {in
reference to the current date} in (C"Monthly inv") and change all the
equations in the (Monthly order req forms) W.S.?

Example, say the date is 6Feb06 a formula in W.S. (Monthly order req
forms) would search row c12:n13 in W.S. (C"Monthly inv") and find that
the date corresponds to column J. Thus all the equations in the W.S. or
column would change its letter variable to j.
 
D

Dav

Yes although it is not entirely clear from your post what you require
You can reach an answer using the match and Offset functions

Match(value,array,0) finds the position of an exact match

eg if A1:d1 contain 10 20 30 40 and a2 contains 30

match(a2,a1:d1,0) returns 3

Offset(cell,rows,columns)

Returns the cell offset by that many rows and columns!
so Offset(b3,0,3) returns e3

as in the above example offset(b3,0,match(a2,a1:d1,0))

I hope that gives you something to go on, a similar function to th
above needs to be incorportated in your formula. Although I have use
numbers it will work with dates

Regards

Da
 
C

Custermd

Dav said:
Yes although it is not entirely clear from your post what you require.
You can reach an answer using the match and Offset functions

Match(value,array,0) finds the position of an exact match

eg if A1:d1 contain 10 20 30 40 and a2 contains 30

match(a2,a1:d1,0) returns 3

Offset(cell,rows,columns)

Returns the cell offset by that many rows and columns!
so Offset(b3,0,3) returns e3

as in the above example offset(b3,0,match(a2,a1:d1,0))

I hope that gives you something to go on, a similar function to the
above needs to be incorportated in your formula. Although I have used
numbers it will work with dates

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=27107
View this thread:
http://www.excelforum.com/showthread.php?threadid=518106
Click to expand...

Let me try to be a little clearer.
I have two rows let’s say 3 and 4. In row 3 I have the beginning date
of each month. In row 4 I have the end date of each month.

I.E.
------------e-------------------f
3---------Jan 1 06---------Feb. 1 06
4---------Jan 31 06--------Feb. 28 06

I am looking for a formula that will return a value of the column.

If today’s date is Feb. 8 06, the formula will search the array and
find that today’s date falls between 3f and 4f and return the value
“F”.

[email protected]
 
D

Dav

match(today(),e4:f4,1) Will return the column number that the match
occurs in

if your formula was wanting to update the cell eg e4 to f4 use
offset(d4,0,match(today(),e4:f4,1))

In the above it match the value that is equal to or lower than todays
date, the 4th row specifies the end of the month so this should work,
assuming jan 1 06 is recognised as a date


This adds the column the match occurs to the cell d4 so if the match
statement returns 2, the refrerence is now f4, 1 it is e4

Regards

Dav
 
Top