Dictionary-like Excel spreadsheet (SMALL(IF(Index...,ROW()))

[sampahmel]

Hi all,

I'm looking to build a dictionary-like excel template where I can slot in new words according to categories (i.e. plant, animal, food,...etc)

I already have something build as in the attached spreadsheet below.

The problem is: I find it incredibly slow when I refresh the spreadsheet.

Therefore, my question is:

1.) Is there anyway to speed up the spreadsheet (as it takes quite a while to refresh). The main culprit , I guess, is the "=SMALL(IF(Index!$A$3:$A$8=Animal!$B$1,ROW(Index!$A$3:$A$8)),ROW(31:31))" formula. So is there any viable and better alternative?

2.) I've a "Car" category, which I would like to capture but for some reason, the spreadsheet isn't able to. Anyway to capture it?

Explanatory Note: I've a "Index" sheet as a content page for my entries. And I sort the entries according to categories in different sheets (i.e. Car, Plant, Animal and so on).

Thanks.

 

[Hui]

I don't find it slow at all, in fact it is instrantaneous
You do have calculation set to Manual, try setting it to Automatic

 

[Hui]

Also all your Formulas in sheets Animal to Car are limited to the first 9 rows
Change the Value of 9 to say 100 throughout

I have also updated Car sheet the same
See attached:

 

[sampahmel]

I don't find it slow at all, in fact it is instrantaneous
You do have calculation set to Manual, try setting it to Automatic

Unfortunately, the =SMALL(IF(Index!$A$3:$A$8=Animal!$B$1,ROW(Index!$A$3:$A$8)),ROW(31:31)) requires array formula, which is incredibly slow on my pc. I've simplified and desensitised my spreadsheet, which might partly explains why it is faster now, but when it's fully loaded, it's crawls like a turtle.

So if there's an alternative to array formula, I'm happy to use it.

 

[NARAYANK991]

Hi ,

Can you explain what exactly you wish to do ?

What is the purpose of the formulae in row 1 , row 6 and column A on each of the tabs Animal , Object , Plant and Car ? And what is the Index tab supposed to do ?

Narayan

 

[sampahmel]

Column A allows me to pickup the entries classified under the category specified in cell B1.

However, the entries in Index sheet are listed in a vertical manner, that's where formulae in Row 1 comes in.

When that's done, Row 6 allows me to pickup the entries and display there.

For example, in Index sheet, you'll see "Animal" category. Then Column A under Animal tab will sort through and filter out only those entries classified under "Animal" category in Index sheet.

After that, Row 1 will rotate the entries 90 degree and Row 6 will display the results in a horizontal manner (as oppose to vertical manner under Index sheet).

Thank you.

 

[NARAYANK991]

Hi ,

Does that mean that each of the tabs Animal , Object , Plant and Car will have only row 6 populated with the correct entries from the Index tab ?

If yes , then what is the purpose of having the formulae in column A going all the way till row 424 ?

Can you upload a sample file with only your data on the Index tab ? Ensure there are at least a thousand entries in the various categories of Animal , Object , Plant and Car , so that testing can be done thoroughly.

Narayan