Copying Vlookup formulas

sonnyboy

New Member
Joined
Oct 8, 2014
Messages
2
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,842
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Oct 8, 2014
Messages
2
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,842
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top