Excel

[Highway6]

I've tried several excel/microsoft forums and yahoo answers to no avail.. cant find the help i need... so figured I'd try here since this place is always pretty active in case someone here knows the answer.

I need to know if there is an excel function that treats a cell like a variable...

essentially =(What is D, assuming a=b )

I have Worksheet 1, long and complicated, but essentially has one variable cell, and one result cell.

if a =2, b= 6

in Worksheet 2, i have several lists, items getting added and subtracted, and the result is 8 possible alternatives for a ( S-Z )

I want to be able to take this value of a, plug it into the other sheet, and give me a value for b.

But.. I cant just plug in the 8 values of S-Z in to a, and get 8 different Bs.... I want it to be automatic since S-Z are constantly changing depending on entries I add to the list.

Simple problem for the excel experts out there.. but i'm not one of them.

Is there a way to make cell a on sheet 2 a variable, that can be referenced with temp values S-Z from other cells to give me numerous answers for b

[Mr. Chenevert]

Sounds like a job for Vlookup

You ever use it before?

Here is an example I wrote up sometime ago. See if you can figure out how vlookup works. I think you can apply it to your problem.

Say you're looking up info on the companies in column A...and you're looking for expenses, which HAPPEN to be in column L (This can be on a totally different workbook...you just have to do the reference properly)

we'll do this 1 step at a time

=vlookup(

now you start...the first thing you need to tell it to look up is the company...so you reference the cell that has the company...in the case of the picture it's "A16"

=vlookup(A16,

put a comma after that delicate flower...

Now you need to tell it where to look

=vlookup(A16,J23:L27,

I like to "lock" the data table, so as you copy and paste the formula, the range stays the same...you acheive that by doing this:

=vlookup(A16,$J$23:$L$27,

See them motha____in $$...those keep the data table locked.

The first column of the array, in this case, column "J" is where it is going to look for the match. IF YOU HAVE MULTIPLE MATCHES IT WILL RETURN THE FIRST AND ONLY THE FIRST ONE. I like to make the last row in the array the row I'm looking for, but you don't have to do it this way.

In this case I'm looking for expenses, which is column "L".

Column "L" is the 3rd column in the array...so that's where the #3 in the formula comes from

=vlookup(A16,$J$23:$L$27,3,

The last piece of the formula "range lookup"...I always put false, which only retyrns EXACT matches to the lookup.

[Highway6]

Sounds like a job for Vlookup

You ever use it before?

Did think about that.

Let's assume that possible and likely values of a can be anywhere from 1 to a million... so any sort of table with all possible values of b for any value a is out of the question.

Also while inputting any value of a, yields a specific value of b....

lets assume that to get from a to b, calculations are made in hundreds of cells..... so it would not be reasonable to determine the direct calculation need to solve b by just referencing a a couple hundred times.

I must say however, i do like the flava of your tutorial

[Mr. Chenevert]

I thought worksheet 1 included all values of if a = 2, b = 6 possibilities. If that is not the case, then vlookup probably wouldnt work since the array doesnt exist.

This is a job for matlab, not excel then.

[Highway6]

I thought worksheet 1 included all values of if a = 2, b = 6 possibilities. If that is not the case, then vlookup probably wouldnt work since the array doesnt exist.

This is a job for matlab, not excel then.

Worksheet 1 includes lots of lists and variables which results in 8 distinct values of a, 8 scenarios.. those potential values of a have no range, can be anything.

These 8 values of a change as the lists are amended and variables changed.

Worksheet 2 calculates the value of b, given a as a starting point for 100s of cells worth of calculations.

I need to find a way to calculate b, treating a as a variable that could be anything.

Tables of possible value with lookups, if else statements, etc - not a viable solution

Figuring out the direct correlation between a and b - not a viable solution.

Yah.. and this is shared.. so not only is it excel, it's slightly watered down google excel.

There may not be a solution.. one magical function that does what i want.

[Highway6]

I guess it boils down to can excel run a calculation where one of the cells need to run that calculation isn't constant.

Can one of the cells needed to run a calculation be variable?

[Subdude]

It's hard to visualize without knowing the lookup criteria. One flexible function that you may want to try is =offset() but this is still just a lookup. I'm not sure that Excel lets you change the criteria or have formulaic lookup criteria within a function.

[20thStDad]

Do you know visual basic? Macro it? hard to see what could be done without seeing the file for me.

[Pumapayam]

Is there a way to make cell a on sheet 2 a variable, that can be referenced with temp values S-Z from other cells to give me numerous answers for b

Yes, it is a combination of INDEX() and MATCH()

If you set your information for S-Z an array with a column header and row header to find the resulting value (your variable)

Index takes care of identifying your array

Match will look up either the column or row header based on your inputs.

An example of a smaller array from A1:D4, with column header on Column A and row header on Row 1 would reveal this

=INDEX(worksheet name'!A1:D4,MATCH(column input cell,worksheet name'!A1:A4,0),MATCH(row input cell,worksheet name'!A1:D1,0))

I guess it boils down to can excel run a calculation where one of the cells need to run that calculation isn't constant.

Can one of the cells needed to run a calculation be variable?

If you know the range for your variables or the 8 values for S-Z, they set it up as I have mentioned above. either as a 1:8 array or if needed a 2:4, what ever works to get the values down.

[Highway6]

Yes, it is a combination of INDEX() and MATCH()

If you know the range for your variables or the 8 values for S-Z, they set it up as I have mentioned above. either as a 1:8 array or if needed a 2:4, what ever works to get the values down.

So this is essentially a lookup function ?

But there isn't a range of possible answer. Each of the 8 scenarios can yield an answer that could be any number.

And the 8 results aren't fixed. I run the lists 1 day, I get 8 results...

I tweak data, add and subtract various options from the list the next day, I get 8 new results.

I dont want to run 1000+ possible values through the 2nd worksheet calculation to make a 1000+ cell array that tells me when a=this then b=that.

Am i misunderstanding your solution ?

[Pumapayam]

I need to know if there is an excel function that treats a cell like a variable...

essentially =(What is D, assuming a=b )

That is just a result of the equation then.

I am not getting how you say plug in input A to get result B on worksheet 1

and then state (8) inputs, S-Z to get result B on worksheet 2

Are you saying the one of the (8) values of S-Z = A to give you the result B that you need?

You need limits or something, because if you have (8) differents values, you will have (8) different results, B

[Mr. Chenevert]

excel is not capable of running these types of calculations...

[Highway6]

That is just a result of the equation then.

I am not getting how you say plug in input A to get result B on worksheet 1

and then state (8) inputs, S-Z to get result B on worksheet 2

Are you saying the one of the (8) values of S-Z = A to give you the result B that you need?

You need limits or something, because if you have (8) differents values, you will have (8) different results, B

Exactly.. for each value of a (s-z) you get a new value of b

I'm attaching an example.

I used a factor to basically show that the lists change, and when they do, new values of S-Z are reached.

On the top half, upon figuring out the values of S-Z... i manually enter that into A on sheet 2, determine B, then manually enter that value of B into sheet 1 for that unique possible value of A (S).

I would then have to do that 7 more times.

The next day.. the lists change, factor, and i get new values for S-Z... I would have to do the above process all over again 8 times.

As you can see, even in this example, the value of A (s-z) has a range of thousands.

And, in worksheet B.. while it would be possible with example since its pretty small, it's a pretty big task to make 1 function that directly correlates A to B.

What I need is in worksheet 2... the input box A should be variable J

Then in worksheet 1, for each B cell.... I need a function that says - Assume J=A, solve for B .. and so on down the line.

I want worksheet 2 to run in the background.... I want B updated for each value of A whenever the lists (factor) changes automatically.

excel is not capable of running these types of calculations...

yes.. im starting to see that.

i do appreciate all the help from everyone though.

[memebag]

It sounds like you just want a function that does what worksheet 2 does, and make column B on worksheet 1 show the results of that function for the values in column A, right?

[Highway6]

It sounds like you just want a function that does what worksheet 2 does, and make column B on worksheet 1 show the results of that function for the values in column A, right?

Bingo.

If the range were small enough, i could make an array of possible values and use a lookup.

If the worksheet 2 were simple enough, i could make one function that directly correlates B to A.

I want to know if there is a solution that doesnt use one of those two strategies.

[Pumapayam]

Exactly.. for each value of a (s-z) you get a new value of b

I'm attaching an example.

I used a factor to basically show that the lists change, and when they do, new values of S-Z are reached.

On the top half, upon figuring out the values of S-Z... i manually enter that into A on sheet 2, determine B, then manually enter that value of B into sheet 1 for that unique possible value of A (S).

I would then have to do that 7 more times.

Okay, you really have:

input

A

→ result

B

in simple terms, but really it's:

input

A(s)

→ result

B(s)

=

A(t)

input

A(t)

→ result

B(t)

=

A(u)

input

A(u)

→ result . . . . . . . . =

A(z)

input

A(z)

→

B

Is this right, then all you have to do if what ever the value is, have that equation pull the value and have an equation relate to this.

What I am getting is that each of the (8) values are related to each other as a linear series of equations where one result leads to the next input with the final value you results after (8) passes.

Is that right?

[Highway6]

I suppose i could copy worksheet2 8 times, and have each one pull their A value directly from S (-Z)

That would work.

But.. if worksheet 2 changes.. i would have to re-copy the pages and fix the links.

But compared to arrays of thousands of possibilities.. that actually sounds like an easy solution.

[Pumapayam]

I suppose i could copy worksheet2 8 times, and have each one pull their A value directly from S (-Z)

That would work.

But.. if worksheet 2 changes.. i would have to re-copy the pages and fix the links.

But compared to arrays of thousands of possibilities.. that actually sounds like an easy solution.

Nope, you can have all the worksheet, if the array stay the same for all (8) of them, each equal the first sheet.

Sheet 2 value = Sheet 1 value . . .

Sheet 3 value = Sheet 1 value . . .

Is this a complete example.

Changing your factor from 5 to 10 yields the same A and B both days.

Are you trying to get input A on workseet 2 day 2 255 to equal the bottom result of 660 from worksheet 1 day 2?

[Highway6]

What I am getting is that each of the (8) values are related to each other as a linear series of equations where one result leads to the next input with the final value you results after (8) passes.

Is that right?

I get what you are saying...

Instead of finding the direct correlation between A & B.... Find the correlation of S-Z to one common factor.

In reality though.. there is no one common factor.. That factor from list 1 doesnt really exist. I only used it to quickly show the lists change.

Really... S-Z change depending on items added and taken away from the lists.

One change might only effect S-U... another might change just Z... another might change all of them.

There is no direct correlation between any of the S-Z factors and each other.

Changing your factor from 5 to 10 yields the same A and B both days.

Are you trying to get input A on workseet 2 day 2 255 to equal the bottom result of 660 from worksheet 1 day 2?

It yielded a different A in sheet1.... But because sheet2 doesnt know that the value of A changed (since there are 8 possible As).. you're right.. B hasnt changed becasue I havent come in and manually figured out what all the new values of B are.

[Highway6]

Nope, you can have all the worksheet, if the array stay the same for all (8) of them, each equal the first sheet.

Sheet 2 value = Sheet 1 value . . .

Sheet 3 value = Sheet 1 value . . .

So.. you're saying an array can be set up that only needs to be as big as there are possible entries of A (8) at any 1 given period of time.....and not as big as there are possible values of each A (thousands) ??

[Pumapayam]

I get what you are saying...

Instead of finding the direct correlation between A & B.... Find the correlation of S-Z to one common factor.

In reality though.. there is no one common factor.. That factor from list 1 doesnt really exist. I only used it to quickly show the lists change.

Really... S-Z change depending on items added and taken away from the lists.

One change might only effect S-U... another might change just Z... another might change all of them.

There is no direct correlation between any of the S-Z factors and each other.

Okay, you are basically trying to contain an "arrays" worth of information in a single cell.

You can't

You need to pull the answer from an array, and if you have a different array for each S-Z, you need to setup up 8 worksheets, and have a 9th sheet as a summary sheet showing the results.

So.. you're saying an array can be set up that only needs to be as big as there are possible entries of A (8) at any 1 given period of time.....and not as big as there are possible values of each A (thousands) ??

No, scratch that, each array is different, as I an seeing now that depending on you A, you get a whole different array of results.

[Highway6]

Okay, you are basically trying to contain an "arrays" worth of information in a single cell.

You can't

You need to pull the answer from an array, and if you have a different array for each S-Z, you need to setup up 8 worksheets, and have a 9th sheet as a summary sheet showing the results.

Well. Actually I'm trying to solve this without an array.

Worksheet 1 would be the summary sheet... but yah, 8 worksheet2s.. ( or Worksheet S.. Worksheet T.. etc) would solve the problem because essentially we would be doing away with A altogether.

The only problem with that solution is what if worksheet 2 changes...

I'd have to make 7 new copies and reattach the appropriate link to S-Z... but.. thats workable.

And while that might be a solution here.. it wouldnt be if instead of S-Z, we were looking at AA-ZZ or worse for possible values of A

[Pumapayam]

The only problem with that solution is what if worksheet 2 changes...

I'd have to make 7 new copies and reattach the appropriate link to S-Z... but.. thats workable.

Worksheet 2-9, or S-Z (or the 8 sheets of arrays should only be formulas), and have no direct values in it that you manually input.

Worksheet 1 or input A/result B should be the only thing you touch.

Once you set up your arrays, with formulas, why would you need to change anything, other than formulas.

If your work requires you to not only change values, but formulas too, then yes, you are royally screwed.

[Highway6]

Worksheet 2-9, or S-Z (or the 8 sheets of arrays should only be formulas), and have no direct values in it that you manually input.

Worksheet 1 or input A/result B should be the only thing you touch.

Right.. Instead of 1 extra worksheet dealing with 8 inputs and outputs... we have 8 background worksheets each dealing with 1 input and 1 output back to the mastersheet..

If your work requires you to not only change values, but formulas too, then yes, you are royally screwed.

Yup.

Screwed if the worsheet# formula changes.

Screwed even more so if it changes and I'm dealing with 100s of individual worksheets, not just 8

I just cant believe there isnt some simple function like ...=Var(*J, S1) that lets cell J derive its value from S1

[memebag]

Can't you create a formula that replaces worksheet 2?

[Highway6]

Can't you create a formula that replaces worksheet 2?

Yes.. in the case of my specific problem, like the example, its simple enough that I was able to write one complex formula that solves B given A.

But.. just like adding background worksheets, this is only viable a solution up to a certain point..

so is trying to determine that 1 formula.

What if to solve B, given A... it went through 10 worksheets and 1000s of cells and calculations?

I don't suppose there is a hidden worksheet analyze button that will specifically work out the relationship between two cells ?!?!?!

[memebag]

Yes.. in the case of my specific problem, like the example, its simple enough that I was able to write one complex formula that solves B given A.

But.. just like adding background worksheets, this is only viable a solution up to a certain point..

so is trying to determine that 1 formula.

What if to solve B, given A... it went through 10 worksheets and 1000s of cells and calculations?

Then you need a real programming language.

I don't suppose there is a hidden worksheet analyze button that will specifically work out the relationship between two cells ?!?!?!

I don't understand the terms you use. What it sounds like you want is an Excel function that sets the value of cells in a worksheet and returns the value of another another cell in that worksheet. It doesn't seem to have that capability, but I'm no Excel expert.

[Highway6]

I don't understand the terms you use. What it sounds like you want is an Excel function that sets the value of cells in a worksheet and returns the value of another another cell in that worksheet. It doesn't seem to have that capability, but I'm no Excel expert.

I was being facetious..

It could be lot of work and time, with a high degree of error, in going through 100s of cells worth of calculations to write 1 formula that links A to B.

A magic button that analyzes my spreadsheet and does it for me would be nicer...

[20thStDad]

Are you stuck with Excel to do this? You could build a simple form and VB code in Access with a few simple tables and crank this out easily I think.