Duplicate columns from a referenced table onto another sheet

[Aydn]

Hi everyone,

I have been looking for hours on the net for a solution to my problem and I couldn't find any, although I am sure there is one and it must be dead simple...

Here is my issue:
On one sheet, I have a referenced table called Table1, let's say the rows are for Salespersons and the columns are for their monthly sales. On the last column of the table I have the Yearly sales of every salesperson just by adding up the values in each column for every salesperson.

Now I would just like to create a summary worksheet (different from the first one) on which I would just display the first and last columns of my referenced table (the names of the salespersons and their yearly sales). I would need this summary table to update size-wise should I want to add a salesperson, that's why I am pretty sure the solution is to use referenced columns here or something like that.
I have tried many things like =Table1([@Salesperson]) or =Table1([Salesperson]:[Salesperson]) but it keeps returning the VALUE error.

My main goal here is to have my summary table update automatically (size-wise) should I want to expand my original table.

I would rather avoid a VBA solution if possible as I have never used VBA before and I don't feel very comfortable with using it.


Thank you very much in advance for your help

[nimbus3000]

There can be a simpler soln if you do not use a table.
Suppose on sheet1, clmn A has salesman names, B has sales for jan and so on.

in sheet2, in clmn A you could write:

Please Login or Register  to view this content.

in clmn B
you cou could write

Please Login or Register  to view this content.

of course the limitation is how many salesmen are there...you would not want to copy the formula on sheet2 all the way down, it might make the spreadsheet very slow.

[Aydn]

Yes, that's the whole point of my issue.

I don't want to have a formula going all the way down of my spreadsheet. I just want to have a duplicate of a referenced table onto another sheet that updates automatically. With your solution I would not be able to use my Summary spreadsheet because in reality my table is very large (the salesperson thing was just to explain my problem) and its size varies every day.

Plus, the problem with your solution is that if for some reason I want to arrange my table differently (change the order of columns for example) your references would break, whereas with direct references to the table's columns such as Table1[Column1] I would be able to tweak my original table without wrecking my summary table.

I hope it clarifies my issue

[nimbus3000]

yes, of course.
my solution is a quick fix...not so elegant

[Aydn]

I have been looking around and I have read about a function unknown to me so far: the INDIRECT function. I haven't really understood how it works but it seems that it could be a solution to my problem.

Any thoughts someone? I am kind of desperate at the moment, I am really stuck on my project...