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
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