Can somebody help me with this formula

[Guest]

Can somebody help me with this formula
It's in a database but I don't now wath to make of it
=SUM(INDIRECT("ck"&ROW()&":"&VLOOKUP($AH$3,$AK$1:$AO$12,5)&ROW()))

 

[Bob Phillips]

Enter this in a cell

="ck"&ROW()&":"&VLOOKUP($AH$3,$AK$1:$AO$12,5)&ROW()

and see what you get. Does it relate to a range that you recognise?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

It's adding a range of numbers (on the same row as the formula is entered -
for matters of an example, I'll use row 2) contained in CK2 through another
column (determined by the vlookup - column AO in your sample formula) such as
DA2.

 

[Guest]

Break it down
ROW() returns the row the formula is in.

VLOOKUP(lookup_value,table_array,col_index_num) Searches the first colum in
AK1:AO12 for the value in AH3 and returns the value in the fifth column of
AK1:AO12. These are probably letters since it is inside an INDIRECT function.

INDIRECT(ref_text) returns a cell or range specified by a string of
charectors. This is useful with functions if the reference is expected to
change. If A1 is "B2" and B2 is 5, then =INDIRECT(A1) would result in 5.

The & symbol is how you join strings without useing concatenate.

SUM(range) sums all cells in a range.

So basically it finds the value in AK1:AK12 that is closest to AH3 and
returns a letter representing a column. This result is put together to make
a range reference CK## (# being the row the formula is in, and @ being the
column obtained by VLOOKUP). Then the cells in that range are summed.