Excel 2007 TABLES

[SkipVought]

I'm developing an Excel application using tables.

On a separate sheet, I have a master list of PART_ID and GROUP_ID.

My dashboard has a Data > Validation cell to list the GROUP_IDs for selection. As a result of the selection a query returns the list of corresponding PART_IDs in column A.

Adjacent to this list are several columns of calculations, driven by the PART_ID in each row.

All simple stuff, had I built the sheet as I described. What I actually did, cuz I've done it in 2003 (not the best approch), was to simply start with a copied list of PART_ID, INSERT the table and proceed with my calculated columns. THEN I went to add the Query, which creates a SECOND table--and never the twain shall meet!!!! This is my dilema. The QueryTable is isolated from the inserted Table. Doing a Select Current Region, selects only the data in one table at a time. SORTING sorts only one table.

In order to recover, it seems that I must shove my table of calculated columns to the left and then add columns to my QueryTable Table, one at a time, recreating my calculations. Any other suggestions?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

I wish I had a better/easier suggestion, but I'd suggest that you do just as you were thinking OR convert both tables to ranges OR if it's something that will change on a recurring basis, build some VBA code around it.

Here's some detail to how I would try to go about it:
1. Copy out any formulas you have in place, so hopefully it'll be easier to paste back in once changes made.
2. Convert each table to a range on the Table-Design tab of the ribbon, when you have each table selected.
3. THEN select Current Region (<Ctrl> + <*>) and Format as Table from the Home tab of ribbon.
4. Paste back in your formulas where they should be, edit as needed.

The reason I say it'd be good to do it via VBA code is that you could get the process set to a quick-access toolbar button, and the next time just click and go.... then again, I don't know how complex a sheet you're dealing with. So it may be more complicated than it's worth.

----of course, saving a coy of the workbook before making any changes may not hurt.

 

[SkipVought]

Thanks Steve. I ended up using brute force and inserting each column/formula (10 in all).

I guess the best advice would be to build the table in the proper sequence, beginning with the QueryTable which is the driver for the results on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Glad you got it working, Herculean effort or not.