Extra-applicational extraction of Excel form
October 29, 2013 1:56 AM Subscribe
I have an MS Excel spreadsheet which uses drop-down menus which affect the content in multiple fields. It's used for buying inventory, so you'd change the "category" menu to "paperware," upon which the fields "account number" "responsible admin" etc. would all populate depending on what I've chosen in "paperware."
Now we're redoing the form and I'm tasked with mapping all available functions and how they are related
Surely there must be a smart way of getting a flowchart / decision tree / dependency map / linked variables out from the Excel sheet, but I've not found one, and since I'm not well versed in the lingo my search for plugins and solutions has failed me.
What I'd like to end up with is a (probably large) list or graphic representation of which fields effect which other fields, all string variables in those fields, and "n" or somesuch where there's a integer/float. Please let me know if I've underestimated the complexity of this, or if I'm missing an obvious solution.
Surely there must be a smart way of getting a flowchart / decision tree / dependency map / linked variables out from the Excel sheet, but I've not found one, and since I'm not well versed in the lingo my search for plugins and solutions has failed me.
What I'd like to end up with is a (probably large) list or graphic representation of which fields effect which other fields, all string variables in those fields, and "n" or somesuch where there's a integer/float. Please let me know if I've underestimated the complexity of this, or if I'm missing an obvious solution.
Trace Precedents and Trace Dependents are what you need.
What version of Excel are you working with?
posted by Aizkolari at 10:39 AM on October 29, 2013
What version of Excel are you working with?
posted by Aizkolari at 10:39 AM on October 29, 2013
Is there VBA involved? is it a .xlsx (no VBA) or .xlsm (VBA)?
If there is VBA involved, you could probably hit Alt+L+V and see the code.
If the drop-down lists are coded using Data Validation (Data->Data Validation->Data Validation), I don't think the "Trade Dependents" command will work to show you the source, unfortunately.
I don't know of any easy way to see what the source for a Data Validation list is. The one thing I will say is that IF the source of the list is dynamic (sounds like it is) then you should be able to go to the cells that it is referencing and look at the formulas to see how they are coded. For those cells, the "Trade Dependents" command should work.
Feel free to follow up if the above needs some clarification.
posted by So You're Saying These Are Pants? at 11:06 AM on October 29, 2013
If there is VBA involved, you could probably hit Alt+L+V and see the code.
If the drop-down lists are coded using Data Validation (Data->Data Validation->Data Validation), I don't think the "Trade Dependents" command will work to show you the source, unfortunately.
I don't know of any easy way to see what the source for a Data Validation list is. The one thing I will say is that IF the source of the list is dynamic (sounds like it is) then you should be able to go to the cells that it is referencing and look at the formulas to see how they are coded. For those cells, the "Trade Dependents" command should work.
Feel free to follow up if the above needs some clarification.
posted by So You're Saying These Are Pants? at 11:06 AM on October 29, 2013
Response by poster: Thanks for the replies.
I'm on OSX, using the latest Office (not at that computer ATM, Office 2011?), in Swedish to boot. I've found the "trace dependencies" ribbon option, but it was grayed out when I had the drop-down menus selected. I'll try to mark the target tables and see what it'll produce.
If the xlsx/xlsm is the file-ending, then "neither" i suppose. It's an Excel workbook file, but I'll try the shortcut non-the-less and see what'll happen.
The plugin mean²error mentioned is Windows only, and I just spent two hours installing bootcamp to have it crap out with kernel_error_shenanigan_bleh so that was rather unfruitful so far… I'll see if I can get a Win laptop somewhere tomorrow and try it out.
I still imagine that for tracing problems and presenting structure, smarter people than I must have wanted a graphic relationship presentation, so I'm still hoping that there's a simpler way than going cell by cell and look up dependencies. Then again, I appreciate that once you get over the hump Excel becomes a very particular kind of fu and graphics just get in the way…
posted by monocultured at 2:35 PM on October 29, 2013
I'm on OSX, using the latest Office (not at that computer ATM, Office 2011?), in Swedish to boot. I've found the "trace dependencies" ribbon option, but it was grayed out when I had the drop-down menus selected. I'll try to mark the target tables and see what it'll produce.
If the xlsx/xlsm is the file-ending, then "neither" i suppose. It's an Excel workbook file, but I'll try the shortcut non-the-less and see what'll happen.
The plugin mean²error mentioned is Windows only, and I just spent two hours installing bootcamp to have it crap out with kernel_error_shenanigan_bleh so that was rather unfruitful so far… I'll see if I can get a Win laptop somewhere tomorrow and try it out.
I still imagine that for tracing problems and presenting structure, smarter people than I must have wanted a graphic relationship presentation, so I'm still hoping that there's a simpler way than going cell by cell and look up dependencies. Then again, I appreciate that once you get over the hump Excel becomes a very particular kind of fu and graphics just get in the way…
posted by monocultured at 2:35 PM on October 29, 2013
Ok my file extension and shortcut were for windows, sorry.
There are ways to trace things out. Based on what I know about how I would set up the type of dynamic drop-down menus that you're describing, though, you need to trace dependents on some other cells, which is what makes this a little difficult, I think. I'm mystified as to why Trace Dependents would be greyed out though.
If you go into Data Validation (I'm assuming this exists on Excel for OSX), can you find the "list" that the drop down menu is pulling from? That's how excel does drop down menus. You use Data Validation to point to a group of cells with the things you want in the drop down menu. In this case, because the drop down menu content is changing, I'm guessing that the group of cells that contains the things that go into the drop down menu is made up of formulas that point to the other drop down menus. Those are the cells that you should try to Trace Dependents/Trace Precedents on.
I don't know how to say "data validation" in Swedish, as much as I'd like to. hah.
posted by So You're Saying These Are Pants? at 4:10 PM on October 29, 2013
There are ways to trace things out. Based on what I know about how I would set up the type of dynamic drop-down menus that you're describing, though, you need to trace dependents on some other cells, which is what makes this a little difficult, I think. I'm mystified as to why Trace Dependents would be greyed out though.
If you go into Data Validation (I'm assuming this exists on Excel for OSX), can you find the "list" that the drop down menu is pulling from? That's how excel does drop down menus. You use Data Validation to point to a group of cells with the things you want in the drop down menu. In this case, because the drop down menu content is changing, I'm guessing that the group of cells that contains the things that go into the drop down menu is made up of formulas that point to the other drop down menus. Those are the cells that you should try to Trace Dependents/Trace Precedents on.
I don't know how to say "data validation" in Swedish, as much as I'd like to. hah.
posted by So You're Saying These Are Pants? at 4:10 PM on October 29, 2013
This thread is closed to new comments.
I also found this downloadable add-in that looks like it might do a more thorough job of what you're after, and looks more list-like than graphical. Looks like you can try before you buy.
posted by mean square error at 3:24 AM on October 29, 2013 [1 favorite]