How do I copy info from one excel spreadsheet column into another using a
formula rather than use copy/paste? The original spreadsheets are in the
wrong column format and I'm trying to avoid endless copying/pasting.

Doug

Describe what you mean by "wrong column format".

What do you consider a "spreadsheet"?

A workbook or a worksheet within a workbook?

Are you copying on the same worksheet or across worksheets or across workbooks?

But yes...........you can use formulas to return data from one place to another.

Just have to know to where/from where.


Gord Dibben MS Excel MVP

On Wed, 6 Jun 2007 16:13:00 -0700, Doug wrote:

How do I copy info from one excel spreadsheet column into another using a
formula rather than use copy/paste? The original spreadsheets are in the
wrong column format and I'm trying to avoid endless copying/pasting.

Gord,

Thanks so much for the quick response and sorry it's taken so long to reply.
The "wrong column format" I was referring to means that I have a specific
column order on one spreadsheet and the second one must be in a totally
different order to upload to a completely different database.

Which brings me to my second dilemma. Since I have to upload the second
spreadsheet independent of the first, I cannot have two spreadsheets in the
workbook.

After I merge the data, is there a way to "freeze" the values so that each
cell contains only the cell value and not the formula?

Thanks again for your help!
"Gord Dibben" wrote:

Doug

Describe what you mean by "wrong column format".

What do you consider a "spreadsheet"?

A workbook or a worksheet within a workbook?

Are you copying on the same worksheet or across worksheets or across workbooks?

But yes...........you can use formulas to return data from one place to another.

Just have to know to where/from where.


Gord Dibben MS Excel MVP

On Wed, 6 Jun 2007 16:13:00 -0700, Doug wrote:

How do I copy info from one excel spreadsheet column into another using a
formula rather than use copy/paste? The original spreadsheets are in the
wrong column format and I'm trying to avoid endless copying/pasting.

I will assume by "spreadsheet" you mean a workbook(file) since you didn't answer
my questions.

Why do you think you cannot upload a worksheet to a database?

You can have multiple worksheets in a workbook each connected to a different
database application.

Copy the entire column then paste to second sheet.

Sort the column as you wish to get the other order.

Save the workbook.

You could also do this with two independent workbooks if that's your choice.

There would be no formulas to change to values but to change them, select the
cells and Copy then Paste Special(in place)ValuesOKEsc.

If you want formulas, select the column and copy then switch to other sheet or
workbook and PasteSpecialLinksOKEsc.

Now you have to deal with changing the formulas.


Gord Dibben MS Excel MVP

On Wed, 13 Jun 2007 14:45:00 -0700, Doug wrote:

Gord,

Thanks so much for the quick response and sorry it's taken so long to reply.
The "wrong column format" I was referring to means that I have a specific
column order on one spreadsheet and the second one must be in a totally
different order to upload to a completely different database.

Which brings me to my second dilemma. Since I have to upload the second
spreadsheet independent of the first, I cannot have two spreadsheets in the
workbook.

After I merge the data, is there a way to "freeze" the values so that each
cell contains only the cell value and not the formula?

Thanks again for your help!
"Gord Dibben" wrote:

Doug

Describe what you mean by "wrong column format".

What do you consider a "spreadsheet"?

A workbook or a worksheet within a workbook?

Are you copying on the same worksheet or across worksheets or across workbooks?

But yes...........you can use formulas to return data from one place to another.

Just have to know to where/from where.


Gord Dibben MS Excel MVP

On Wed, 6 Jun 2007 16:13:00 -0700, Doug wrote:

How do I copy info from one excel spreadsheet column into another using a
formula rather than use copy/paste? The original spreadsheets are in the
wrong column format and I'm trying to avoid endless copying/pasting.

To have the values from A1 in Sheet1 to appear in A1 of Sheet2 (for
example), put this formula in A1 of Sheet2:

=IF(Sheet1!A1="","",Sheet1!A1)

Then you can copy this formula to other cells in Sheet2.

This will not change the format of the cell, however. You can use
TEXT(Sheet1!A1,format_string) to make some adjustments to numeric
data, or you could manipulate the values in Sheet1!A1 in some way (for
instance, if you have a date in the form 20070606 and you want it to
be in Excel's date format), but you will need to supply some further
information as to what your problem is before you can get a more
specific answer.

Hope this helps.

Pete

On Jun 7, 12:13 am, Doug wrote:
How do I copy info from one excel spreadsheet column into another using a
formula rather than use copy/paste? The original spreadsheets are in the
wrong column format and I'm trying to avoid endless copying/pasting.

Pete,

Thanks so much for the quick response. Sorry it's taken so long to reply.

Your formula is exactly what I was looking for, but it creates a new issue.
Please see my question posed in the thread above.

Thanks again for your help!

- Doug


"Pete_UK" wrote:

To have the values from A1 in Sheet1 to appear in A1 of Sheet2 (for
example), put this formula in A1 of Sheet2:

=IF(Sheet1!A1="","",Sheet1!A1)

Then you can copy this formula to other cells in Sheet2.

This will not change the format of the cell, however. You can use
TEXT(Sheet1!A1,format_string) to make some adjustments to numeric
data, or you could manipulate the values in Sheet1!A1 in some way (for
instance, if you have a date in the form 20070606 and you want it to
be in Excel's date format), but you will need to supply some further
information as to what your problem is before you can get a more
specific answer.

Hope this helps.

Pete

On Jun 7, 12:13 am, Doug wrote:
How do I copy info from one excel spreadsheet column into another using a
formula rather than use copy/paste? The original spreadsheets are in the
wrong column format and I'm trying to avoid endless copying/pasting.

Gord,

You're right. I forgot to clarify what I meant by "spreadsheet." What I
mean is a single sheet within a workbook. The problem of uploading the
spreadsheet to the database is that it's a proprietary program that will pull
whatever's in the cell from the specifically-named sheet into the database.
So if there's a formula in the cell, the uploaded info will be the formula,
not the value. Which, of course, won't work.

But I didn't know about the "Paste special" option - which is exactly the
answer I was looking for.

Thank you again for your help! You've saved me a tremendous amount of time.

- Doug

"Doug" wrote:

How do I copy info from one excel spreadsheet column into another using a
formula rather than use copy/paste? The original spreadsheets are in the
wrong column format and I'm trying to avoid endless copying/pasting.

Thanks for the feedback Doug.

Glad to hear you've got it working.


Gord

On Wed, 13 Jun 2007 16:36:01 -0700, Doug wrote:

Gord,

You're right. I forgot to clarify what I meant by "spreadsheet." What I
mean is a single sheet within a workbook. The problem of uploading the
spreadsheet to the database is that it's a proprietary program that will pull
whatever's in the cell from the specifically-named sheet into the database.
So if there's a formula in the cell, the uploaded info will be the formula,
not the value. Which, of course, won't work.

But I didn't know about the "Paste special" option - which is exactly the
answer I was looking for.

Thank you again for your help! You've saved me a tremendous amount of time.

- Doug

"Doug" wrote:

How do I copy info from one excel spreadsheet column into another using a
formula rather than use copy/paste? The original spreadsheets are in the
wrong column format and I'm trying to avoid endless copying/pasting.