Attempting to write an xlookup in each file in a folder and for a changing set of rows in a folder

Help StudioX
,
1

Hi All - I am looking to get help for an issue I haven’t been able to figure out for a month or so. I’m attempting to do the following:

Open an excel file (SourceFile let’s call it)
Write an xlookup formula that references Sourcefile in each excel file in a specified folder
Write this formula on a set number of lines on the excel sheet (i.e. from cells Q22-Q90) but have the cell references update. The first cell listed would need to update to reference what line its on (cellF22) See base level formula below
=XLOOKUP(F22,‘[Sourcefile’.xlsx]Sheet1’!$R$2:$R$9834,‘SourceFile’.xlsx]Sheet1’!$Q$2:$Q$9834,0,1)")

I’ve tried doing this a few ways, and can’t figure it out. I’d appreciate any feedback anyone has. Thank you!
I’m having issues getting this to work

2

@mwells

This is how you generally refer a file

'D:\excelsfolder\[abc.xlsx]Jan sales'!$B$2:$B$5

Here Jan sales is the sheetname…abc.xlsx is file
And remaining is path

Hope this helps

Cheers

3

Anil - I need help with how to actually structure this bot, preferably in StudioX. I don’t know if i should be creating my source file as a template, using a template for one of the 100 sheets ill be overwriting in a separate folder, etc. That is what I’m having issues figuring out.

image
image466×813 40.7 KB

4

@mwells

As far as I understand…you want to reference one source file in multiple excel files in a lookup…

So your forloop and use excel inside should be good…and posted in precious post give the for mula using the full address of source file

And I am not sure how you are thinking of template option in use excel…it is only for using with excel activities while designing its not that yhe data would be psted in the template file provided…it is only for structuring input while referencing only …no relation with actual input during runtime

Cheers

5

I don’t think this is working. The best i could get is for it to write something in just one of the files, and it didn’t seem to be using relative references, which is what i’m hoping for. I might not have explained this well enough. Here is what I’m looking to do.

Open source file that contains lookup data for my formula (to use as reference for the formula being written)
Open and loop through a folder of several excel sheets, and write lookup formula in on each line in a range (Xlookup to source file and i need it to reference a certain cell in each row (i.e. Q22 in row 22, Q23 in row 23)

I’m trying to use studioX for this and the structure I’ve come up with to use for this isn’t working. Could someone provide a structure in studiox to use for this?