3

I am working on a payroll sheet, and I need to extract Unique Employee ID from multiple sheets in the workbook and place them in the same workbook in another sheet.

Although I am able to create a formula, to get those Unique List however I am not able to make it dynamic, since every month I shall be importing a new sheet in the workbook and that should be taken into consideration, which is not working out with my formula.

I have tried using the INDIRECT Function to dynamically refer all the sheets but in vain may be I am doing something wrong here. I know it can be done with Power Query but I dont want to change the structure of the database also its possible with VBA, but I'm reluctant to it, specifically want to accomplish it using Excel Formula.

The below formula which I have used in Master_List Cell A2

="ID_"&SORT(SUBSTITUTE(UNIQUE(
FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",,Blad1:Blad3!A2:A1000)
,",","</b><b>")&"</b></a>","//b")),"ID_","")+0)

BLAD1

BLAD2

BLAD3

MASTER_List

I tried using this as SHEETS Defined Name within the formula, but it gives #REF Error

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")&T(NOW())

The above formula, is used for grabbing other sheets data for calculations, tried implementing it within the formula by wrapping it within INDIRECT function, but doesn't work, I know why is n't will it possible to make it dynamic or is there any workaround. I hope am able explain. Thank you for the effort and time.

Note: This is a sample data, created for the query.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32

1 Answers1

2

If you insist on formulae, here is what I did to make this work:

  • I created a name formula in the name manager: SHEETNAME. It refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
  • Assuming you have at least two sheets (a 'Master' and any other sheets have ID's in column A;

Now I used in A2 in the masterlist:

=UNIQUE(FILTERXML("<t><s>"&REDUCE("",SEQUENCE(SHEETS()-1,,2),LAMBDA(a,b,TEXTJOIN("</s><s>",,a,INDIRECT(INDEX(SHEETNAME,b)&"!A2:A100"))))&"</s></t>","//s"))

Note1: I assumed you have only got ID's in the range of A2:A100 to keep it rather simple.

Note2: This use of TEXTJOIN() can hit it's limits rather quick.

Note3: You could try to nest a 2nd UNIQUE() that would make sure that each iteration handles as little as records as possible. This would hopefully make sure that the limits of TEXTJOIN() aren't hit as quickly.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Sir, it worked perfectly, like a charm! Really `FILTERXML` is a treat ! So I have 5 sheets until now, and I have to consider the {3,4,5} Sheets hence I edited this part in my workbook `SEQUENCE(SHEETS()-2,,3)` . Also since those ID's are number I have used a `SORT` Function in the beginning as well, I already have `Sheets` Defined do I need to use the one you have given or shall I continue with the one I am using. – Mayukh Bhattacharya Mar 17 '22 at 12:30
  • 1
    [`SHEETS()`](https://support.microsoft.com/en-us/office/sheets-function-770515eb-e1e8-45ce-8066-b557e5e4b80b) is a native function, not a function you should create yourself @MayukhBhattacharya. So remove that from your name manager. – JvdV Mar 17 '22 at 12:57
  • Sir that I understood I am telling whether do i need to use another defined name for the `SHEETNAME` since I am already using one defined as `Sheets` --> `=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")&T(NOW()) ` and Sir, do you think what I have tried to achieve is valid. – Mayukh Bhattacharya Mar 17 '22 at 13:00
  • 1
    If it works it's valid. `INDIRECT()` is volatile allready, so using `NOW()` won't make such a negative impact nomore I think. – JvdV Mar 17 '22 at 13:02