[libreoffice-users] Re: Referencing data in a row based on content of cell value in column A?

On 07-06-2013 20:23, Tanstaafl wrote:
> On 2013-06-07 11:38 AM, Luuk <luuk34@gmail.com> wrote:
> > This seems to be a bug.
> >
> > The reference to you first sheet (Main.A2) should be to the same cell,
> > even after sorting your data in this first sheet.
>
> It does, so no bug... but that is the problem.
>
> I'll try a simpler and more specific example...
>
> Assume a workbook containing multiple sheets: 'Main', Team1', 'Team2', etc.
>
> Sheet 'Main' has the Sales numbers for all SalesReps, and each Rep is on
> a single Team, with their numbers on their Team sheet referenced from/by
> the numbers on the 'Main' sheet.
>
> Sheet 'Team1' has SalesRep1 on row 4, with 'Team1.B4' having a reference
> value of 'Main.B4', reflecting his January Sales numbers of $10,000,
> with 'Main.C4' reflecting Feb Sales, etc, and with 'Main.N4' showing his
> Year to Date numbers (summary of 'Main.B4:M4).
>
> Now, I sort sheet 'Main' on Column N (Year to Date) in descending order
> (so the top performing Sales Rep will be at the top).
>
> Problem:
>
> 'Main.B4' now contains the Sales Figures for some other Sales Rep, but
> 'Team1.B4', which is still the row for SalesRep1 on the Team1 sheet, is
> showing the Sales Figures for 'Main.B4', which is NOT the Jan Sales
> numbers for SalesRep1. The reference to 'Main.B4' did not change when
> the data on Main sheet was sorted.
>
> As I said, I believe this is by design, but it is certainly not what I
> want/need...
>
> Thanks

Indeed no bug... ;)

VLOOKUP only works if you have a sorted list (on 'Team', not on year)

You should not make a rederence to the team ('Main.B4'?), but use the 
name of the team when determining the sales.

Sorry but i dont see the solution ....


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted