Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TLC TLC is offline
external usenet poster
 
Posts: 23
Default Copy Data from One Spreadsheet to Another ??????

We are creating a huge spreadsheet to integrate data into our Accounting
System and need to know if there is an easy way to make changes to formulas
to pull data accross columns instead of rows.

For example we have data in the original spreadsheet as follows:

Column A = Date
Column B = Location
Column C = Cash
Column D = Visa
Column E = MC
Column F = Amex
Column G = Discover

In the new spreadsheet we want the data to be as follows:
Row 1 = Date, Location, Cash
Row 2 = Date, Location, Visa
Row 3 = Date, Location, MC,
Row 4 = Date, Location, AMex
Row 5 = Date, Location, Discover

SO our formula to pull the information is as follows"
Cell A1 = "=Sheet1!A2"
Cell B1 = "=Sheet1!B2"
Cell C1 = "=Sheet1!C2"

Cell A2 = "=Sheet1!A2"
Cell B2 = "=Sheet1!B2"
Cell C2 = "=Sheet1!D2"

Is there a way to make the column change for the formula in Column C?
--
TC
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Copy Data from One Spreadsheet to Another ??????

In the top cell C1, instead of:
Cell C1 = "=Sheet1!C2"


you could put this in C1, then just copy down all the way:
=OFFSET(Sheet1!$C$2,,ROWS($1:1)-1)

The above will return the desired pattern, viz.:
in Cell C2 = "=Sheet1!D2"
in Cell C3 = "=Sheet1!E2"
etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"TLC" wrote:
We are creating a huge spreadsheet to integrate data into our Accounting
System and need to know if there is an easy way to make changes to formulas
to pull data accross columns instead of rows.

For example we have data in the original spreadsheet as follows:

Column A = Date
Column B = Location
Column C = Cash
Column D = Visa
Column E = MC
Column F = Amex
Column G = Discover

In the new spreadsheet we want the data to be as follows:
Row 1 = Date, Location, Cash
Row 2 = Date, Location, Visa
Row 3 = Date, Location, MC,
Row 4 = Date, Location, AMex
Row 5 = Date, Location, Discover

SO our formula to pull the information is as follows"
Cell A1 = "=Sheet1!A2"
Cell B1 = "=Sheet1!B2"
Cell C1 = "=Sheet1!C2"

Cell A2 = "=Sheet1!A2"
Cell B2 = "=Sheet1!B2"
Cell C2 = "=Sheet1!D2"

Is there a way to make the column change for the formula in Column C?
--
TC

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Copy Data from One Spreadsheet to Another ??????

You can put these formulae in Sheet2 in the cells stated:

A2: =INDEX(Sheet1!A:A,INT((ROW(A1)-1)/5)+2)

B2: =INDEX(Sheet1!B:B,INT((ROW(A1)-1)/5)+2)

C2: =INDEX(Sheet1!C:G,INT((ROW(A1)-1)/5)+2,MOD(ROW(A1)-1,5)+1)

Then just copy these down as far as you need to.

Hope this helps.

Pete

On Aug 18, 10:32*pm, TLC wrote:
We are creating a huge spreadsheet to integrate data into our Accounting
System and need to know if there is an easy way to make changes to formulas
to pull data accross columns instead of rows.

For example we have data in the original spreadsheet as follows:

Column A = Date
Column B = Location
Column C = Cash
Column D = Visa
Column E = MC
Column F = Amex
Column G = Discover

In the new spreadsheet we want the data to be as follows:
Row 1 = Date, Location, Cash
Row 2 = Date, Location, Visa
Row 3 = Date, Location, MC,
Row 4 = Date, Location, AMex
Row 5 = Date, Location, Discover

SO our formula to pull the information is as follows"
Cell A1 = "=Sheet1!A2"
Cell B1 = "=Sheet1!B2"
Cell C1 = "=Sheet1!C2"

Cell A2 = "=Sheet1!A2"
Cell B2 = "=Sheet1!B2"
Cell C2 = "=Sheet1!D2"

Is there a way to make the column change for *the formula in Column C?
--
TC


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TLC TLC is offline
external usenet poster
 
Posts: 23
Default Copy Data from One Spreadsheet to Another ??????

I don't think I made my question clear enough. On the Original Spreadsheet
we have the the following column headers:

Date / Is / Location / 10621 / 10609V / 10607VSO / 10607VTO / 10609D /
10607DSO / etc.

and then below each headeris the data as follows:
03/01/09 / 0106 / Ticket Office Window 6 / $444.00 / $2257.00 / $0.00 /
$0.00 / $0.00 / $0.00 / etc

03/01/09 / 0105 / Ticket Office Window 2 / $1285.00 / $3078.00 / $325.00 /
$0.00 / $0.00 / $0.00 / etc.

I want to be able to pull the Dollar amounts to my other worksheet and have
them flow down by using a formula. When I try either of the formulas listed
here they do not work. Is this possible?

--
TC


"Max" wrote:

In the top cell C1, instead of:
Cell C1 = "=Sheet1!C2"


you could put this in C1, then just copy down all the way:
=OFFSET(Sheet1!$C$2,,ROWS($1:1)-1)

The above will return the desired pattern, viz.:
in Cell C2 = "=Sheet1!D2"
in Cell C3 = "=Sheet1!E2"
etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"TLC" wrote:
We are creating a huge spreadsheet to integrate data into our Accounting
System and need to know if there is an easy way to make changes to formulas
to pull data accross columns instead of rows.

For example we have data in the original spreadsheet as follows:

Column A = Date
Column B = Location
Column C = Cash
Column D = Visa
Column E = MC
Column F = Amex
Column G = Discover

In the new spreadsheet we want the data to be as follows:
Row 1 = Date, Location, Cash
Row 2 = Date, Location, Visa
Row 3 = Date, Location, MC,
Row 4 = Date, Location, AMex
Row 5 = Date, Location, Discover

SO our formula to pull the information is as follows"
Cell A1 = "=Sheet1!A2"
Cell B1 = "=Sheet1!B2"
Cell C1 = "=Sheet1!C2"

Cell A2 = "=Sheet1!A2"
Cell B2 = "=Sheet1!B2"
Cell C2 = "=Sheet1!D2"

Is there a way to make the column change for the formula in Column C?
--
TC

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TLC TLC is offline
external usenet poster
 
Posts: 23
Default Copy Data from One Spreadsheet to Another ??????

I don't think I made my question clear enough. On the Original Spreadsheet
we have the the following column headers:

Date / Is / Location / 10621 / 10609V / 10607VSO / 10607VTO / 10609D /
10607DSO / etc.

and then below each headeris the data as follows:
03/01/09 / 0106 / Ticket Office Window 6 / $444.00 / $2257.00 / $0.00 /
$0.00 / $0.00 / $0.00 / etc

03/01/09 / 0105 / Ticket Office Window 2 / $1285.00 / $3078.00 / $325.00 /
$0.00 / $0.00 / $0.00 / etc.

I want to be able to pull the Dollar amounts to my other worksheet and have
them flow down by using a formula. When I try either of the formulas listed
here they do not work. Is this possible?

--
TC


"Max" wrote:

In the top cell C1, instead of:
Cell C1 = "=Sheet1!C2"


you could put this in C1, then just copy down all the way:
=OFFSET(Sheet1!$C$2,,ROWS($1:1)-1)

The above will return the desired pattern, viz.:
in Cell C2 = "=Sheet1!D2"
in Cell C3 = "=Sheet1!E2"
etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"TLC" wrote:
We are creating a huge spreadsheet to integrate data into our Accounting
System and need to know if there is an easy way to make changes to formulas
to pull data accross columns instead of rows.

For example we have data in the original spreadsheet as follows:

Column A = Date
Column B = Location
Column C = Cash
Column D = Visa
Column E = MC
Column F = Amex
Column G = Discover

In the new spreadsheet we want the data to be as follows:
Row 1 = Date, Location, Cash
Row 2 = Date, Location, Visa
Row 3 = Date, Location, MC,
Row 4 = Date, Location, AMex
Row 5 = Date, Location, Discover

SO our formula to pull the information is as follows"
Cell A1 = "=Sheet1!A2"
Cell B1 = "=Sheet1!B2"
Cell C1 = "=Sheet1!C2"

Cell A2 = "=Sheet1!A2"
Cell B2 = "=Sheet1!B2"
Cell C2 = "=Sheet1!D2"

Is there a way to make the column change for the formula in Column C?
--
TC

--
TC


"TLC" wrote:

We are creating a huge spreadsheet to integrate data into our Accounting
System and need to know if there is an easy way to make changes to formulas
to pull data accross columns instead of rows.

For example we have data in the original spreadsheet as follows:

Column A = Date
Column B = Location
Column C = Cash
Column D = Visa
Column E = MC
Column F = Amex
Column G = Discover

In the new spreadsheet we want the data to be as follows:
Row 1 = Date, Location, Cash
Row 2 = Date, Location, Visa
Row 3 = Date, Location, MC,
Row 4 = Date, Location, AMex
Row 5 = Date, Location, Discover

SO our formula to pull the information is as follows"
Cell A1 = "=Sheet1!A2"
Cell B1 = "=Sheet1!B2"
Cell C1 = "=Sheet1!C2"

Cell A2 = "=Sheet1!A2"
Cell B2 = "=Sheet1!B2"
Cell C2 = "=Sheet1!D2"

Is there a way to make the column change for the formula in Column C?
--
TC



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Copy Data from One Spreadsheet to Another ??????

Assume your source data as described is in Sheet1,
in cols A to I, data from row2 down (you have col headers in row1)

Cols A to C contain data to be repeated 6x down,
while cols D to I contain currency data to be "re-arranged" into a vertical
col

In another sheet,
Put in A2:
=INDEX(Sheet1!A:A,INT((ROWS($1:1)-1)/6)+2)
Copy A2 across to C2

Put in D2:
=INDEX(Sheet1!D:I,INT((ROWS($1:1)-1)/6)+2,MOD(ROWS($1:1)-1,6)+1)
Select A2:D2, fill down as far as required to exhaust the source data.
Format col A as date, col D as currency to taste.

The "6" in the INT and MOD bits in both expressions above correspond to the
number of source cols required to be re-arranged into a vertical col (ie your
source cols D to I). Just adapt this num to suit ..

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"TLC" wrote:
I don't think I made my question clear enough. On the Original Spreadsheet
we have the the following column headers:

Date / Is / Location / 10621 / 10609V / 10607VSO / 10607VTO / 10609D /
10607DSO / etc.

and then below each headeris the data as follows:
03/01/09 / 0106 / Ticket Office Window 6 / $444.00 / $2257.00 / $0.00 /
$0.00 / $0.00 / $0.00 / etc

03/01/09 / 0105 / Ticket Office Window 2 / $1285.00 / $3078.00 / $325.00 /
$0.00 / $0.00 / $0.00 / etc.

I want to be able to pull the Dollar amounts to my other worksheet and have
them flow down by using a formula. When I try either of the formulas listed
here they do not work. Is this possible?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help !! How to Copy a data from one spreadsheet to another Suyog Excel Discussion (Misc queries) 2 April 4th 09 04:56 AM
Copy data to another spreadsheet JIm New Users to Excel 2 February 9th 07 12:28 PM
copy data from one spreadsheet to another one using value in cell Teethless mama Excel Worksheet Functions 0 November 30th 06 05:56 AM
How do I copy spreadsheet data as shown to another spreadsheet? trainer07 Excel Discussion (Misc queries) 2 August 7th 06 09:39 PM
Copy data from one Excel spreadsheet to another gvih2g2 Excel Discussion (Misc queries) 2 March 15th 06 02:26 PM


All times are GMT +1. The time now is 09:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"