Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Inga
 
Posts: n/a
Default Can somebody help me with this formula

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()))
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Can somebody help me with this formula

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)


"Inga" wrote in message
...
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()))



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MDBCT
 
Posts: n/a
Default Can somebody help me with this formula

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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default Can somebody help me with this formula

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.



"Inga" wrote:

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()))

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF formula? meris Excel Worksheet Functions 1 September 6th 05 07:14 AM
writing a formula for a colored value aaronwexler New Users to Excel 11 September 1st 05 03:11 PM
referencing named formula using INDIRECT function [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM
Simplify formula Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM


All times are GMT +1. The time now is 11:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"