LINKED cell gives WRONG result in DATA TABLE. Please help.

[McGu]

Hopefully, you are familiar with data tables, otherwise, this won't make much sense to you.

Okay, here's the question:

Below, I have simple spreadsheet that represents the core of issue that I am dealing with in a much larger example spanning numerous worksheets and data tables. Here it is in it's simplest form:

BACKGROUND
Revenue, cell C1, is the independent variable. The rest of the categories in column C rows 1 through 13 are derived from the adjacent cell in column D multiplied by Revenue. Basic, I know, but illustrating a point *** you'll see.

The first Data table occupying A15:J20 represents a manually input revenue base in cell B16 by visually inspecting cell C1. Cells A17:A20 are also manually input as percentages. Adjacent cells, B17:B20 multiply the base number, cell B16, by (1+percentage), where percentage is located in cells A17:A19. For example, cell B17 is calculated by taking the base number of 1200 from cell B16 then multiplying it by (1+20%) to return the value of 1440. Cell B16 is the upper left most corner of my data table. As such, cells B17:B19 and cells C16:J16 become the inputs for the data table. The column input is Revenue cell C1.

The second data table occupying A21:J25 represents a linked revenue base in cell B22. This number is directly linked, meaning the formula in cell B22 is: =C1 All other data is calculated just like the previous data table.

PROBLEM
When the base number is linked, where the formula in cell B22 is: =C1, the data table generates the wrong information. Take a look at the two data tables. They should be identical, but they are not because when the base number is linked, the data table goes back and reads the number from the base, which happens to be the base number previously generated from the data table. The 20% in each data table is correct and match identically because it is the FIRST calculation. However, each successive number is not, because when the data table calculates the SECOND value of 15% it is really using the 20% increase in the base case or 1200 as its base case to multiply 1200 (1+15%) to spit out the corresponding results.

WHAT I NEED
I need a solution that will link the cell but not miscalculate the results.

 

[tusharm]

That's a good description of your problem. Of course, at some point the image will disappear from the server where it currently resides. Anyone subsequently reading this topic will have the text but nothing to relate it to.

Of course, that's not relevant to solving your problem here and now. {grin}

Interesting problem. Essentially, XL is not "resetting" the column input cell to the original value before it looks up the next value to use. While I would consider it a bug, I imagine a case could be made that the column values are meant to be independent of the calculations carried out for the Data Table. And, of course, in this instance, they are not.

In your case, the simple solution would be to link B22 to B16. After all, B16 is the *real* independent cell that is under your control.

 

[McGu]

That's a good description of your problem. Of course, at some point the image will disappear from the server where it currently resides.

Yeah, I'm using OS X at home and can't get Colo's to work on a mac. If you know of a way, please send me the link. The image is the best I could do.

In your case, the simple solution would be to link B22 to B16. After all, B16 is the *real* independent cell that is under your control.

Unfortunately, this is not a good option for my case. I'll elaborate:

The real purpose I am using this for is a sophisticated financial model that sensitizes results AUTOMATICALLY based on typical ratios and calculations when assessing the worthiness of an investment/credit. This model will be used by UNSOPHISTICATED individuals with minimal Excel abilities and equal analytical skills. Therefore, this model needs to incorporate AUTOMATED SOPHISTICATION to produce the required results.

As easy as it is for you and me to run a data table, it would be incredibly diffficult to teach numerous indiviudals how to create these tables, let alone when the model will contain over a dozen.

Any other thoughts on how to pull this by linking? One thing that did come to mind was to have the linked cell only calculate/change/pull when a certain macro was run? I have neither the sophistication or approach to ascertain the solutions appropriateness. Thoughts?

 

[shippey121]

why not try naming cell C1 as revenue, then in b22 type = revenue rather than =C1, not sure why it wouldnt work as =C1 but worth a try

 

[McGu]

hy not try naming cell C1 as revenue, then in b22 type = revenue rather than =C1, not sure why it wouldnt work as =C1 but worth a try

Nope, didn't work. Thanks for the suggestion. Any others?

 

[shippey121]

dont know if this helps tried naming the cell, same results, then i tried entering 1200 in cell E1, then i pointed B22 to E1 not C1 and i got the same results in both tables

HTH

sorry when i check the data table was still pointing to C1

back to drawing board

 

[McGu]

dont know if this helps tried naming the cell, same results, then i tried entering 1200 in cell E1, then i pointed B22 to E1 not C1 and i got the same results in both tables

Right. That should work, but it is no different than my example in the first data table. You are pointing cell B22 to a MANUALLY input cell, not a linked cell derived from the 'model'.

I am looking for a way to link the B22 directly to the model WITHOUT manually input values. Thanks again, keep them coming. I'm sure someone out there has something for us.