Hi there - I am looking to loop through excel files in a folder and enter two formulas in each row. What i’d like to have happen:
Open the main reference file that contains the data i need to access
Perform an xlookup against this reference file in each sheet in the folder - Example - =Xlookup(F22,‘ReferenceFile$R2-$R4000’,‘ReferenceFile$Q2-$Q4000’,0,1)
Enter pricing data in each sheet in the folder
Calculate the difference between prices on each row in each excel sheet in a folder
I’d like to do this in StudioX and avoid code if possible. Please let me know your thoughts.
In Studio X you should be able to use a for each file in folder activity to open the files and inside have a for each row in excel activity to write the formulas. You may need a template file to reference the cells if you are adding new columns for the pricing data and the calculation for the difference.
Hi all - I think I still need help with this. The first file I open is the master file that i will be referencing all my xlookups to. I added a template file that is an example of one of the 100 excel sheets i need to write formulas into. I’m then having it attempt to write the xlookups in each row in those sheets, either until it hits a blank or for rows 22-90 of the sheet. Please see attached and let me know where I’m going wrong.
To start with you need not do that in a loop…instead use write cell once and write the formula in first cell…then use auto fill range activity and pass the range till where to write the formula and the formula would be filled automatically
Anil - I can’t get this to work either way I tried.
I intend step 2.1 to signify the source excel file i’m referencing my Xlookup from. I then want 2.1.1 - the end to write the formula referencing that in each file in the folder. It doesn’t seem to be able to understand what the files are that i’m having it reference.