PDA

View Full Version : Substitute Macro using a lookup column



pto160
02-23-2009, 07:02 PM
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
02-23-2009, 08:28 PM
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
02-23-2009, 08:34 PM
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
02-23-2009, 09:47 PM
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
02-24-2009, 06:35 PM
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

xld
02-25-2009, 01:23 AM
I don't get it, what do you want to change D2 on Division to, as an example?

pto160
02-25-2009, 05:44 AM
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

xld
02-25-2009, 06:21 AM
B2: =INDIRECT("City!B"&D2)

etc.