Need help copying cell location into formula

[leaveit2bevo]

So I have a drop down box that when a specific tenant is selected pulls the cell address from another sheet. I then want to put this into a if statement as shown in the macro, the problem Im having is that the macro copies the specific cell contents when I record the macro. If I change the tenant and re-run the macro it still pulls whatever the cell contents were when I made the macro. In my macro its B64, but when the tenant changes I need the macro to copy the new address. Any way to do this?

Please Login or Register  to view this content.

Thanks,

[abduljaleel.mca]

Please Login or Register  to view this content.

change cell values as per your requirement

[HaHoBe]

Hi, leaveit2bevo,

whereīs the dropdown located, why do you input a string into C4?

Please Login or Register  to view this content.

This code will always look for the value in C4 and adjust the formula in J7 while I believe you might be better off using Indirect there so that only a change in C4 will deliver the correct result.

Ciao,
Holger

[leaveit2bevo]

Ive tried the indirect function in the If statement and couldnt get it to work, I also need to be able to to pull the formula down to get more results.

[leaveit2bevo]

Tried the state from Holger maybe I entered it wrong but it puts "c107" in the equation instead of the cell contents.

Thanks

[leaveit2bevo]

Tried the state from Holger maybe I entered it wrong but it puts "c107" in the equation instead of the cell contents.

Thanks

[HaHoBe]

Hi, leaveit2bevo,

no, donīt think so as the code I posted inputs C107 into Range C4 to check for (yours entered B64).

Maybe thatīs the reason why I asked about the drop down and the values from there.

Change the formula to be entered into J7 to

Please Login or Register  to view this content.

The result in J7 will reflect any change in Cell C4.

Ciao,
Holger

[leaveit2bevo]

Sorry drop down is in C3, when I input the indirect formula it gives me an error. I got a version of it working but when you drag the indirect formula looks at the current sheet instead of the Raw Data sheet. Thank you so much for the help.

[HaHoBe]

Hi, leaveit2bevo,

when I input the indirect formula it gives me an error.

I know that VBA sometimes doesnīt do what the programmer is expecting but Iīm sure that there is an error number and a description which would make debugging a lot easier than just the information that there was an error (did not show up in my sample workbook with Excel2010).

I got a version of it working but when you drag the indirect formula looks at the current sheet instead of the Raw Data sheet.

Maybe you should post the working formula as it might be of interest for other users (maybe even me if I shoiuld continue to try and solve your problem). Dragging wasnīt part of teh thread opening question and has appeared for the first time. I would ask you to attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Ciao,
Holger

[leaveit2bevo]

Holger thank you for your help, the indirect formula I did works but not how I want it. Im attaching an example of what Im trying to do, basically just trying to pull the name of the mall and the $ associated with it for a specific retailer.

[leaveit2bevo]

bump any help would be greatly appreciated, this is pretty much the last hurdle to finish this project.

[HaHoBe]

Hi, leaveit2bevo,

could you please describe what the formula should do for which range ( should be two of them) as right now Iīm a bit lost with the workbook and the code supplied and have a faint idea of what you may want to achieve.

Ciao,
Holger

[leaveit2bevo]

So basically if someone selects the retailer Banana Republic I want to pull both the mall name and $ value associated with it from the tab raw data. I currently use a =if('raw data!'B32>0,'raw data!'B11,"") and =if('raw data!'B32>0,'raw data!'B32,""). First If statement pulls the mall name second pulls the $ amount associated with it. I then drag the formula right column by column to pull all relevant values. I used the macro recorder to do most of this stuff I just have to drop in the correct cell address before I run it. I dont want to do that I want to automate that portion as well so people call pull the values themselves.

[HaHoBe]

Hi, leaveit2bevo,

regarding your ranges in the macro you may try:

Please Login or Register  to view this content.

You would need to set the column absolut to where you rely on (I choose Column B for that so itīs C2 here).

Ciao,
Holger