Hello guys,

I'm having a problem with a formula I have to repeat a lot of times that, if I can make automatic, can save me literally a lot of time.
The formula is the following:

=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD2;FALSE);"#N/A N/A")

in cell B2, and I drag it down until B17 and then to the right until AB17. In cell 1 there is what I need to look in the HLOOKUP formula, and are fixed values.

In the AD column I have this formula:

=MOD(ROW();18)

and I want this formula to be copied down in cell B20 with this result

=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD2;FALSE);"#N/A N/A")

to be dragged down and to the right again in order to cover that area, and then down again in cell B38 to be like

=IFERROR(HLOOKUP(B$1;Prep!$B$37:$AB$53;$AD2;FALSE);"#N/A N/A")


An example of how the formula appear in the B column is:

=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD2;FALSE);"#N/A N/A") -> starting in B2, in B1 I have years, 1986 to 2012
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD3;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD4;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD5;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD6;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD7;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD8;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD9;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD10;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD11;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD12;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD13;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD14;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD15;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD16;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD17;FALSE);"#N/A N/A")


=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD2;FALSE);"#N/A N/A") > starting in B20, in B19 I have years, 1986 to 2012
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD3;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD4;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD5;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD6;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD7;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD8;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD9;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD10;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD11;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD12;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD13;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD14;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD15;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD16;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD17;FALSE);"#N/A N/A")

I will then drag them to the right to fill the space until the AB column.
I have to use this formula until cell B53984 that's why I'm asking if there is any way to make it automatic.

Let me explain why I'm trying to do this: in the Prep worksheet I have 3000 companies and I'm analyzing a time series data for 16 balance sheet items. I'm analyzing this starting from 1986 to 2012. The problem is that I'm downloading this with Bloomberg, and on the B column data starts from the first year available for that company, and it might also skip years where there is no data available for that specific company. So in the B column a company might have data from 1997, another from 1989, another from 2001, and it might happen that the first does not have a 1998 so in the C column I have 1999.
What I'm doing is organizing data in order to have each year in the same column: I'm therefore looking for the year in the Prep sheet (raw 1 in the first case) and if it's the same as the one in my actual sheet it then pastes the value of that row in the new table; if there is not it will put #N/A N/A. So all companies will have the same years in the same column.

The problem is that I have to do this 3000 times (then copy this excel worksheet into others and adjust formulas, since I have other 24 sheets where I have to do this). I managed to make the row selection automatic using the =mod(row();18) formula, but I cannot make the table selection shift down automatically.

Is there any way to make it automatic, maybe using macros (which I've never used before)? I just cannot adjust it manually for so many times.

I don't know if I can upload the file since it's around 60MB.

If you need more infos to understand my problem and help me, please feel free to ask.

Thank you again for your help,

Manuel