Share via

Excel non-formula values not sorting with formula data

Anonymous
2022-03-28T19:10:02+00:00

I have two sheets in the same workbook. The first is the Master sheet, where all information is input as just normal information, no formulas (except the highlighted cells, but those are just calculations on this sheet). See screenshot below.

The second sheet is the Sub-Page. Here, column A is a straight ='s formula that references the Product Number in column A on the Master sheet. Columns B-N are VLOOKUPS that reference Sub-Page's column A to pull in information from the Master sheet. Columns O-R and AC are values that I input (no formulas) and columns S-AB are calculation formulas (no VLOOKUPS). See screenshot below.

Here's a screenshot of the VLOOKUP in column C. All the VLOOKUPs have this same $ formatting.

I want my values in columns O-R and AC to stick with the products in their same rows (27000 SHOULD keep it's $3.00 PO value). I can sort the Formula page by column A and these numbers will all move around like they are supposed to. See below.

The problem that I have is when I go to sort the Master sheet by column A, the values in the Sub-Page's columns O-R and AC DO NOT stick with their correct products. Below is the screenshot of the sorted Master sheet; you can see that the "New Product" has moved into it's correct smallest-to-largest configuration:

BUT on the Sub-Page, you'll see that the formula values have shifted to correspond with the new-order on the Master-page, but the values in columns O-R have not moved with their correct products (Product 5 now has the $3.00 PO value).

How do I fix this??

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-29T09:11:36+00:00

    Hi Natalie Hartel,

    First, my pleasure to assist you.

    As per your description, please don’t worry, we can work together to narrow down and resolve the situation.

    As the Sort does not carry any cell reference that is prefixed by a worksheet name of the formula. If possible could and if you don’t mind and if it is convenient for you, could you please send your copy of document here(you may need to upload your file into OneDrive location and share a link here) so that we can look from my side, and I will check this behaviour and help you to fix and verify the result with you.

    If there is confidential information, then you can send it to me in Private Message

    I appreciate your understanding and stay safe!!

    Best regards

    Waqas Muhammad

    0 comments