Copying Vlookup formulas

[sonnyboy]

Hi

New user - older person - very slight computer experience seeks help in trying to do a lookup formula that i can copy down and across so it automatically changes certain cells or sheets from within a named data field.

I have 86 sheets in the workbook, with 29 columns of data that needs to be consolidated into one sheet of all client accounts, with relevant data. it has to be able to sort out a single row of data from 12 rows so have included one cell to instruct the Vlookup which month i am needing.

='Sheet 1'!$C1 this formula picks up the house number, address and account number situated on each sheet in column c1:c3 - i need the sheet 1 to change to sheet 2, sheet 3 etc as i copy down and the c1 to change to c2, c3 when i copy across.

=VLOOKUP($B$1,data1,2) this formula i need to keep the b1 which is the month number that i want it to retreive data from that row, the data1, must change to data2, data3 etc when copied down and the column number of data to be retreived must chang from 2 to 3, 4, 5 etc.

Ithink from reading odds and end that an array formula may be the answer but i am not able to get it right.

I am sure one of you big brains out there can help with this problem or i will have to sit and write out over 5000 formulas!!!! Thank you for your time.

sonnyboy

 

[jasonb75]

Welcome to the forum!

This can be done, but it will be slow and inefficient as there is no way to do it without using volatile functions (this type of function recalculates whenever something changes, non volatile functions only recalculate when they need to.)

For the first formula this would be

=INDIRECT("'Sheet "&ROWS(A$2:A2)&"'!C"&COLUMNS($A2:A2))

This assumes that the first formula will be entered into A2, otherwise the references to that cell will need to be changed accordingly.

I need a little more information on the second formula, does data1, data2, etc. refer to a named range or a column heading?

If it's a column heading, where would it be found?

Hopefully this helps get you started.

 

[sonnyboy]

Jasonb75

thank you for your advise .. Yes the data1, data2 etc are named ranges from each customer sheet across the entire area of data that I am trying to draw information from .. So sheet 1 will have a named range called data1 and so on. The named range will cover 13 rows (months in year plus total to date) and is 26 columns across .. Of which I need all the data that is on one row for the current month or if I need to pull info from previous months I can just change the number in B1 which is the search criteria. God I feel like a fish out of water with this, so thank you for all your help .. Especially trying to understand what I am trying to explain so badly !

 

[jasonb75]

I'm assuming that the first formula is intended to create row headers, then the lookup pulls in summary data to those rows, in which case try

=VLOOKUP($B$2,INDIRECT("data"&ROWS(D$2:D2)),COLUMNS($D2:D2),0)

As with A2 in the first formula, the references to D2 should be changed according to where you place the first formula.

See if tis does the trick, if you have noticeable delays in calculation then in might be worth considering an alternative vba (macro) approach.