HLookups - Rolling Spreadsheet to 2018

[H-Ro]

Hey guys! As we head towards year end, I get the fun part of “rolling” our 2017 spreadsheets to 2018. One particular spreadsheet I have to roll takes me the longest and I dread it every year. I’m reaching out hoping someone out there can help me determine a quicker way to roll it.

I've attached a spreadsheet with some screen shots of the "Dashboard" and "Data" tabs to help you visualize my issue.

This spreadsheet has one “Data” tab where tabs of graphs and a “Dashboard” pull from. Currently, the “Data” tab has 1,113 rows. Within those rows are several sections of data (I've only shown you 2 sections of data) “# Days”, “LTL Rev ex-FSC" and within those sections is every year from 2008-present. The columns include 52 weeks, as we report this data weekly.

To roll this spreadsheet, I will have to go into each section and insert a line for “2018”. Doing this will obviously move the range beyond the 1,113 rows. The “Dashboard” tab uses “HLookup” formulas to report the data within the specific date range. i.e. so if I drop in week 50 in the blue shaded area below the word "week", the “HLookup” formulas will update the “Dashboard” tab with the values from week 50 for each section (# Days, LTL Rev ex FSC, etc).

Every line inserted to insert 2018 just messes with the HLookups. There has got to be an easier way to roll this spreadsheet. Any ideas?

If you need me to send you further screen shots, don't hesitate to ask! I didn't design this spreadsheet so feel free to critique as needed. I'm still learning Excel so I'm excited to hear all of your ideas.

Thank you to everyone in advance! Help me look like a Rockstar to my bosses! Cheers!

 

[Chihiro]

Use dynamic named range? Or replace HLOOKUP with more flexible INDEX/MATCH combo and use Week# and Year as condition?

Images doesn't help much here. I'd recommend uploading sample workbook with desensitized data.

 

[H-Ro]

Thank you for getting back to me so quickly. I've attached a sample workbook with the relevant tabs. If you go to the "Data" tab you'll see week 50. That's what starts the HLookup process. Then as I add each week's data for that week/year, the "Dashboard" tab pulls accordingly from the "Data" tab. Adding a set of 2018 rows to every section in the "Data" tab is going to make issues on both the "Data" and "Dashboard" tabs.

Thanks again for your help!

 

[Nightlytic]

Hi H-Ro,

Try this, I did up a formula in dashboard cell G9, just replicate that across. The only issue is, see how your Hlookups use the row references to the right? these will no longer be necessary but your headings in data need to be exact same as per dashboard (see data tab, cell A330, I changed that, as those are used for the match function now. Oh, and they need to be exactly above the first year of your data, you should now be able to delete prior years if you wish, but make sure that if, say, you change it to 2010-2018, the heading is directly above "2010".

Alternatively, the formula is:
"INDIRECT("Data!"&SUBSTITUTE(ADDRESS(1,MATCH(G$2,Data!$3:$3,0),4),"1","")
&MATCH(B9,Data!$A:$A,0)+(MAX(Data!$A$4:$A$25)-MIN(Data!$A$5:$A$25)+1))*1000"

You can change B9 into a text reference, as I did in cell G10 onwards for the revenue section, for example for G9 this would be:
"INDIRECT("Data!"&SUBSTITUTE(ADDRESS(1,MATCH(G$2,Data!$3:$3,0),4),"1","")
&MATCH("LTL Bills / day", Data!$A:$A,0)+(MAX(Data!$A$4:$A$25)-MIN(Data!$A$5:$A$25)+1))*1000"

That will work same, without you needing to change the data headings at all, but you will need to make sure that the red bit there is the relevant heading in the data, whatever it is. And just sense check this, if the heading is repeated throughout the data you might pull the wrong info, match formula only pulls the first match going from top.

It won't look good on your person if you can't explain your work, so quick explanation:

"INDIRECT("Data!"&SUBSTITUTE(ADDRESS(1,MATCH(G$2,Data!$3:$3,0),4),"1","")
&MATCH("LTL Bills / day", Data!$A:$A,0)+(MAX(Data!$A$4:$A$25)-MIN(Data!$A$5:$A$25)+1))*1000"

Indirect( Sheet & Column & Row)

"Data!" is the sheet

&SUBSTITUTE(ADDRESS(1,MATCH(G$2,Data!$3:$3,0),4),"1","") is the column (matching G2, the current week no. to the data row 3, to figure out what month we are looking for, and address function as structured there returns it as a letter reference ("A") rather than number ("1") which indirect function requires to work

MATCH("LTL Bills / day", Data!$A:$A,0)+(MAX(Data!$A$4:$A$25)-MIN(Data!$A$5:$A$25)+1)) is the row, it matches the title heading to the data A column, to figure out what row we are looking at, but now the cell returned is the heading itself, we already know the column from above, so we just need to go down X rows, that's the

+(MAX(Data!$A$4:$A$25)-MIN(Data!$A$5:$A$25)+1))
it Takes max year (2017 now) and min year (2006) = 11, adds 1, =12, we go down 12 rows from heading.