TexasCHLforum.com

I have several legislative data sources that I export/import into Excel. I need a way to create a single report capturing some, but not all of the data. My problem has been finding a way to select data from one sheet based upon the column name, rather than the column letter, and pasting it onto another sheet in the same workbook. This is necessary because not all of my data sources have the same information, nor is the information that is common to some reports presented in the same column. For example, a Bill number may appear in Column D in one report, but in Column A in another report.

If the data that will be imported into the master report always appeared in the same column, then it would be easy to create a macro that would harvest the information I need. However, since the data (bill number, author, rating, etc.) appears in various columns, but with the same column titles, I need a way to select it from one sheet based upon the first row in that column (what I'm calling the column name, ex. Bill No.), not the column letter. I've tried recording a macro by searching for the column name, then copying everything from there down to a point well below anything I would need. I've done the same using "Use relative references" but neither method works. It always selects data from the absolute column reference (ex. Col. A).

Is there a way to copy the needed data from one sheet to another sheet? (Each data source will have it's own sheet and this is where the column variations occur.)

Thanks,
Chas.

Are you familiar with named ranges?

Charles,

PM sent.

PM sent to Charles and Vol Texan.

You could paste them into different tabs of the same spreadsheet and then nest a few "index match" formulas into a few if statements to create a consolidated list. Each individual report is in the same order each time you pull it you would only have to build it once. Then just paste the new reports in each time you needed to.

(Not sure how many reports you have if its more than say 15 this would be a relatively heavy computational load on excel.)

Pivot tables will sort and segregate on a column or row basis.

oljames3 wrote:Are you familiar with named ranges?

Yes, but I don't know if a search/copy function would be based solely on the range name, or if it would go to the same cell to begin. In other words, is the range name just a simplified way to identify absolute cells in a formula?

Chas.

Charles L. Cotton wrote:

oljames3 wrote:Are you familiar with named ranges?

Yes, but I don't know if a search/copy function would be based solely on the range name, or if it would go to the same cell to begin. In other words, is the range name just a simplified way to identify absolute cells in a formula?

Chas.

They can be used in that way. Once a range is named you can use that name in a formula or a macro and it will reference what you have named.

lama wrote:

Charles L. Cotton wrote:

oljames3 wrote:Are you familiar with named ranges?

Yes, but I don't know if a search/copy function would be based solely on the range name, or if it would go to the same cell to begin. In other words, is the range name just a simplified way to identify absolute cells in a formula?

Chas.

They can be used in that way. Once a range is named you can use that name in a formula or a macro and it will reference what you have named.

Is this true if different sheets have the same range names appearing in different columns? I need one macro that will search all sheets and copy the appropriate data to the master report sheet.

Thanks,
Chas.

In a given workbook, Excel won't allow you to have different ranges with the same name. Some ranges that conceivably would have the same name can still be named using extra characters, perhaps to denote the source sheet. It shouldn't be too hard to create a report sheet that calls on the data in the other sheets, using named ranges and lookup functions.

Charles,

Sorry, my original PM to you didn't make it out. I sent another one with my phone number.

VBA is lthe easiest way to do this, and I can write it up for you pretty easily. If you get me samples of the source data files, explanation of what on those files you are looking for, and a sample of the output you'd like, I can write something that will open / read / copy data from / and close as many files as you want.

I can do this over the weekend, or if you prefer me to come by your office on Monday, we can work out the details then.

VT

Vol Texan is right VBA is prob the easiest. If he has the knowledge and time you should take him up on that haha.

If you were to name the ranges you could do something along the lines of:
=IF(ROWS($A$2:A2)<ROWS(M),INDEX(M,ROWS($A$2:A2),COLUMNS($A$2:A2)),IF(ROWS($A$2:A2)<ROWS(M)+ROWS(B),INDEX(B,ROWS($A$2:A2)-ROWS(M),COLUMNS($A$2:A2)),IF(ROWS($A$2:A2)<ROWS(M)+ROWS(B)+ROWS(CH),INDEX(CH,ROWS($A$2:A2)-ROWS(M)-ROWS(B),COLUMNS($A$2:A2)),"")))

Where your named ranges were "M" "B" & "CH". But you would then have to paste values and remove duplicates, because if your ranges were any larger than your datasets you would have rows of all zero.

Fun with Formulas

There is probably a pretty solution that checks if the result is text and if not jumps to the next range but I am simple minded and don't know VBA well. Since someone offered that solution I will move on.

Have a great day and good luck.

Thanks for the tips guys. I found a way to get what I need. It's not in a single macro, but it works and it's quick.

Thanks again.
Chas.

One thing about Excel .. There is more than one way to skin a cat.