Range Name Use

[Mike S]

I was reviewing a formula developed by a fellow employee and cannot figure
out WHY it works.

=(range1)*0.1

facts:
range1 refers to sheet1!$g$2:$g$27
dates in sheet1!b2:b27 are the same as sheet2!b2:b27
formula above is in sheet2 cells c2 to c27

Why does the formula know to bring the value in range1 for the same date? I
have rechecked and 1) the name range1 refers to a single column array of
values and does not include the date and 2) the formual is not entered as an
array formula.

Thanks.

Mike

 

[John C]

It's not bringing the value for the same date. It's using the value for the
same row. If you were to switch 2 of the dates, the formulas would still
calculate the same as they are now. If you want to ensure they calculate for
the correct date, then your formula in C2 should be something like:
=SUMIF(Sheet1!$B$2:$B$27,B2,range1)*0.01

 

[Mike S]

Thanks for the quick reply John. Normally we vlookup on the date and bring
the value over that way.

Mike

 

[John C]

That's fine too, assuming you only have 1 date and 1 value per date. If you
had multiple values per date over several rows, though, the VLOOKUP will only
bring over the first value.

 

[Mike S]

Only one value per date since the dates in question here are always the first
day of the month. Instances where there are more than one date within a
month are summed using an array formula with month/year criteria.