Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting Rows/Columns for Copying Formulas
I have two spreadsheets in a workbook as follows:
Source Worksheet contains by customer each product it purchases and the sales for each product (see sample below). Not all customers buy each product. There are also new customers added/deleted each month and new products added/deleted each month. Column A Column B Column C January February..... Customer A Product Code 1 Customer A Product Code 1 100 Customer A Product Code 2 200 Customer A Product Code 3 300 Customer B Product Code 1 500 Customer B Product Code 3 600 Customer B Product Code 4 250 Customer C Product Code 2 350 I have added a column (C) in the above sheet that combines column A & B (=A1&" "&B1) - I need this for what I need to do below (at least that is what I came up with). And, what I want to do in the 2nd sheet of the spreadsheet is list by each customer in a column and then in the correpsponding row show the total sales for each product the customer purchases. So Column A will contain a list of all customers. Row 1 will consist of all the product codes (see sample below). The first empty cell in row 2 (so Customer A's purchases) will add up all of those purchases. Column B will have a formula that adds the total sales of Product Code 1 etc..... I'm OK with getting the customer names & product codes to the 2nd sheet (deleting duplicates etc) where I'm having a problem, because the number of products and customers will change each month is trying to create a macro that will copy the formula the correct number of times and changing the formulas - it can be a straight copy because of absolute references in formula.. Right now what I have done is create vlookups. So for example in cell B2 (in the 2nd sheet) I have a formula that reads: =IF(ISNA(VLOOKUP(A2&" "&$B$1,DATA,2,FALSE)),"",VLOOKUP(A2&" "&$B$1,DATA,2,FALSE)) Range "data" is in the source sheet and the first column in the range is Column C. Second Sheet: Column A Column B Column C Product Code 1 Product Code 2 (assume this is row 1) Customer A 100 200 Customer B 500 Customer C 350 The formula above works great except I want to set up a macro that enters the formula in the 2nd sheet in cell B2 and have it copy down and across equivalent to the number of rows (customers)and number of columns (product codes). The problem is I can't do a straight copy because the formula in cell B2 has the B1 as absolute so that when it's copied down column B the product code remains the same but for Column C the formula is =IF(ISNA(VLOOKUP(A2&" "&$C$1,DATA,2,FALSE)),"",VLOOKUP(A2&" "&$C$1,DATA,2,FALSE)). All of this would be OK if I knew the number of columns I needed to perform the copy and and change the absolute value. Maybe I have went about this the wrong way??? I'm very new to vba and figure there must be away of having it count the number of rows in range and telling it to repeat the same task (move over a cell and replace the absolute cell reference and then copy).... I have looked at arrays and crosstabs and tried those but no luck.... Any help would be appreciated.... Much thanks!!! P.S. Sorry this is so long - I was trying to get as much info as possible to explain my situation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
Formulas | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
Pivot table, how do you exclude counting cells with formulas as a | Excel Worksheet Functions |