A columns' data under custom sort but still report to subsequent sheets

Copper Contributor

Hi Everyone, 

This is the leaderboard in our sales office at work. In every Sheet (Month), column BP shows each salesman's total sales for the month in green. It is custom filtered to order the salesmen from most sales to the least in descending order. Resulting in column BP constantly rearranging; so I am struggling to make the final totals in column BP report on the following months column B titled "Last Month" with the correct corresponding salesman. Does anyone know how I can make each sheets column BP report to column B on the following sheet although they sort every day? 

 

Thank you for your time. 

 

Screenshot 2024-07-06 152617 - Copy.pngScreenshot 2024-07-06 152646 - Copy.pngScreenshot 2024-07-06 152654 - Copy.pngScreenshot 2024-07-06 153153.png

1 Reply

@Kymberly2222 

=IF(July!B5="","",July!B5)

=IF(July!B5="","",July!B5) should be entered in August in B5 to get the value of the last month (in this case July). Pay attention to the name in column A.

 

If July!B5 is empty:

The formula evaluates as TRUE and returns "" (empty).

If July!B5 contains a value (e.g., 100):

The formula evaluates as FALSE and returns 100.

But I see an error in your pictures. August starts with the name "Adrian" and all other months with "Gsaba". In general, the names are not in the same order as the other months, which may be why there is an error in the formula.

With your permission, I recommend to provide more information.

You'll get a quicker and more accurate answer, and those who want to help don't have to invest time guessing the problem.

I recommend: Always inform about your Excel version, formulas you using, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.).

If possible, add a file (without sensitive data) and use this file to describe your project step by step, or add photos with the appropriate description (step by step – Cell by Cell).

Don't forget that not every Excel user has a clue about every job.

 

Thank you for your understanding and patience

 Hope I was able to help you with this information.