Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question
You have responses at your other post - please do not multi-post.
Pete On Aug 18, 11:02*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 -- TC |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question
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? 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/...set-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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question
Sounds a bit complicated !!
Pete On Aug 19, 1:52*am, smartin wrote: 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/...ng-a-dataset-i... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question
I was hoping this was not too complicated, but of course if there are
any questions I will be happy to elaborate (^: Pete_UK wrote: Sounds a bit complicated !! Pete On Aug 19, 1:52 am, smartin wrote: 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/...ng-a-dataset-i... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula question | Excel Discussion (Misc queries) | |||
IF Formula Question | Excel Worksheet Functions | |||
Formula Question | Excel Worksheet Functions | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Formula Question | Excel Discussion (Misc queries) |