copying form data

[Crystal Harriman]

I have a workbook that I input customer sales. I then have to look up in
another tab (same workbook) what county that customer is taxable in. I then
have to copy the base sales amount into that county. Is there a way for me
to merge the infomation on the county taxable worksheet with the master
sales data worksheet so that when I input the following scenario it would
automatically copy the base amount I enter into the corresponding county
taxable column?:

Customer Sales Tax Taxable in (County Name)
ABC Co $1.00 .08 $1.00

 

[Arvi Laanemets]

Hi

Somehow I feel you have leaved a lot of usefull information out here! What
about the table where you want this base amount to put in (counties table?)?
And the table with customer information? Have you for every customer a
single row in sales table, which you overwrite again, or do you add new rows
after some time (But then you would have some date colum there too! Again,
with single row for customer, why to have as separate customers table at
all.)? Is there for every customer a single row in county table, or a row
for every county, or otherwise? Is tax same for all counties?

With several entries for customer in sales table, which all are counted in
(p.e. all belong to same month), with different taxes for counties, and an
entry for every customer in counties table, you can consider next design

Counties table/sheet
Counties: County, TaxPercent, Sales, Tax
Define named ranges County, CountyTable
County=OFFSET(Counties!$A$1,,,COUNTIF(Counties!$A:$A,"<>")-1,1)
CountyTable=OFFSET(Counties!$A$1,,,COUNTIF(Counties!$A:$A,"<>")-1,2)
Fill in County column

Customers table/sheet
Customers: Customer, County, Sales, Tax
Define named ranges Customer, County, Sales, Tax, CustTable
Customer=OFFSET(Customers!$A$1,,,COUNTIF(Customers!$A:$A,"<>")-1,1)
CustTable=OFFSET(Customers!$A$1,,,COUNTIF(Customers!$A:$A,"<>")-1,4)
Format a range in County column with Data.Validation.List(Source=County)
Fill Customer and County columns for every customer.

Sales table/sheet
Sales:Customer, Sales, Tax, County
Format a range in Customer column with Data.Validation.List(Source=Customer)
Into cell C2 (Tax) enter the formula
=IF(B2="","",IF(ISERROR(VLOOKUP(D2,CountyTable,2,FALSE)),"",B2*VLOOKUP(D2,Co
untyTable,2,FALSE)))
Into cell D2 enter the formula
=IF(ISERROR(VLOOKUP(A2,CustTable,2,FALSE)),"",VLOOKUP(A2,CustTable,2,FALSE))
Copy both formulas down for some amount of rows
Define named ranges SalesCust, SalesSales, SalesTax, SalesCounty, SalesTable
SalesCust=OFFSET(Sales!$A$1,,,COUNTIF(Sales!$A:$A,"<>")-1,1)
SalesSales=OFFSET(Sales!$B$1,,,COUNTIF(Sales!$A:$A,"<>")-1,1)
SalesTax=OFFSET(Sales!$C$1,,,COUNTIF(Sales!$A:$A,"<>")-1,1)
SalesCounty=OFFSET(Sales!$D$1,,,COUNTIF(Sales!$A:$A,"<>")-1,1)
SalesTable=OFFSET(Sales!$A$1,,,COUNTIF(Sales!$A:$A,"<>")-1,4)

In Customers table:
Into cell C2 enter the formula
=SUMPRODUCT((SalesCust=A1)*(SalesSales))
Into cell D2 enter the formula
=SUMPRODUCT((SalesCust=A1)*(SalesTax))
And copy formulas down for every customer

In Counties table:
Into cell C2 enter the formula
=SUMPRODUCT((SalesCounty=A1)*(SalesSales))
Into cell D2 enter the formula
=SUMPRODUCT((SalesCounty=A1)*(SalesTax))
And copy formulas down for every county

 

[Arvi Laanemets]

Hi again

A correcture at first - in all named range definitions, the first OFFSET
parameter must refer to 2nd row, i.e.
County=OFFSET(Counties!$A$2,,,COUNTIF(Counties!$A:$A,"<>")-1,1), etc.

About getting data about p.e. sales per customer in specific county, you can
1) use autofilter on Customers table;
2) create a cross-table
3) create report sheet.

Cross-table
Add a worksheet p.e. SalesCrosstable
A1="Customer"
A2=IF(ISERROR(Customer),"",Customer), and copy down as much as you have
customers
B1=IF(ISERROR(INDEX(County,COLUMN(B1)-COLUMN($A1))),"",INDEX(County,COLUMN(B
1)-COLUMN($A1))), and copy to right as much you have counties
B2=IF(SUMPRODUCT((SalesCust=$A2)*(SalesCounty=B$1)*(SalesSales))=0;"";SUMPRO
DUCT((SalesCust=$A2)*(SalesCounty=B$1)*(SalesSales))), and copy down and
right to fill the table

Report sheet basics
Create a sheet p.e. CountyReport, with drop-down (Data.validation.List with
Source=County) for county selection in some cell (p.e. in C2)
Into Customers table, add column p.e. Row, where for every customer the row
number is displayed, when Customers.County=CountyReport.County, otherwise an
empty string is displayed.
Into Customers table, add column p.e. Rank left to Customer column (all
named range definitions for customers table are shifted automatically),
where the rank of row number is displayed, when the last is not empty - thus
numbering all records in Customers table where
Customers.County=CountyReport.County.
Create a named range CustRankTable
CustRankTable=OFFSET(Customers!$A$2,,,COUNTIF(Customers!$A:$A,"<>")-1,5)
Hide both added columns
Now you can get information for every customer from selected county into
CountyReport, using VLOOKUP(), like
Customer1=IF(ISERROR(VLOOKUP(1,CustRankTable,2,FALSE)),"",VLOOKUP(1,CustRank
Table,2,FALSE))
Sales1=IF(ISERROR(VLOOKUP(1,CustRankTable,4,FALSE)),"",VLOOKUP(1,CustRankTab
le,4,FALSE))
Customer2=IF(ISERROR(VLOOKUP(2,CustRankTable,2,FALSE)),"",VLOOKUP(2,CustRank
Table,2,FALSE))
etc.