Register
Log In
Unanswered
Home
Power Query
Power Pivot
Power BI
July 11, 2017
Offline
Hi There,
Every week I have to update 6 worksheets with the previous weeks results (Have attached copy of 1, others are the same format) then I adjust the formula in columns W to Z (row 4) and drag down.
I thought I would try HLOOKUP formulas to do this automatically each week on all 6 worksheets when I change the week numbers in cells V2 to Z2, however, it works fine on the first row (row 3) which relates to site 1, but when you drag down the 'Row_Index_Num' stays as '2'.
How can I change the formula so it pick up the correct row for each site?
Any help would be greatly appreciated
Regards
Paul
See Answer
Members
Dashboards
Power Query
Power Pivot
Power BI
December 7, 2016
Offline
Hi Paul,
A simple solution is to use ROW formula to get the row number.
Your formula in cell W3 is currently:
=HLOOKUP($W$2,$B$2:$T$251,2,FALSE)-HLOOKUP($V$2,$B$2:$T$251,2,FALSE)
My proposal is that you replace the 2 with ROW()-1 as row reference. With this you get 2 as row reference in cell W3, you get 3 in cell W4 and so on.
=HLOOKUP($W$2,$B$2:$T$251,ROW()-1,FALSE)-HLOOKUP($V$2,$B$2:$T$251,ROW()-1,FALSE)
I would also remove the first $ symbol for the cell lookup reference, so I can copy the formula horizontally also.
=HLOOKUP(W$2,$B$2:$T$251,ROW()-1,FALSE)-HLOOKUP(V$2,$B$2:$T$251,ROW()-1,FALSE)
Br,
Anders
Answers Post
Most Users Ever Online: 57
Currently Online:
15 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
sugi gunawan
janja mesec
John DiMartino
lizette acosta
eric johnson
Kim Bond
Nichelle Stevens
Manuel Rodriguez
peter wang
Amit Kanekar
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
