Cell table range determination

[forumguy]

Hello,

Here where I work, the higher-ups are placing multiple tables on a single sheet. This is problematic, as toward the top of each individual table there is a cell (Cell A) with a value used in formulas in the cells beneath so each cell calls out an absolute reference to Cell A. When individuals copy and paste to create a new table—which may expand or shrink in rows so we cannot use a relative reference—beneath a table, the cells in the column of the new table automatically look back toward the original table's Cell A.

What I was wondering, is if a cell is highlighted within any of these tables and sort is selected, Excel knows and highlights only the table the cell is within. I wish to place a vlookup in the column of cells that locates Cell A of the table the cell is within. Is there a function or combination of functions that, similar to the sort feature, would allow the cell to determine the table range it is within?

Hopefully this is not too confusing.

Thanks in advance,

FG

[ChemistB]

I would suggest uploading a spreadsheet showing how some tables would be laid out, where "Cell A" would be in relation to the table and what you would like to see happen.
(Go Advanced> Manage Attachments)

[forumguy]

Thanks ChemistB,

A sample sheet is attached.

The pink highlighted cells on the middle table are the issue. This table was copied and modified from the first table, but due to the copy paste process incorrectly reference back to the 'Equipment' and 'Labor' margins on the original table.

The green highlighted cells on the lower table are representative of what I am looking for with vlookup used to locate the associated margins within the same table. Here I have used the following formula for the 'Equipment' margin:

Please Login or Register  to view this content.

And the following formula to locate the 'Labor' margin within the sheet:

Please Login or Register  to view this content.

I am seeking a means to avoid editing the range within each table, e.g., $A$32:$F$42, each time the table is copied and pasted.

Now if you select cell E21 and select Data/Sort the table range in which E21 resides will be highlighted and nothing more. Same with selecting E34 on the bottom table, only that table range will be selected when selecting Data/Sort. It seems that Excel has an internal means of identifying individual tables. Is there a function or formula that I can place in these cells that can do this?

My goal is to ensure that when someone copies, pastes and edits a table, the cells below the margins on the new table are referencing the margins within their own table.

Thanks

[hawkdoc]

Forumguy,
VLOOKUP is for identifying a value in the first column of a range of cells and returning the value in a different column but the same row. Your formula, "=C34/((100-VLOOKUP("Equipment",$A$32:$F$42,5,FALSE))/100)" goes to the only line in it marked "Equipment" and returns the value of "45" formatted to show "45.0% Margin" in E33. Thats a lot of work when "=C34/((100-$E$33)/100)" does the same thing.

Tip: Don't copy the formulas from the first table. Make your formulas for each set of margins (Equipment & Labor) then copy and past only the uncalculated data (ex. A9:D11) and past it to A34. Don't make it more complicated than it needs to be. This way, if you need to expand your table, insert rows & copy the formulas from the row above to fill in the additional rows.

I tweeked the formulas in the additional table I added to demonstrate the workings. Notice that I removed the vlookup and formatted the margin % in E47 & E56 to reflect the % you want vs a formula calculation. So muche easier...

[forumguy]

Thanks Hawkdoc,

There's a difference between margins and markup, therefore the original formulas will have to remain. As to eliminating vlookup, essentially if you look at the uppermost table you will see that that was where I started and while that is easy programming for me as you suggest, that was not my goal. ( I like impossible challenges. ;-) )

The goal was to simplify the process for individuals less knowledgeable about Excel who would be copying a table and making their edits. I do not want them left with the task of ensuring that the cell formulas are correct.

Thanks though, I definitely appreciate the reply,
FG

[hawkdoc]

Would you want to copy the entire table (A6:F11) & (A13:F13) to each separate table or do you want a variable amount of rows other margin table? In your example, Changing the code in E6 from "=C6/((100-E$5)/100)" to "=C6/((100-E5)/100)" allows you to copy that row to the same position in your 45% margin table (Paste to Table 3 for example). Change the code in all 6 lines and you can copy the block A6:F11 to Cell A34 and it will automatically point to F33 (45%). The same works with A13:F13 going to A41.

Otherwise, you can just point references to the other locations and not have to copy/Paste anything as is Table 4. If the top table has blanks, Table 4 hides the formulas. No muss, no fuss.