VLOOKUP name range change area after new paste

[timzgida]

Hi all, I created a worksheet A based on a report B generated by a software daily. A contains employees and formulas to vlookup their numbers from B. B contains many markets ie name ranges. My problem each time I copy past new report into B the name ranges change their rows. Never same place so I have to update them using "Formulas/Edit". Columns in B report don't change. Is there a way to avoid editing each time name range? like defining range by rows between header and footer? header would be market name and footer market totals and between the two are names of sales people.
The reason I don't select the whole sheet B is because some people work in different markets hence their names are redundant.
Hope my question is clear. appreciate any help.

[dosydos]

If you are using a vlookup can't the range of that formula just be the entire column? You said the columns don't change so if you do that no matter where the rows show up they should accurately pull over to your Worksheet A.

If this doesn't help could you please attach an example worksheet showing us how this data moves in your worksheet B?

[timzgida]

Maybe I didn't explain it well. The worksheet B is the data source. It contains multiple named ranges. This worksheet B has to be updated daily meaning each day I copy/past a new report there. Doing so makes the named ranges areas slide up and down along the rows. That's how the report is generated. Ex. range X today is between rows 12 and 25 but tomorrow might be between rows 15 and 28. And this affects formulas results in worksheet A.
My question is there a way Excel can recognize the named range, though they moved, based on some top and bottom references: like name of market and market totals.? Thanks for reply

[MarvinP]

Hi Tim and welcome to the forum,

A look at a sample workbook would help to understand your question. That being said, two things come to mind.

1. Use Dynamic Named Ranges that adjust with your data.
2. Restructure your data and use Pivot Tables.

https://www.ozgrid.com/Excel/DynamicRanges.htm for an explanation of DNRs


Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.