Vlookups

[Guest]

I have a spreadsheet that calculates an income statement for a selected
reporting period for various entities. The period is selected using a combo
button from the forms tools which then works in conjunction with a table to
populate a range of cells for Vlookup references. The Vlookups then populate
from a table that has 12 months of data into a table that only has the
current period and the YTD for the same period. SUMIF's then summarize the
selected data into financial statement line item categories.

The problem is that because of the size of the spreadsheet, there are about
60,000 Vlookups that update whenever the period is changed. The recalc time
is about 2 minutes. I'm certain there must be some other way to select
specific columns of data from my main table without using Vlookups, but every
possibility I've thought of has not worked.

Several options get me as far as populatiing data, but not to the point of
automating the population of the data. I guess what I'm looking for is the
functionality of a Vlookup without the processing drawback. Any ideas?

 

[Alan Beban]

I have a spreadsheet that calculates an income statement for a selected
reporting period for various entities. The period is selected using a combo
button from the forms tools which then works in conjunction with a table to
populate a range of cells for Vlookup references. The Vlookups then populate
from a table that has 12 months of data into a table that only has the
current period and the YTD for the same period. SUMIF's then summarize the
selected data into financial statement line item categories.

The problem is that because of the size of the spreadsheet, there are about
60,000 Vlookups that update whenever the period is changed. The recalc time
is about 2 minutes. I'm certain there must be some other way to select
specific columns of data from my main table without using Vlookups, but every
possibility I've thought of has not worked.

Several options get me as far as populatiing data, but not to the point of
automating the population of the data. I guess what I'm looking for is the
functionality of a Vlookup without the processing drawback. Any ideas?

It's not entirely clear to me how the Vlookups "then populate from a
table that has 12 months of data", but for what it's worth

=INDEX(a1:m10,0,3), array entered into a 10-cell column, will return the
3d column of a1:m10)

Alan Beban

 

[Roger Govier]

Hi

Assuming the source data is on sheet 1 in say A1:L20000
Insert>Name>Define> Name MyData > Refers to =Sheet1!$A$1:$A$20000

Assuming you are pulling the data to Sheet2, starting at cell B4 for the
first value for the current month.
Enter the require month number in cell B1 and in cell B4 enter
=INDEX(MyData,ROW(B1),$B$1)
Copy down column B as far as required.

For the cumulative column, use
=SUM(INDEX(MyData,ROW(B1),1):INDEX(MyData,ROW(B1),$B$1))

 

[Guest]

FYI - I have 6 columns of data that is about 10,000 rows in length. There is
data concatenated at the left of each row that the Vlookup uses in
conjunction with a column # at the top of each column. That column number is
the column index number on the main data table, which is driven from a table
by using the combo box forms button. You select the period on the combo
button which then updates a separate lookup that determines the column index
number. That cell with column index number is referenced in every lookup in
the column below, thereby populating the selected period from the larger data
table on a separate tab....

 

[Guest]

Thanks Alan and Roger. The Index function worked exactly the way in which I
needed it to. I was even able to link it into the control buttons so that
changing the periods for reporting is now automated....