High Plains Grifter
Board Regular
- Joined
- Mar 9, 2010
- Messages
- 129
Hello,
I have made a spreadsheet which people will use to look up cancelled bookings made with one airline through lots of different travel agents. The users will type in the (normally but not always unique) booking reference, and will get a small table of information about that booking, which is then copied into our computer system manually, and used as a basis for various calculations outside of Excel.
The main lookup table is a list 10 columns by 23057 rows. Depending on the travel agent used, the booking may also appear on another list which is 10 x 27943. I am using formulae like this to pull records out of these lists:
=IF(ISERROR(INDEX(lookup1!$A$2:$J$23057,SMALL(IF($D$3=lookup1!$A$2:$A$23057,ROW(lookup1!$A$2:$A$23057),""),ROW(1:1))-1,COLUMN(A:A))),"",INDEX(lookup1!$A$2:$J$23057,SMALL(IF($D$3=lookup1!$A$2:$A$23057,ROW(lookup1!$A$2:$A$23057),""),ROW(1:1))-1,COLUMN(A:A)))
When the user opens the sheet, a userform loads which asks them the booking reference. If no record is found, another loads to retry, and if it is found, the information from the preceding formula is displayed on another userform. If two records are found, the user is told to choose one, and that information is displayed. They click a buton on the form which copies the information and away we go.
Each travel agent also has a seperate sheet assigned to it, which contains advice etc relevant to that agent. The information userform also contains this information in tabs.
This is all fine, and all works, but it is so slow that no one can use it. How might I make things go a little quicker? I can post all the code etc if you like, and am willing to try any general tips you might have.
In total there are 6 fairly basic userforms on the file, and none have more than 3 buttons. There is some other code as well, for making the travel agent sheets from a template, but this is not long.
All help gratefully received.
ps. the sheet was made in 2010 and will be used by others in '97. It is even slower in '97.
Mark
I have made a spreadsheet which people will use to look up cancelled bookings made with one airline through lots of different travel agents. The users will type in the (normally but not always unique) booking reference, and will get a small table of information about that booking, which is then copied into our computer system manually, and used as a basis for various calculations outside of Excel.
The main lookup table is a list 10 columns by 23057 rows. Depending on the travel agent used, the booking may also appear on another list which is 10 x 27943. I am using formulae like this to pull records out of these lists:
=IF(ISERROR(INDEX(lookup1!$A$2:$J$23057,SMALL(IF($D$3=lookup1!$A$2:$A$23057,ROW(lookup1!$A$2:$A$23057),""),ROW(1:1))-1,COLUMN(A:A))),"",INDEX(lookup1!$A$2:$J$23057,SMALL(IF($D$3=lookup1!$A$2:$A$23057,ROW(lookup1!$A$2:$A$23057),""),ROW(1:1))-1,COLUMN(A:A)))
When the user opens the sheet, a userform loads which asks them the booking reference. If no record is found, another loads to retry, and if it is found, the information from the preceding formula is displayed on another userform. If two records are found, the user is told to choose one, and that information is displayed. They click a buton on the form which copies the information and away we go.
Each travel agent also has a seperate sheet assigned to it, which contains advice etc relevant to that agent. The information userform also contains this information in tabs.
This is all fine, and all works, but it is so slow that no one can use it. How might I make things go a little quicker? I can post all the code etc if you like, and am willing to try any general tips you might have.
In total there are 6 fairly basic userforms on the file, and none have more than 3 buttons. There is some other code as well, for making the travel agent sheets from a template, but this is not long.
All help gratefully received.
ps. the sheet was made in 2010 and will be used by others in '97. It is even slower in '97.
Mark
