Formula Question

[TLC]

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?

 

[Pete_UK]

You have responses at your other post - please do not multi-post.

Pete

 

[smartin]

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?

There is more than one way to do this. Here are a couple I am familiar
with...

This one was recently described by Mike Alexander on his blog. He uses
Access and a little VBA:
http://datapigtechnologies.com/blog/index.php/transposing-a-dataset-in-access/

Another method uses a pivot table. For this to work properly there must
not be any duplication of the key (Date + Location), and the value
fields (Cash, Visa, etc.) must be numeric. Make a Pivot Table out of
your data. Put Date and Location in Row areas, the remaining fields in
the data area. Ensure aggregation is "Sum of" each. This is nearly the
layout you seek.

To refine, first remove the automatic subtotal on Date. Next, copy and
paste the PT as values nearby (e.g., in column F). Now fill in the blank
category cells with this formula in F4, filled right and down:
=IF(ISBLANK(A4),F3,A4)
Fix the date format in F, and do a replace in H to remove the "Sum of "
prefix. Columns F:I are now a normalized version of your original data.

 

[Pete_UK]

Sounds a bit complicated !!

Pete

 

[smartin]

I was hoping this was not too complicated, but of course if there are
any questions I will be happy to elaborate (^: