How to make a dynamic reference in Excel?

How do I automatically pull data from one Excel workbook to another?

I need to pull data from one workbook, store it in a variable and then perform a second series of calculations on that variable and produce new data which I want to place into another Excel workbook.

My two attempts at achieving this using XLRD (below) are not working. When I run this code below I get no output and no error.

Can anyone help me out with this? I have been struggling with this for a while and would greatly appreciate some help. import lxml.etree as etree import xlrd. Path = 'D:temp.xls' book = xlrd.openworkbook(path) sheet = book.sheetbyname('E1') a = sheet.cellvalue('F2',0) print(a). Print(a.tag) b = sheet.cellvalue('G2',0) print(b). Print(b.tag) c = sheet.cellvalue('H2',0) print(c). Print(c.tag) d = etree.tostring(sheet, encoding='unicode', method="html") print(d). D = etree.tostring(book, encoding='unicode', method="html") print(d). Your first attempt looks very close to what I'm currently trying to do. I can only find a single cell with the value "4.0". You're probably missing a sheet tag or something. But let's assume you got one.

Path = r'C:temp.openworkbook(path) sheet = book.sheetbyname('E1') a = sheet.cellvalue('F2',0) print(a). Print(a.tag) b = sheet.cellvalue('G2',0) print(b). Print(b.tag) This outputs: . . 4.

How do you reference other workbooks in Excel dynamically?

I need to populate a report based on values in other workbooks.

I found an article suggesting that I use the INDIRECT function like this: =INDIRECT("'R:MyProjectSheet1.xlsm'!" & TEXT(A15,"000,000") However, when I enter this formula into a cell in my workbook, the result is 0! (no reference name entered) So then I tried double quotation marks and then a backslash, still with no luck. I even tried putting the full filename in quotes instead of the text value for "!" (but got invalid formula error), and it took an even longer time. So the question is. How do you reference a different workbook dynamically?

You need to do two things: Make sure that the path is pointing to a cell that does not have any errors. Try using double-quotes around the file path. See example below.
=INDIRECT(""""R:pathworkbook.xlsx""""") In my experience, you should probably also add a #REF! in order for your INDIRECT command to work properly (even though it will return a 0, instead of an error). =INDIRECT("""" & Text(A15,"#REF!") & """""). This way, if there is an error, you won't get any nasty message and instead simply get an undefined result from Excel. So, try double-quotes around the workbook path and use the #REF! option, especially when you are using formulas where error checking is needed. If the sheet changes, you will have to update the path as well.