Get data from multiple sheets at Summary Sheet

Hi,

Currently I am facing an issue with compiling the data from multiple sheets at 'Summary' Sheet.Here the problem is that I want Column A (NAME) from multiple sheets which will be unique and no duplicates.Secondly I want to pull the data of the column which has header 'Net Pay' but the problem is that Column is not fixed and may vary its position. In some sheets it is Column Y, sometimes Z,AB,AA and so on.

So,how can this be done ?

Here sheet name can be shown in row.Sample header and column A data is shown manually in 'Summary' sheet including Total of 'Net Pay' at last.

Pls note that there are many sheets may be 50-60.Here in this demo file I have presented only few sheets for demonstration purposes.

Please Login or Register  to view this content.

NOTE: formula in column A to get unique list of names

Added a "Start" and "End" sheet to delimit the "pay" Sheets"

UPDATE: add TMS formula solution in sheet SUMMARY(2)

This formula, dragged across and down should work:

Formula:

Please Login or Register  to view this content.

And it does, for the first five columns, but not the last five. Removing the INDIRECTs doesn't help so I can only think the structure of the data is different somehow. Struggling to work out what the issue is.

@tms:

Formula:

Please Login or Register  to view this content.

Originally Posted by JohnTopley

@tms:

Formula:

Please Login or Register  to view this content.

Thank you JT:

That first Z should have been ZZ. But you have highlighted the errors of my ways .

Formula:

Please Login or Register  to view this content.

There is compatibility issue with the formula may be with using Start:END.

LET(r,Start:END!$A$6:$A$90,SORT(UNIQUE(FILTER(VSTACK(r),VSTACK(r)>0))))

While I recognise the need for flexibilty in the formula, you need to recognise this means testing (via VBA) all 50/60 sheets to construct a unique list; it would be better/simpler to make the tables a consistent length of the likely maximum number of (name) entries. And the number of columns in each table is not consistent but has been addressed by both the VBA and the formula.

It is bad prectice to put tables one beneath the other .e.g the OT and Print tables: not least as this presents problems as illustrated here.

Secondly,D3,D4 across,I have mentioned manually but for more than 50 sheets it is not possible by manually.

What is the diificulty is dragging/copying a formula across 50 columns? a one-off exercise!

Added macro to get unique names list and TMS macro from post #12 to add formula solution.

Please Login or Register  to view this content.

@TMS: thanks for rep but totally unwarranted!

@JT: you see what you expect to see. I could have been looking at the formula and the data for hours, if not days, and not picked up on the error. And, it would have been an ongoing source of irritation if I hadn't been able to fix it. So, you saved me from that .

@TMS: we have all been there ... probably more times than we care to admit!

@JT: Oh yes, but a second pair of eyes often identifies the issue(s) in seconds!

Code to drop the formula into the matrix

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thank you very much John💖💖💖💖💖.

By the way why you have given 3 blank rows and stacked 'Total'.No needed to give blank rows.

Please Login or Register  to view this content.

Change as required