Do calculation in same coloumn for datas from various coloumns and save results

**GMara** · 09-30-2008, 05:31 AM

Hi all,

Could you please help me with following:

I'm using Excel 2003.
I have three worksheets in my workbook:
1. "Data" - to insert datas
2. "Calculation" - to do calculation with datas
3. "Results" - to save results of calculation.

There are two columns in "Data" with simple numbers: column "A" and "B".
My issue is to do calculation in column "A" of worksheet "Calculation" for datas in column "A" of "Data" worksheet and save results in column "A" of worksheet "Results" then do same calculation in same column "A" of worksheet "Calculation" for datas in column "B" of "Data" worksheet and save results in column "B" of worksheet "Result".

I know this could be done using VBA but my issue is to solve this using only Excel's features.

Attached you can find .xls file with simple example.

Thanks in advance

GMara.

**StephenR** · 09-30-2008, 08:15 AM

Why do you have to use the same cells in the calculation sheet? I doubt you can do it without VBA.

**Mark@Work** · 09-30-2008, 08:36 AM

Getting the different columns of data into the same calculation column is relatively easy, using the OFFSET function, or perhaps the INDEX function.
Getting the results back into two separate columns is probably imposible.

Why can't you simply copy the calculation function from Column A to Column B thus (in your example) Calculation B2 becomes:
=Data!B2*Data!B2
Results B2 then becomes:
=Calculation!B2

Mark.

**GMara** · 09-30-2008, 08:36 AM

Hi StephenR,

I have 60 columns in "Data" sheet and 15 columns for calculation of each of 60 columns in "Calculation" sheet, that's means that I have to have 900 columns. But in the way that I want to do calculations I will have only 15 columns in "Calculation" sheet.

So that is the only reason.

Best
Marlena.

**Mark@Work** · 09-30-2008, 08:51 AM

Attached is a spreadsheet that shows how to select just one of the data columns for the calculation (using INDEX).
Just change the column number in Cell A1 of the calculation sheet.

The only way I can think of getting the answers out is using copy and paste special values.

Mark.

**GMara** · 09-30-2008, 09:19 AM

Hi Mark,

Thanks, but this is still no solution for my task.
It seems I have to search new way to organize this

Best
GMara.

**StephenR** · 09-30-2008, 10:10 AM

What's the objection to using VBA?

**GMara** · 10-03-2008, 03:32 AM

The only objection is that my team leader wants me to solve this task without use of VBA.

**StephenR** · 10-03-2008, 05:45 AM

Perhaps if you point out that it is not possible without the use of VBA, he or she will change their mind?

**GMara** · 10-03-2008, 09:16 AM

It is more difficult to cange his mind than to solve this task.

**StephenR** · 10-03-2008, 02:05 PM

Ho ho, I sympathise. If only one could reprogram one's boss...

**ChemistB** · 10-03-2008, 02:34 PM

As Stephen said, it is impossible to transfer your results to the result sheet from the calculations without VBA. The closest you can do is to automate the calculation sheet in the way that Mark@Work suggested (or something similar) and then to copy and paste special your 15 columns of data from your Calculation! to Results!

You're going to end up with 900 columns in your Results sheet anyway. Why not skip the Calculations sheet and perform all your results in Results?

Do calculation in same coloumn for datas from various coloumns and save results

LinkBack

Thread Tools

Rate This Thread

Display

Do calculation in same coloumn for datas from various coloumns and save results

Keep the calculation streams separate.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions