PDA

View Full Version : Excel: how to execute Worksheet 1 calculations for values of worksheet 2?



Nicolas
2010-Feb-16, 05:48 PM
Hi there,

I have quite some Excel mileage, yet I'm stuck with something that should be rather trivial...

I've got 2 worksheets in my Excel file.

Worksheet 1 uses the value in its A1 field to do a large amount of calculations, giving the result in its Z45 field.

What I want to do is have on worksheet 2 2 columns:

Column A = 1, 3, 5,.....77
Column B = the results as calculated by worksheet 1 in Z45 for A1 = the values of this Column A of worksheet 2.

How can I do this. I can't google it, as I don't know what word to use to describe this.


-----------------------
For clarity a very simple example.
Worksheet 1:
B1 = A1 + 5
Worksheet 2
2
7
9
15
39

I want Worksheet 2 to do the Worksheet calculations for all values of its A column, so the result should be:
2 7
7 12
9 14
15 20
39 44

The problem in my actual case as described above is that the amount of calculations in worksheet 1 is large, so just copy pasting it for every instance in worksheet 2 is no option. Not enough Excel real estate and extremely unclear presentation that way. :)

PetersCreek
2010-Feb-16, 05:59 PM
I'm not sure I understand what you're trying to accomplish overall but if you just need to refer to cells from another worksheet, use this form of reference: Sheet1!A1. You can also give cells (or ranges of cells) a unique name (Insert>Name>Define) to use as a handy reference.

SeanF
2010-Feb-16, 07:10 PM
You've got a funny definition of the word "trivial," there, Nicolas. :)

I presume, since your "source" cell is A1 and your "result" cell is Z45, that there are intermediate cells?

That is, Z45 doesn't use A1 directly, but uses (for example) W25, which uses D12, which uses A1. Something like that?

Nicolas
2010-Feb-16, 11:11 PM
SeanF: that's it. Worksheet 1 is very complex. Therefore, if I want to do the calculations of Worksheet 1 for multiple starting values, it is no option to keep copying all these calculations.

I must find a way to change the starting value of Worksheet 1 (cell A1) and return the result to Worksheet 2.

So, PetersCreek, I know what you're saying but it doesn't work as simple as that in this case.

What I'd need, expressed in semi-code is:


Worksheet1.A1 = Worksheet2.A1
Worksheet2.B1 = Worksheet1.Z45

Worksheet1.A1 = Worksheet2.A2
Worksheet2.B2 = Worksheet1.Z45

Worksheet1.A1 = Worksheet2.A3
Worksheet2.B3 = Worksheet1.Z45

etcetc

Is the only way to do this something with VBA? I can imagine that I'm nt the only one trying to do this, so I'd assume there is a standard function to do this...

mugaliens
2010-Feb-16, 11:42 PM
No VBA needed, if I'm reading your OP right. Can you post the actual spreadsheets somewhere so I can take a peek?

Nicolas
2010-Feb-17, 08:09 AM
I'd like to Mugs, but they're company property and may in no case be spread.

But I'll make you an example.

Nicolas
2010-Feb-17, 08:20 AM
Can you PM me your Email adress? I'll email you an example file. I don't have a webhost for general files.

Glom
2010-Feb-17, 08:40 AM
Here's what I think you want to do:
You have a column of inputs in Sheet2. You want to do a reasonably convoluted operation on those values to inevitably return an output to the neighbouring column in Sheet2. The operation involves intermediate cells in Sheet1 doing stages of the operation.

There's two ways.

Make entire columns with the formulas of A1 and Z45 each corresponding to a cell in the input column.
Write a function so you can just do the calculation directly in the output column without the need for intermediates. This is done in VBA.


As mug says, it would be easier to see what you're working on.

Nicolas
2010-Feb-17, 09:10 AM
1) doesn't go because we're talking more than just columns of operations here. It is a huge calculation involving logical choices, tables etc. That's why this standard method doesn't work.

2) I've now written a nice VBA that, for a given range, copies all values of column A as starting value and pastes the output value back to column B.

It's a bit "blunt", but it does what it needs to do. It's just that I thought that this might be possible without VBA, because I feel this is quite a standard situation.

Tog
2010-Feb-17, 09:56 AM
Are you trying to get it to change automatically to each new value?

If I understand what the goal is, you want it to calculate Sheet2!A1 through all of sheet A and give the final result in Sheet2!B1, but for the whole column of Sheet2!A. So basically, you want the formula Sheet2A1(Sheet1)=Sheet2B1?

Nicolas
2010-Feb-17, 10:45 AM
I don't exactly follow you Tog.

Anyway, I've got it working in VBA now. Problem is that Excel absolutely wants to also visually show the hundreds of switches between the worksheets. This runs slow and very epileptic.

Is there a command somewhere to make a macro run without visually switching between sheets?

Now I have code like this:

Sheets("Data set 3 calc").Select
Range("AB148:AB198").Item(i, 1).Select
Selection.Copy
Sheets("Flow calculation").Select
...

This code causes the visual switching. Maybe there is a more direct way of refering to the fields that doesn't cause visual switching?

Tog
2010-Feb-17, 10:49 AM
I found out the non VBA method, but you're gonna hate it.

It's done with Data Tables, which an add-in you have to download. It's also very picky about the format. The main issue is that it has to be on one sheet.

You put your column of values on, say, b2 to B100.
You put your formula on C1, then the next leg of the formula on D1, and so on, making sure that all formulas are on Row 1. Let's say it goes to zz1

Then you select b1 to zz100 and click on What-if? -> Data Table.
Then select the first cell to be used in the formula. Say A1. This is the cell that C1 looks at.

The data for each step in the process will appear to the right of the B column, in the row associated with the value that was used to create it.

Tog
2010-Feb-17, 10:53 AM
I'm not sure about how to hide the switching. The only way I know of to do it is to make them two different workbooks. The one running the macro can read from one that's not actually open, but I don't know if it can run calculations from it. Maybe you could hide Sheet 1?

Glom
2010-Feb-17, 11:10 AM
I don't exactly follow you Tog.

Anyway, I've got it working in VBA now. Problem is that Excel absolutely wants to also visually show the hundreds of switches between the worksheets. This runs slow and very epileptic.

Is there a command somewhere to make a macro run without visually switching between sheets?

Now I have code like this:

Sheets("Data set 3 calc").Select
Range("AB148:AB198").Item(i, 1).Select
Selection.Copy
Sheets("Flow calculation").Select
...

This code causes the visual switching. Maybe there is a more direct way of refering to the fields that doesn't cause visual switching?

Oh dear! Come clean. Have you been using the macro recorder?

IIRC this may stop the apparent switching, though it is still switching without showing.


Application.Screenupdating = False


This compresses the first three lines of your snippet into one line and avoids having to activate the sheet to do it. To know where you want to go from here, I'd need to see what you do next.


Sheets("Data set 3 calc").Range("AB148:AB198").Item(i, 1).Copy


I've got my own Excel problems at the moment. I have a list and I want to make a parallel list that only lists the items that meet a certain criterion, but without blank cells in between. In order words, for example, cells 1,4,6 in the list meet the criterion so I want the parallel list to show the contents of cells 1,4 and 6 in cells 1,2 and 3.

Glom
2010-Feb-17, 11:30 AM
Well I figured out what my colleague did in the original spreadsheet. A little bit hoop jumpy but can be reasonably contained.

Nicolas
2010-Feb-17, 11:31 AM
Thanks for the input guys. I'll clean up the VBA mess and see what that does.

Nicolas
2010-Feb-17, 11:39 AM
Glom, my hero!

I've cleaned the code, which saved a lot of time and made the code a lot more clearly.

I've also used the screenupdate command, which solved the epileptic seizures.

Now it calculates in about 10 seconds, without any flickering.

Glom
2010-Feb-17, 12:08 PM
Glom, my hero!

I've cleaned the code, which saved a lot of time and made the code a lot more clearly.

I've also used the screenupdate command, which solved the epileptic seizures.

Now it calculates in about 10 seconds, without any flickering.

How long was it taking before? Can you beat my all-time record for speeding up a subroutine: 6 minutes to 3 seconds? I was proud of that one.

Nicolas
2010-Feb-17, 12:52 PM
This one went from about 3 minutes and 7 LCD monitors ;) to 18 seconds and no flickering.

However, yesterday I changed some code from 90 seconds to instantly, while making it more correct as well. :)

Nicolas
2010-Feb-17, 01:08 PM
Tog, the excel sheet I just sent you contains a very dirty VBA solution. I've got a clean VBA solution now, but as it's still relatively slow, I'm always interested in a "excel direct" solution!

HenrikOlsen
2010-Feb-17, 03:22 PM
All of this sounds like a "When all you have is a hammer..." situation.

Or to say it another way, it sounds like Excel is the wrong tool for the problem.

Nicolas
2010-Feb-17, 03:34 PM
At this point, it seems indeed that Matlab is the way to go.

The "historical" problem is though, that 90% of the necessary calculations is already in Excel. :)

Tog
2010-Feb-17, 03:59 PM
Your VBA is still cleaner than the Excel way to do it.

Using a data table you will need to re-enter or move every formula on the first sheet so that they are in one row across the top. I'm not sure it would be any faster since I think it does basically the same thing. I am sure it will be ugly.

Nicolas
2010-Feb-17, 04:06 PM
OK, VBA it is. Thanks for helping me.

Glom
2010-Feb-17, 04:12 PM
VBA when optimally programmed will be faster than formulas in cells. Formulas are there to make this quicker and easier to make, not to mention more transparent.

Nicolas
2010-Feb-17, 06:42 PM
My VBA code doesn't calculate anything, it just copies values from cells to cells; all calculations are done within excel itself.

I'll rebuild my sheet tomorrow so you don't get an 18 seconds wait after every change, but only when clicking a "recalculate" button.

Tog
2010-Feb-17, 06:48 PM
You should be able to do that by just turning off the Auto calculate feature.

Glom
2010-Feb-17, 07:25 PM
My VBA code doesn't calculate anything, it just copies values from cells to cells; all calculations are done within excel itself.

I'll rebuild my sheet tomorrow so you don't get an 18 seconds wait after every change, but only when clicking a "recalculate" button.

ARRRGGGHHHH!!!! No wonder you have to wait 18 seconds!

Reading from and printing to cells takes absolutely ages. If you're going to use a macro, use a macro. You can use any Excel function in VBA.



Application.WorksheetFunction.{whatever}


That is of course assuming you can't do whatever calculations you're doing in VBA native.

Nicolas
2010-Feb-17, 08:19 PM
Redoing the calculations in VBA really isn't an option in this case. You see, we're not talking 10 consecutive, unique calculations here. Not even 100. Closer to 1000. And it must be finished tomorrow...

If I'd have to remake all that in VBA, I might as well remake the whole project in Matlab. The issue at hand is that we've got a very complex worksheet already inside excel, and I have to use its results. That's why I had to "feed" said worksheet with data and retrieve its results.

@Tog: thanks, but I know how to tackle that issue as originally I had a macro run button, then switched to "run on change". Now it turns out that a button works better here, so I just have to rebuild the button tomorrow.

And that should be the end of this terrible calculation. :D

Glom
2010-Feb-17, 08:22 PM
Redoing the calculations in VBA really isn't an option in this case. You see, we're not talking 10 consecutive, unique calculations here. Not even 100. Closer to 1000. And it must be finished tomorrow...

Is that 1000 stages of calculation to get from a cell in the input list to its corresponding cell in the output list or are you talking about doing the same calculation for the 1000 cells in the list?

Nicolas
2010-Feb-17, 09:07 PM
1000 (well, hundreds) steps between input and output. And very few of them can be grouped iteratively. It's a large physics simulation.

Tensor
2010-Feb-17, 09:19 PM
Nicolas, do you know how to make a cell (say A1 on spreedsheet1) an object and move to different cells within that spreadsheet? Say you want to read a whole column or row into your equations. Or, even jump around. Then you can determine a starting cell, put in a loop, and when you reach a empty cell, jump out of the loop.

Nicolas
2010-Feb-18, 02:01 PM
What would be the advantage/difference with the method I'm using now? (cop and paste through VBA for..next loops)

Glom
2010-Feb-18, 02:36 PM
Nicolas, do you know how to make a cell (say A1 on spreedsheet1) an object and move to different cells within that spreadsheet? Say you want to read a whole column or row into your equations. Or, even jump around. Then you can determine a starting cell, put in a loop, and when you reach a empty cell, jump out of the loop.

If you're talking about reading cells in a column one by one, that is incredibly slow. Read the entire range to an array, work the array, then print an array back to a range. We're talking orders of magnitude time savings here.

Glom
2010-Feb-19, 01:02 PM
New question: I there a way to pick out a range from an array? I obviously know how to get a specific point, but I'm looking at getting columns out of the array.

I've been doing this the long winded way by defining a new array and loading it up using a for loop. It would be nice to be able to directly reference it.

Nicolas
2010-Feb-22, 09:48 AM
I use the worksheet.range.item.copy construction with a for loop.

I haven't tested it, but maybe it works if you simply use worksheet.range.copy?