Information about cell - any formula existing which is linked to it

[Stefan67!]

Hello all,



I have following issue:

Tab1 contains several columns with Text in different languages.
Several cells of Tab 2-7 are linked to these cells depending on a pre-selection of language.
Now I deleted some Tabs (2,3,6) and want to clean up (remove unused lines) Tab 1.

Is there any possibility to find out any obsolete lines now in Tab 1?

[ArviLaanemets]

Are every one of tabs 2-7 for specific language? I.e. you removed all references to specific 3 languages? When this is the case, then:
In table on sheet Tab1, add (temporarily) 2 additional columns. Into one of them insert a formula, which checks the table on Tab4 containing a current value of Language4 column in Tab1, and returnig some value (e.g. TRUE or FALSE) depending the result. And into another one the formula, doing likely check for current value of Language7 in Tab1 and Tab7.

Now you can set Autofilter for Tab1 to be both of those added columns indicating the match was not found (e.g. both columns have FALSE in them). Filtered rows are the unused ones.

If each of Tab2-Tab7 did have links to several language columns in Tab1 (i.e. the division between them was not based on language), then there in not enough info in your post, to decide how to filter the unused rows.

[Stefan67!]

No, that's not the case. Tab 1 has one column for each language and Colmn A contains the selected language.
The other Tabs are independing on each other - cells are filled with different formulas of which some are linked to a cell of Tab 1- Col A.
Some Cells of Col. A may be used also in multiple Tabs - not only in one.

[ArviLaanemets]

So in tables on Sheet2 to Sheet7, there was more than a single formula per column? I'd advice to avoid this unless on those sheet were not tables, but reports. But then why did you have links to different language entries from Tab1 in those reports?

The only design I had where I used a dictionary table in my Excel projects, was an Excel app, where all data entry was made in main language (English), and user could select a reporting language. Report get data from data entry tables, and generated a report fully in selected language (the app was used in factories in several countries). Obviously this is not the case for your workbook, and currently I can see no reason you did need it at all.

[Stefan67!]

This is not correct, maybe I explained not well.
The File I'm talking about is a form to be filled by suppliers globally with different Tabs for different topics (General Info, Concerns, Process descriptions, etc.). So each Tab (=Page) has already same headers and similar footers where you can find words like:
"Name", "Address", "Pls. sign here", and lots of individual information and questions which will be shown in different languages.
Depending on the laguage selected all Pages (Tabs) will occur in that language.
Now I reduced the amount of Pages and thus several words/sentences of Translation Tab 1 are obsolete. Those I want to remove, but without any risk to destroy the existing Pages or leave cells empty.
I hope this is more clear now.

[ArviLaanemets]

So Tab2 to Tab7 were data entry sheets. User selected the language he/she did want to use, and headers on data entry sheets were displayed accordingly. The info entered into those sheets by user was not translated later anywhere. And no only Tab4 and Tab7 of them remained (and were redesigned).
And on sheet Tab1 is a table with some number of columns with text in different languages.

It looks like the solution I adviced in Post#2 may be work for you, but instead 2 of them you must have 2*n (n is the number of available languages) additional columns in Tab1 - 1 for every language per Tab4 and per Tab7

Update: Probably you can do it with 2 temporary columns only - you simply select a language, and then search for matches in selected language.