Macro Picking the Wrong Cell Reference from an other Worksheet

excelmacros321

New Member
Joined
Nov 11, 2017
Messages
1
Hi All,

I have several worksheets in a single excel file. In one worksheet I have calculated my Monthly Forecast Result, using formulas which are linked with other worksheets in the same file. I have made another worksheet where I want to present Monthly Actuals +Forecasts.

The requirement is when I enter the actual result in any cell it should automatically populate the forecast for the following months in the following columns. For example; in Actuals+Forecast worksheet, I enter the value of $ 1,000 in the month of June, it should automatically populate values from Forecast worksheet for the next months i.e. July, August and so on. I wrote a macro for this but it is picking up the value from the wrong cell.

Is there any way to correct the macros or is there any simpler way to tackle this without macro, using any other formulas?

Thank you.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi,

In order to get a cleaner answer, you'll need to better describe what is sitting where in your workbook. Ie, cell references, a couple of examples of what a macro/formula should populate. This could help quite a bit in making easy to understand pictures and example expected results from your workbook: http://www.mrexcel.com/vbaddin.shtml

Based on the info you provided, a couple of possible approaches:

- put all the info on one worksheet, this will make everything simpler. Then you can use lookup formulas for calculated cells, always referencing the cells that are higher on the page (eg, vlookup(a15,$a$1:$k14,11,0) on row 15)... and set up a pivot table to filter on / look at one subset of data at a time.

- if you need to keep the data on separate sheets, try the indirect() formula. For example, if you have a sheet called "July forecast" and you want to grab cell A1's value... on a different sheet type July into cell B1 and this in cell A1: =INDIRECT("'"&B1&" forecast'!A1")

With more information you could get a more spot-on answer.

Tai
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top