Arranging of data in separate worksheets

[VDS]

Dear All expert members,

It is has been a long time to put my thread on chandoo.org.

I am attaching herewith two files contains the following :-

File 1 : in which solution is required. (named SAR lEG CAS DUMMY)
File 2 : in which formula has already there. (named VDS--DATA OF CHANDOO)

In file 1 there are 7 different worksheets one master copy and 6 additional worksheets. Each worksheet has been prepared as per company name (NISPL, LETPL, LEPL, etc) and the data has been taken.

What I require that the data to be entered/updated in the master file and output to be reflected in each of the individual worksheets .

The solution formula also there in File 2. But when I apply the formula of File 2 in File 1, it gets error message and cant get the correct output.

In fact this is the court file and each day the dates are to be updated in the mater file.

Kindly help me in sorting out this issue. If any other formula is available, it is also most welcome.


Regards
VDS

 

[p45cal]

In cell A2 of the NISPL sheet you can have the likes of:

=FILTER('ON GOING BY SAR-MASTER COPY'!A2:P10,'ON GOING BY SAR-MASTER COPY'!B2:B10="NISPL")

Similar for other sheets. The sheets will update automatically (it's a formula after all) but the range it refers to (rows 2 to 10) may have to be adjusted so that they cover more rows than will ever exist on the 'master' sheet. Better to convert that master shet to a proper table then you can refer to the table and it doesn't matter how long it gets. The formula would be like:
=FILTER(Table1,Table1[COMPANY]="NISPL")

 

[VDS]

Sir,

Kindly apply the formula in excel file and share copy as there is no FILTER COMMAND available.

Pls see the screenshot also

VDS

 

[p45cal]

Always helpful to say what version of Excel you're using.
Are macros allowed?

 

[VDS]

Sir
Will reply by tomorrow about correct version . Anyway, macro can be applied as a secondary option

 

[VDS]

 

[VDS]

Sir, Pls see attached screenshot and reply soon.

VDS

 

[p45cal]

Writing formulae in Excel 2010 for this is complicated and resource-hungry, so I'm not doing it!

On to VBA
In the attached, there's a macro with 9 lines of active code in the ThisWorkbook code-module.
It operates automatically each time you move (select/activate) from one sheet to another.
It depends on the name of each tab (sheet) being exactly the same as the values in column B of the ON GOING BY SAR-MASTER COPY sheet.
It uses Advanced Filter (the same as the Advanced Filter in the Sort & Filter section of the Data tab of the ribbon).
It briefly writes (and overwrites if there was something there before) some values into cells R1:R2 of the sheet you've just activated.
It does the Advanced Filter, then it clears cells R1:R2.
(If this writing and clearing of R1:R2 on each sheet is inconvenient we can move it elsewhere.)
It is important to note that the headers on each sheet have to be exactly the same as the headers on the ON GOING BY SAR-MASTER COPY sheet, but they don't have to be in the same order, nor do you have to use a complete set of headers. If you reduce the number of headers (columns) you want to see then adjust the code CopyToRange:=.Range("A11") to a smaller range There should be no blank headers).
It is limited to work on only these sheets by the line in code:

SheetsItShouldWorkOn = Array("NISPL", "LEPL", "LETPL", "LIPL", "Livfast BPL", "LPL") 'only works on these sheets

.
so edit this list if you make changes and want it to work on different sheets. You don't ned to delete sheet names which no longer exist.
There is some commented-out code which operates on all sheets that are not named ON GOING BY SAR-MASTER COPY, so you won't ned to update the code unless you add sheets that you don't want this to operate on.

 

[VDS]

Sir,
Received your file. seems to be okay. Let me work on the file and try with live data. Will revert for any doubt/ query.

VDS