Hi
Sorry, but I don't have enough time to adjust my example below (it is a
copy-paste from my answer in one Excel-NG's from last year) to your task.
Anyway I think they are similar enough, so you can use it to get some
ideas - a part most interesting for you begins with OrderRepTemp sheet.
*******
On fly, something like this has to work for you (probably you have to
replace all ; in formulas with , - it was too much of them for me to do it
here):
With empty workbook, you create worksheets Orders, Details, Items, OrderRep
and OrdRepTemp
Define named range (Insert.Name.Define)
VAT=YourVAT%
p.e. VAT=0.18
On sheet items, you have columns
Item, MeasuringUnit, UnitPrice - you have to fill the table.
You create 2 named ranges
Items=OFFSET(Items!$A$2;;;COUNTIF(Items!$A:$A;"<>")-1;1)
ItemsTable=OFFSET(Items!$A$2;;;COUNTIF(Items!$A:$A;"<>")-1;3)
(when you have more columns in Items table, replace number 3 in second named
range with your number of columns)
On sheet Orders, you have columns
Order, Date, Order, Customer, OrderSum, OrderVAT
(first Order column is hidden, I needed it while I wanted Date entered as
first, but for lookups I need Order sa leftmost)
A2=IF(C2="";"";C2)
C2=IF(B2="";"";IF(ROW(C2)=2;1;IF(C1="";"";C1+1)))
Define named ranges
Orders=OFFSET(Orders!$C$2;;;COUNTIF(Orders!$B:$B;"<>")-1;1)
OrderTable=OFFSET(Orders!$A$2;;;COUNTIF(Orders!$B:$B;"<>")-1;6)
D2=IF(C2="";"";ROUND(SUMPRODUCT((DetOrder=C2)*(DetSUM))/5;2)*5)
E2=IF(C2="";"";ROUND(SUMPRODUCT((DetOrder=C2)*(DetVAT))/5;2)*5)
NB! 2 last formulas round to 0.05. When you need otherwise, adjust formulas.
Format the cells in range A2:E2 and copy down for so much rows you think you
do need
On sheet Details, you have columns
Order, Date, Customer, Item, MeasUnit, UnitPrice, Amount, Price, VAT, Sum
Define named ranges
DetOrder=OFFSET(Details!$A$2;;;COUNTIF(Details!$A:$A;"<>")-1;1)
DetSum=OFFSET(Details!$J$2;;;COUNTIF(Details!$A:$A;"<>")-1;1)
DetVAT=OFFSET(Details!$I$2;;;COUNTIF(Details!$A:$A;"<>")-1;1)
Format enough of cells (starting from A2) in column A as combo using
Data.Validation.List with Source=Orders
B2=IF(A2="";"";VLOOKUP(A2;OrderTable;2;FALSE))
C2=IF(A2="";"";VLOOKUP(A2;OrderTable;4;FALSE))
Format in column D same number of cells (starting from D2) as in column A as
combo using Data.Validation.List with Source=Items
E2=IF(ISERROR(VLOOKUP(D2;ItemsTable;2;FALSE));"";VLOOKUP(D2;ItemsTable;2;FAL
SE))
F2=IF(A2="";"";VLOOKUP(D2;ItemsTable;3;FALSE))
H2=IF(OR(A2="";G2="");"";F2*G2)
I2=IF(OR(A2="";G2="");"";J2-H2)
J2=IF(OR(A2="";G2="");"";H2/(1-VAT))
NB! In my example, the VAT is calculated from endsum. When it's calculated
from Price, adjust last 2 formulas accordingly.
Format cells in range A2:J2, and copy down - again for so much of rows you
think you need.
It's all you need to enter Order and Order Detail info. Now you do need a
report to print one selected order.
On sheet OrderRepTemp, enter the formula
=IF(Details!$A2=OrdRep!$B$1;Details!A2;"")
and copy it to same range, as table on Details. You can add column names
into 1st row too.
Add 2 columns (Row and Rank)
K2==IF(A2="";"";ROW(A2))
Rank=IF(ISERROR(RANK(K2;K$2:K$xxx;1));"";RANK(K2;K$2:K$xxx;1))
where xxx is number of last row with formulas on Details table. Copy both
formulas too down.
Hide the sheet.
On OrdRep sheet, you must have some cell formatted as Data.Validation.List
with Source=Orders
Get rest of info from Orders Sheet (Date, Customer, etc), using VLOOKUP
function. P.e. with Order in B1:
Date=IF(ISERROR(VLOOKUP(B1;OrderTable;2;FALSE));"";VLOOKUP(B1;OrderTable;2;F
ALSE))
Customer=IF(ISERROR(VLOOKUP(B1;OrderTable;4;FALSE));"";VLOOKUP(B1;OrderTable
;4;FALSE))
You can put them into any cell on sheet, and move them frpm one location to
another.
To get details list into order, enter into some cell for first item code the
formula:
=IF(OFFSET(Details!D1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OFFSET(
Details!D1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
into same row
Unit=IF(OFFSET(Details!E1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OFF
SET(Details!E1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
UnitPrice=IF(OFFSET(Details!F1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"
";OFFSET(Details!F1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
Amount=IF(OFFSET(Details!G1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";O
FFSET(Details!G1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
Price=IF(OFFSET(Details!H1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OF
FSET(Details!H1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
VAT=IF(OFFSET(Details!I1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OFFS
ET(Details!I1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
SUM=IF(OFFSET(Details!J1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OFFS
ET(Details!J1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
Copy the range with those formulas for so much rows you need maximally on
your report
Add summary - you can sum details on report sheet, or you can take them from
Orders sheet using VLOOKUP, or you calculate them from Details sheet using
SUMPRODUCT.
Format report sheet as you like, and add any texts you need to it.
That must be all!
*********
