If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Alternative formula to HLookup
BeSmart,
(I actually knew someone named B. Smart ;-0) Simply make the row lookup parameter dynamic: in this case, replace the 4 with ROW()-Row($BN$10) This expression returns a 4 for cells in row 14, 3 for cells in row 13, etc. So your final formula is =HLOOKUP(EK$9,($O$11:$BN14),ROW()-Row($BN$10))*$CZ14 HTH, Bernie MS Excel MVP "BeSmart" wrote in message ... (If this thread comes up twice I apologise - but the first posting did not appear on today's listings????) Can anyone suggest an alternative formula to the Hlookup formula so I don't need to change the number of rows looked up each time I copy the formula down. The current formula works but I need it to automatically know to look at it's current row. Current formula: =HLOOKUP(EK$9,($O$11:$BN14),4)*$CZ14 EK$9 is a date entered (eg 30/5/04) to lookup within the range (O11:BN14), Row 11 has a commencing Sunday date in each cell (a formula calculates them) eg 4/4/04, 11/4/04, 18/4/04, 25/4/04, 2/5/04, 9/5/04, 16/5/04, 23/5/04, 30/5/04, 6/6/04, 13/6/04 etc. Cell W11 contains the date 30/5/04 Rows 12-14 have activity entered randomly (eg cell W14 (the column for 30/5/04) has "1" entered) The formula is written in cell DE14, and needs to find the 30/5/04 activity ("1" in W14) and multiply it by the rate in CZ14. Any help would be greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|