Substitute Macro using a lookup column

[pto160]

Sheet1
Is there a way to highlight rows A2 to A5 and change the row references based on the numbers in B2 to B5 using a macro. So =CustList!A1 would become CustList!A6, CustList!A2 would change to CustListA7 etc.

A1 B1
CustList!A1 6
CustList!A2 7
CustList!A3 8

[nst1107]

Hi, pto. Welcome to the forum. It might not be possible for anyone here to understand your problem. Could you give a better description of your objectives and perhaps attach a demo file (personal information stripped) to illustrate?

[pto160]

I tried to use Excel Genie to paste the info into the Thread. It did seem to work when I posted the thread. How do you paste screen shots or attach files?

[nst1107]

At the bottom of the thread, under the box where you would type your 'quick reply', click the "Go Advanced" button. Scroll down and you'll see the "Manage Attachments" button. Click on it to upload files.

[pto160]

We have a company with a number of different Distribution centers around the country. We do a lot of external linking to spreadsheets and some of the centers are moved to other divisions or we break out the DC's by brand This means that the row references change. I use a match function to get the new row references and then use find and replace to change the row references. Is there a way to change the row references by highlighting A2 to A5 to match the numbers in D2 to D5 by using a macro? This will save me a lot of time. I am attaching a spreadsheet with the sheet division linking to the city list. The new correct row references are in D2 to D5 on the division tab.

Sean

[Bob Phillips]

I don't get it, what do you want to change D2 on Division to, as an example?

[pto160]

I want to leave D2 to D5 the way it is. It is being used to change the row references in B2 to B5 The current row for New York is =City!B3. The Match in D2 for New York is 1. So the macro would change New York to =City!B1. Based on this macro, Boston would change from City!B1 to City!B2. Chicago would change from City!B4 to City!B3 and LA would change from City!B2 to !CityB4.

I hope this is a bit more clearer. I can't find anything on this anywhere. A macro like this would save me hours finding and replacing. Let me know if you have any more questions.

Sean

[Bob Phillips]

B2: =INDIRECT("City!B"&D2)

etc.