Cell value in a formula --> Indirect function? Help please...

[Rob B]

Hello all, need some help. I want to use the cell output in the
formula. There are two outputs from the secondary worksheet in the D
and H columns. A month and a number. They are if then formulas
though, on the secondary worksheet, based on other cells in that
secondary sheet.

I have the following formula on my primary sheet:

=SUMIF(Ret_Rate_Worksheet!D10109,"January",Ret_Rate_Worksheet!H10:H109)

On the Ret_Rate worksheet, the 'January' value is a calculated value
and the value in the H column is also a calculated value. What should
my formula look like for it to work? If I manually type in January in
the D field and some numbers in the H field, the formula works.
However, I want excel to use the 'value' or output/result on the other
worksheet in the formula I have on my other worksheet. I think I need
to use the indirect function??? Help please...

-Rob

 

[Frank Kabel]

hi Rob
One question regarding the calculated values in column D: Are they text
values or dates. From your description I ssume you have calcualted
dates (and formated them to show only the month). Maybe you can post
your formula in column D and some example data

Frank

 

[Rob B]

Hi Frank,

D is actually just a date that formats to just the month "January"
(custom format: MMMM), I have another field "Day employee left the
company". The D cell is just the output of the other field that shows
just the month. I suppose it is a bit repetitive, but that is so the
user can see the month isolated. The other field 'h' is a calculated
value based on employee's years of service with the company. (end
day-start day) It is a long if/then formula that basically says
if<1year, .5,>=1and<3,1 and >3, 1.75. Suffice to say, I would like my
formula

=SUMIF(Ret_Rate_Worksheet!D10109,"January",Ret_Rate_Worksheet!H10:H109)

To look at the month only date field and for all Januarys, add up the
corresponding years of experience weighting. I then plan on using the
same formula, but substituting "February" for January and then
following that down the list for the other 10 months. The only
problem I am having is that excel does not seem to see January (which
is what I see, but instead sees the formula or the date I guess. I
want it to use the value that I see, in this case "January".

Any help would be appreciated.

Thanks,

Rob

 

[Frank Kabel]

Hi Rob
one way: try the following formula:
=SUMPRODUCT(--(MONTH(Ret_Rate_Worksheet!$D$10:$D$109)=1),(Ret_Rate_Work
sheet!$H$10:$H$109))
and change the 1 for your other months


HTH
Frank