Newbie data sort/formula follow question

MadDoc

New Member
Joined
Oct 6, 2002
Messages
3
I'm trying to create a spreadsheet in Excel that will allow me to create 'addressed' formulas that will follow a piece of data around even if I sort it differently. I haven't been able to figure out how to make that work. Any suggestions?

Here's a general description:
I have a table of data that could be sorted by many different parts of the data.
I want to be able to sort differently, or change the data and resort by the same criterion.
I need to use formulas for sums, etc.
I would like to not have to rewrite the formulas every time I resort if the resorting changes the sequence of the data (which it is likely to do).

I've tried:
Naming cells. The default is to have fixed cell references. They won't change to accommodate sorting. I've tried using variable cell references (getting rid of the $'s). That didn't work either.
Using SUMIF. I can't figure out how to assign a list of relative "if's". For instance, I want it to add values up if the cell to the right of the value is one of a list of names.

I can do this in access but my boss wants in done in excel. Please help me out. Thanks.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
can you post us an example?

Would there be more than one occurence of a name in the filtered list?
 
Upvote 0
Nope, their is only one occurrence of a name. And I will try to post an example later. Thanks.
 
Upvote 0
I know this may sound foolish but have you highlighted the whole sheet and went to data sort? It keeps everything in tack the same way you are refering to but lists stuff differently according to your sort preference
 
Upvote 0
If there's only ever one occurence then I would suggest you use a VLOOKUP - you can have numerous ranges (A1:D100,B1:D100) if you have to lookup using different criteria.

Say the code is in column C on your main sheet ("sheet2") and in cell A1 ("lasw10") on your sheet that will hold the returned data("sheet1").

=VLOOKUP(A1,Sheet2!C1:D100,2,FALSE)

I would always recommend naming your fields especially if you only need one - so instead of having C1:D100, name that field "DATA" and thne

=VLOOKUP(A1,DATA,2,FALSE)

Does this solve the problem??
 
Upvote 0

Forum statistics

Threads
1,226,697
Messages
6,192,512
Members
453,727
Latest member
tuong_ng89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top