Colums of data

[Pat]

Is there a smart way, except recording a macro, that will update all columns
with sales info and give me a new week? I enter in all new data from a new
week at the top of the column. I'm using the List function, but not for
this purpose as I can only enter in new data at the bottom.

Lisa Ken
A B C D
1 Aug-22 $5200 Aug-22 $4300
2 Aug-15 $5000 Aug-15 $5100

In the actual worksheet the columns are not that together as above, but have
graphs in-between the sellers. Every seller is on its own sheet and that
sellers columns and graph prints on one page. Therefore I need to keep the
date column for every seller. How do I insert a new row for a new week and
have the date updated automatically, not using a macro or is that the only
way?

Thanks in advance.

 

[Arvi Laanemets]

Hi

My advice is - modify your design a bit!

An example

Add a sheet Sellers, with a sellers list like:
Seller
Lisa
Ken


Define a dynamic named range Sellers (from menu: Insert, Name, Define), with
next formula in RefersTo field:
=OFFSET(Sellers!$A$2,,,COUNTIF(Sellers!$A:$A,"<>")-1,1)

Add a sheet Weeks, with a weeks list like:
Week
2004.34
2004.35


Define a dynamic named range Weeks, with next formula in RefersTo field:
=OFFSET(Weeks!$A$2,,,COUNTIF(Weeks!$A:$A,"<>")-1,1)

Redesign your sales sheet with data like this:
Nr Date Seller Sum Week
15.08.2004 Lisa 5 000.00 $ 2004.34
15.08.2004 Ken 5 100.00 $ 2004.34
16.08.2004 Lisa 6 200.00 $ 2004.34
16.08.2004 Ken 4 600.00 $ 2004.34
1 22.08.2004 Lisa 4 150.00 $ 2004.35
22.08.2004 Ken 4 020.00 $ 2004.35
2 23.08.2004 Lisa 3 890.00 $ 2004.35
23.08.2004 Ken 3 760.00 $ 2004.35


Probably your dates are in different format.
The first column (Nr) contains a formula (I'll give the one from cell A2)
=IF(OR($E2<>Week,$C2<>Seller),"",SUMPRODUCT(($E$2:$E2=Week)*($C$2:$C2=Seller
)))
(there are 2 named ranges used in formula, which are given later, so maybe
you wait with typing in the formula a bit)
You can type in the week number, or you can format the column using data
validation list with named range Weeks as source, and select the week from
drop-down list, or you can compose a formula, which returns the week number
based on entered date.
You can format sellers column using data validation list (named range
Sellers as source).

Define a dynamic named range SalesTbl
=OFFSET(Sales!$A$2,,,COUNTIF(Sales!$B:$B,"<>")-1,5)

Add a sheet Report
A1="Week:"
D1="Seller:"
D2="Sold:"
A3="Date"
B3="Sum"

Format B1 as data validation list with named range Weeks as source. Name the
cell Report!$B$1 as Week
Format E1 as data validation list with named range Sellers as source. Name
the cell Report!$E$1 as Seller
(now you have defined both names, needed in column A of sheet Sales)

Into cell A4 on sheet Report enter the formula
=IF(ISERROR(VLOOKUP(ROW()-3,SalesTbl,2;0)),"",VLOOKUP(ROW()-3,SalesTbl,2,0))
Into cell B4 enter the formula
=IF(A4="","",VLOOKUP(ROW()-3,SalesTbl,4;0))
Copy both formulas for some amount of rows (as much as you think you have in
week for a single seller)
Into cell E2 enter formula to sum weekly sales for seller, like
=SUM(B4:B9)

Create graph(s) based on table in Report sheet

Now, you simply select week and seller, and you get a report and graphs you
needed.


Arvi Laanemets

 

[Patrik Karlstrom]

Thank you, Arvi. I will study this. You gave me a whole book of data.