Thread: [Solved] Index and Match Function
View Single Post
 
Old 05-29-2019, 10:52 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 949
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

Quote:
Originally Posted by mismag View Post
What are "nCalcClass,nCalcGroup,nSaleTerm" in the formula?
Names. There are more of them - Names (Names are returning a value), and Named Ranges (logically, Named Ranges are returning cell range). You see all of them at Formulas>Name Manager (or something like that - I don't have Excel available currently). Some of them are defined as cell reference, some as Table details (Table column dataranges mostly), and for some are formulas used. Generally I use naming convention where the name of Name is started winth letter 'n', or letter 'l' (Latter I use when name is used as source for Data Validation list. Data validation lists can't work with Table formula syntax, so I defined setup Tables, and then defined Table columns as named ranges).

Quote:
Originally Posted by mismag View Post
And also Can I create the all the tables in one sheet instead of in individual sheets?
You can, but is it a wise move? You work really only with one sheet - with Calulator (or was it Main?). All other sheets are needed only for setup. Really after the setup is done, you can hide all other sheets.

And to have all setup-tables on some single setup sheet makes this sheet cluttered for sure. And only reasonable placement of tables with this setup is to distribute them vertically (so you can delete rows from any setup table without problems, does any need for this arise in future). This means you probably can't see them all at once anyway.

Edit: An afterthought! I am not sure how familiar you are with defined Tables. When you expand defined Table (add rows), all formulas (and formats, data validations, etc.) are expanded automatically too. But only when formula or format is same for whole column! E.g. when you clear one cell anywhere in column with formula, then after that the formula will not expand into new rows, and when you edit the formula in some cell, the edited formula will not expand to other cells in column anymore too.
Reply With Quote