I have a program that can export a table into Excel. I want to use that table as a database in Access on which I can run queries that are beyond Excel's capabilities. The table will look much like a database with column headings that will serve as fields. What is the best way to get the Excel data into Access. In perusing other posts, I have seen mention of "creating a linked table to a named range in your spreadsheet". Is that the only way to make it happen?
Posts by gstettner


Re: Can Access screen variables
Great. Thanks. Now if I can get the IT guys to hook it up.

I'm presently using Excel to screen a database for certain characteristics. Imagine a database of people for whom we have the following data: height, weight, hair color, eye color, and sex. Can I use Access (which I have little experience with) to search for people having two or more of four qualifying characteristics? These are my qualifying characteristics: male, over 5'9" tall, less than 170 lbs., blond hair. I don't want just those who possess all these characteristics, but all who possess at least two of the characteristics. In other words, a 5'6" tall male who weighs 160 lbs. (passes two criteria) would pop up, as would a 5'9" blond female, weighing 140 lbs. (passes three criteria), as would a 6' male who weighs 160 lbs. with blond hair (passes four criteria).
Will that work in Access?

I need to analyze data that is exported to Excel from another system at different times. That is, I always want to perform the same analysis on different values. Then, I want to take the analysis and sort it or otherwise manipulate it without affecting the data I've exported/imported. Presently, I copy and paste the data in to a given tab/sheet (call it ID for import data). A second tab/sheet (call it CALC) has functions in that look at corresponding data on sheet ID. I fear that my approach is inelegant at best inefficient at worst.
Let's say that row A in CALC and row B in CALC look at the corresponding cells in ID. For example B6 in CALC refers to cell B6 in ID. The cells in CALC look at the data in ID and assign a value to the cell in CALC. My trouble comes when I want to sort the data in CALC. I can't just sort the data in CALC or the next time I copy/paste new data in to ID, the formulas in CALC will all refer to different cells. What I have done is copied the entire CALC page and Paste/Special . . . as Values all of the cells into a third sheet. I then manipulate/sort the third sheet.
Does is sound like there could be a better way to handle this?

I have what may be a math question as much as an Excel question. See my attached spreadsheet example in which I grow the denominator of a ratio by a fixed percentage until it equals or exceeds the numerator. This results in the denominator being grown X number of times. How can I solve for X?

Re: Look for label and data in adjacent row
I'll give it a shot. I thought the answer would look a lot more hairy. I should have thought a bit longer.

I have a spreadsheet with rows of data. Presently, there are three pairs of columns. The first column in each pair has a label (E, F, R). The second column represents the number assigned to the label (e.g. 30, 40, 60, 70). The labels may appear in the first column of any of the three pairs.
I would like to reorganize the data in three columns, which are headed, E, F, R, with the associated number underneath the heading. I have attached a sample spreadsheet showing the desired results.

Re: Concatenate formula
Much obliged. I enclosed all of that in another CONCATENATE to produce "6.5% interest rate." Thanks, again.

I think this is a pretty lame question, but is it possible to concatenate in such a way that formatting is retained? For example, my =CONCATENATE formula refers to a cell containing 0.065, but which is formatted as a % so that the cell shows 6.5%. Concatenating that with text, however, produces "0.065 [text]" Any hope for me?

I have a list of addresses in cells running down a worksheet. For example, in cell A5 is "Greenwich, CT 06830"; in cell A5 is "Cincinnati, OH 45202" etc. I would like to extract the state abbreviations from each of the addresses, but can't think how to make that happen. Can you help?


I have a column of numeric data with some blank cells. Can you confirm that the STDEV formula/function ignores blank cells. From a quick test it appears to, but I'd like a second opinion

I have four columns of data, each of different length. I want to perform analyses on the data in each column after sorting in descending order. When I sort, however, the blank cells are listed first.
How do I get rid of the blank cells, so that my descending columns don't put the blank cells at the top.

Thanks, Derk. I've only downloaded Andy's solution and haven't looked at it yet, but I appreciate knowing about the PERCENTILE function. Thanks.

I have four columns of data, each of different length. I would like to analyze each of the columns of data by quintiles. That is, for example, from a sorted list of 100 pieces of data, I want to analyze the first 20, the second 20, etc. The problem is that I don't know how long (i.e. how many bits of data) each column will be.
I tried to count the nonblank cells in each column, divide this by five, and assign cell references to various cells (see my example ssheet). However, when I use the MAXIMUM function and reference the cells containing the actual cell references, it only returns the maximuman error, in factfrom the cells containing the cell references, not the range of cells to which they refer. Can this be made to work?
Is there a better way to do this?


I have two columns of data that extend over the general period of time (9/90  present), but which data points occur at different frequencies. Column D is weekly; Column F is quarterly.
How can I perform a correlation analysis of the data?

I have two columns of data that are of uneven length. Both, however, correspond the same length of time, but their frequencies are different. For example, the data of both columns extend from 9/1990 to the present, but data column D is weekly, while data column F is quarterly.
I would like to see the data displayed over the same overall period. What am I doing wrong?

Roy,
I inserted your code into mine and it worked. I have a question, though, why did you include the "dim" line?

Thanks, Roy. I wasn't aware there was such a forum. I'll try your suggestion, too.