Getting data to follow in a sort

  • Thread starter Thread starter Windowgirl
  • Start date Start date
W

Windowgirl

I am trying to create my new spreadsheets for next year. I will have
a master sheet that will have account numbers assigned where I will
plug in monthly sales figures.

On separate sheets I will have each salesman's lists of accounts that
I want to get filled each month by linking them to the master sheet.

How do I get the data to stay with it's right account if a sort is
done.

For example:

ABC company, account 123 has $5600 in sales in January.
On Jim's sheet, account 123 is the cell that has $5600 (say J3)

Now my boss wants to sort all the accounts by total sales. If I sort
the main spreadsheet, then account 123 moves to J17 and on Jim's sheet
his account 123 now shows sales for whatever account now occupies J3.
How do I rectify this so that a sort doesn't screw up these sheets?
Is it possible?

Thanks
Maribeth
 
I'm new with these newsgroups, and I've only recently learned Excel (I've
gotten pretty good, though!!), but I have an idea that might work...


As long as the account numbers are completely unique and not duplicated per
a given salesman's list, you can use "vLookup" to crossreference the account
"123" on the different spreadsheets and bring back the value for that
account regardless of sorting.

For example, on the main spreadsheet, let's say the account number "123" is
in A1 and you would like to populate B1 with the sales $. Let's also say
that on Jim's sheet, the account number "123" is in A3 and the sales $
amount is in J3.

- In B1 on the main sheet, click 'Function', 'vLookup'.
- Lookup Value = A1 [on main sheet (the account that you will be referencing
on another sheet)]
- Table Array = Click over to Jim's sheet and highlight columns A through J.
Numbers will appear at the upper right hand side of the columns as you drag.
- Code Index Number = The number of the column that you would like to return
if the account in question is found. The function will search for the
"Lookup Value" in the first column highlighted, and return the value of the
column number you select.
- Range Lookup = Enter "False" if you would like the function to only return
a value if an exact match for the "Lookup Value" is found. Enter "True" if
you would like the function to return a value for the closest match found
for the "Lookup Value". (I almost always use False).

Now, no matter how you sort the main spreadsheet, the function will still
search on the other spreadsheet for the account in question, and bring back
the correct value regardless of position on either sheet.


I'm not sure if this is what you're looking for, but thought I'd give it a
try anyway. Good luck!

Dan R.
 
Thanks Dan,

I'm learning and I think that may do the trick. I will let you know!

Maribeth
 
Back
Top