I’m trying to prepopulate a VLOOKUP string with a string from Dynamo. It seems to fall on it’s face once the “=” to start Excel formulae is added. I can see the values written literally until the VLOOKUP is passed.
No version of the screenshot will write in excel. The quoted versions ("") at least print the text and the "s.
If I can’t use a string or a codeblock, how would I paste simple values to perform a VLOOKUP once it hits EXCEL?
thanks in advance!
What’s the error?
You’ll need to use a backslash (\) escape character before each quotation mark (") to preserve the string:
That allowed the statement in the Code block thank you! But it still won’t push the code to Excel.
@Jason_Parker what is your Excel version?
@Kulkul 2016
Doing more digging/troubleshooting here, we figured out why it’s failing. The table name “Line Number” isn’t available on the sheet the data is being written to because dynamo, when “Overwrite” is checked on, actually deletes the sheet and rebuilds it from scratch each time. (You can see the Sheet move on the Excel file) Dynamo appears to be unable to create a table (only function I can find is to import/export data from and to Excel.)
I figure I’ll look into 3rd party nodes and see what’s available, but it doesn’t seem like there’s a good search function on 3rd party nodes to figure out what functions actually include.
Just wondering if it would be simpler to put the formulas into another worksheet
i.e use Dynamo to write data to a different worksheet, and the formula references this data
We currently have it set up now that you can simply delete all of the data below Row 2, and with Overwrite disabled, we can keep the table data in the cell above it, this way EXCEL auto populates the formula downward, as long as it doesn’t reach an empty row in the Header column. This is working pretty well now. I’d like to be able to turn the overwrite function on, so the end users don’t have to clear the EXCEL rows to proceed, it could be a “click and go” if I can write the formula from Dynamo, or store it in EXCEL until data is available.
We have a mess of VLOOKUP Tables (79 of them) that is stored on another page that holds all the pipe NDE requirements, based on the line number, fills out the rest of the dumped table data from the first write of line numbers from Revit, then pushes all of the data back to the sheet parameters associated to their particular line # for their individual spools.
I could see how your idea might work though, I’ll have to think about it some and try it.
I can’t help from feeling what I’m doing should be writing to a database instead of a spreadsheet, and treating the spreadsheet like a database, but I can’t find any communication nodes to Access out of the box for Dynamo.
Oh I remember why I didn’t do this in the beginning. I explored that first. When the raw data dump from Dynamo takes place, it has to go into a table to quarry it in Excel. We have a Many to One relationship as our line numbers will be on multiple parts, NDE matches multiple parts, and has to get written back to specific sheets in a specific order to keep the data matched properly. Since EXCEL has no way to know how much data is on one sheet, I can’t tell it how many items to perform the search on, so I’d be confronted with a mismatched list, or a 1 for 1 relationship, where I’d have to reference each specific cell down the list. Tables allow a search by the first index name.
I agree with the database idea- although you probably don’t want to reinvent everything if it is now (almost) working
MSAccess is problematic, particularly with 64bit operating systems
I have successfully used Dynamo with the free version of SQL server (used to be called SQLExpress, think it is something different now)
The joins/lookups of data would probably be much easier in an SQL query.
You can easily link the SQL tables back into MSAccess or Excel for reporting.
Could you do the look up in dynamo itself. IE read the excel file to a list in dynamo. Then loop the list to pull items?
Are you familiar with the Bumblebee package? It’s a very powerful alternative to the built in excel nodes:
https://konradsobon.gitbooks.io/bumblebee-primer/content/120_write_data_to_excel.html
It might be able to send the formula as is. Other than that, I’d listen to what the others have suggested, 79 VLOOKUPs might not be the best approach to solve your problem.
I tried installing the bumblebee package yesterday actually and it’s not working right. I left the author feedback the latest version won’t install. I finally got a version from 6/15 installed but none of the nodes work. Hopefully the author can respond about it soon.
I mistyped earlier, we’re performing 7 VLOOKUPS, but our NDE sheets have 79 tables total in them. The reason we started with EXCEL is we need it to be open to the Field Engineers, Foremen, and the shop. We’re thinking a DB dashboard could be pretty neato in the future, I’m just not that vested in coding, so everything is a “Gotta figure it out” thru this process haha.
I’ve been digging into the SQL quarries and using Slingshot! but I can’t get it to make a valid connection to SQLEXPRESS. hopefully the developers build some built in OBDC tools for Dynamo soon. For now I think we’re going to stick with Excel.
If you are using SQLExpress- by default, it installs to a named instance.
(This means that you can run several separate instances of SQL on one machine)
When it installs to an instance, you need to put in the computer name and instance name to connect to it. This is usually “pc name”\SQLExpress
My screenshot above is using default instance (= no instance)
So if I had used named instances, the connection string would be CND5331M5K\sqlexpress
It is easiest to set up a file DSN (ODBC) connection, then use the details in the connection file
@Jason_Parker thanks for leaving a comment on my blog (actually 3). I mentioned this before. bumblebee currently only supports Dynamo 0.9.2. I am working on an update for 1.0.
I’ve been out for a couple days sick, but huge thanks for these screenshots @Andrew_Hannell I can’t get away from converting it over to a database as it’ll provide so much more functionality down the road. I’m not going to give up on this!
