I'm a biologist and I frequently have to do analysis on relatively large (for Excel) datasets. Usually a few charts are needed to display the data, but creating them seems to require laying out out the data in a different way than how I have it natively and getting there usually presents a problem. Excel has a couple of functions that almost give me what I need, but fall short. Basically I am looking for the ability to simulate a pivot table without some of the things (header/summary rows, sorting the data) that seem to be mandatory with them.
I typically format my data table in a "database"-style fashion, with each column representing a single variable, the top row being the column labels, and the other rows being individual data points. Some of these columns are dependent variables and some are independent variables. For example, say I'm charting the effects of a drug on different types of cells. I would have "independent variable" columns for the type of cell, the dose of the drug, the time since the drug was administered, etc... The "dependent variable" columns would be things measuring the response, like how many survived.
What I typically do is create a new worksheet and make a new table, with one independent variable along the top and the others on the left, and one dependent variable in the middle. So for example on the left I would have a label column with rows for every combination of cell type and drug dose, which would be the different series in my chart. On the top I would have columns for each time point, which will be on the chart X axis. In the middle would be a statistic (percent of surviving cells, maybe) that corresponds to the cell type and dose for that row and the time point for that column and will be the Y value of each data point. All these have to be copy/pasted in small chunks from the original data table. This is pretty much the format that is required if I want to make a line chart of the dependent variable vs one of the independent ones.
Obviously this requires a lot repetitive clicking to accomplish, and is also somewhat prone to errors. I realize this is almost exactly what a pivot table is, but the pivot table presents its own problems. It gives me the layout I need but there are a lot of extra rows/columns like summary and header rows added. In addition to cluttering the table these also get in the way of creating a proper chart by just selecting the entire region. The best way I've found is to create the table, copy the data area to a different section, delete the original, delete the extra rows and columns in the copy, and then move that over to the data region of an empty table where I have already created the correct labels. This runs into issues too because it also sorts the data, when I have my own ordering which isn't alphabetic or numeric but needs to be maintained. It also doesn't maintain links back to the old table which is annoying if something needs to be updated.
The dget function would be perfect for populating the data region of the new table given that I already have the row and column labels in place, but unfortunately some of its arguments need to be supplied in the form of an array in the table (functions or constant arrays are not allowed) so it doesn't work in this format. Back when my lab used Openoffice calc I wrote a custom function that took separate arguments for the source data area, a list of column/value pairs to select the correct row and the name of the column to grab from that row. This greatly simplified everything, but unfortunately doesn't work in Excel 2011.