J
Jason Krug
Hello-
I have been trying to get SQL.REQUEST to work, and not
having much luck. One problem is I am stuck in the
INDIRECT logic, and now I cannot figure another way to do
this?
In the CLOSED workbook, I have a PIVOT TABLE of SALES
DATA Columns are Products Rows are Daily Sales Figuers
A B C
1/1/3 1.1 1.2 1.3
1/2/3 2.1 2.2 2.3
1/3/3 3.1 3.2 3.3
Product A Sold 3.1$ on Jan 3, 2003.
In the OPEN workbook, I am trying to do a SUMMARY report
with:
Todays Sales
WeekToDate Sales
MonthToDate Sales
YearToDate Sales
I have all this working in INDIRECT, but found out AFTER
I did this that INDIRECT has to be in the same sheet. I
have to do this for 2 years of data, so I am trying to
keep the datasheet separate.
Once a date is entered, I calculate the START/END Dates
of each WTD, MTD, YTD totals. Using MATCH, I found the
DATE ROW. Using Match to find the Product Column as
well, I converted these numbers into a reference for
ADDRESS, which got me a reference for a STARTING POINT,
and an END POINT.
DATE = 2/1/2002
=MATCH(H$17,'U:\Daily\LY-02\[scorecard16.xls]SKU-POS'!
$C:$C,0)
PRODUCT = A
=MATCH(B33,'SKU-POS'!$5:$5)
INDIRECT(ADDRESS
(DATE_MATCH_ROW,PRODUCT_MATCH_ROW,,,"[scorecard16.xls]SKU-
POS"))
I then could easily calculate the SUM:
SUM(INDIRECT(ADDRESS(START POINT)):INDIRECT(ADDRESS(END
POINT)), in which ALL these sums are calculated, allowing
the person to choose any date of the year.
==SUM(INDIRECT(ADDRESS($H$12,$B22,,,"[scorecard16.xls]SKU-
POS")):INDIRECT(ADDRESS($I$12,$B22,,,"[scorecard16.xls]
SKU-POS")))
I tried to move it to INDEX formulas.
SUM(INDEX(FIELD)) worked fine.
SUM(INDEX(START POINT):INDEX(END POINT)) behaves like
ADDRESS, in which it works when sheet is openned, and
returns a !REF error when the sheet is closed.
=SUM(INDEX('U:\Daily\LY-02\[scorecard16.xls]SKU-POS'!
A1:AZ400, $H$12,$B22):INDEX('U:\Daily\LY-02
\[scorecard16.xls]SKU-POS'!A1:AZ400, $I$12,$B22))
In working with the SQL.REQUEST, I had hoped to select
the SUM of numbers, working on the range. Since my DATA
is a PIVOT table, I am not able to use column names
reliably. I have started using relative column
names "F1, F2" etc. I can not get my WHERE clause to
return a value... it always returns a N/A error. I
cannot get the correct syntax for WHERE F3 = "1/3/3"
=SELECT F4 FROM AvailableSKUS WHERE to_char
(F3,"MM/DD/YY") = "03/02/02"
I have been stuck on this for a week.
HELP!? PLEASE
--Jason
I have been trying to get SQL.REQUEST to work, and not
having much luck. One problem is I am stuck in the
INDIRECT logic, and now I cannot figure another way to do
this?
In the CLOSED workbook, I have a PIVOT TABLE of SALES
DATA Columns are Products Rows are Daily Sales Figuers
A B C
1/1/3 1.1 1.2 1.3
1/2/3 2.1 2.2 2.3
1/3/3 3.1 3.2 3.3
Product A Sold 3.1$ on Jan 3, 2003.
In the OPEN workbook, I am trying to do a SUMMARY report
with:
Todays Sales
WeekToDate Sales
MonthToDate Sales
YearToDate Sales
I have all this working in INDIRECT, but found out AFTER
I did this that INDIRECT has to be in the same sheet. I
have to do this for 2 years of data, so I am trying to
keep the datasheet separate.
Once a date is entered, I calculate the START/END Dates
of each WTD, MTD, YTD totals. Using MATCH, I found the
DATE ROW. Using Match to find the Product Column as
well, I converted these numbers into a reference for
ADDRESS, which got me a reference for a STARTING POINT,
and an END POINT.
DATE = 2/1/2002
=MATCH(H$17,'U:\Daily\LY-02\[scorecard16.xls]SKU-POS'!
$C:$C,0)
PRODUCT = A
=MATCH(B33,'SKU-POS'!$5:$5)
INDIRECT(ADDRESS
(DATE_MATCH_ROW,PRODUCT_MATCH_ROW,,,"[scorecard16.xls]SKU-
POS"))
I then could easily calculate the SUM:
SUM(INDIRECT(ADDRESS(START POINT)):INDIRECT(ADDRESS(END
POINT)), in which ALL these sums are calculated, allowing
the person to choose any date of the year.
==SUM(INDIRECT(ADDRESS($H$12,$B22,,,"[scorecard16.xls]SKU-
POS")):INDIRECT(ADDRESS($I$12,$B22,,,"[scorecard16.xls]
SKU-POS")))
I tried to move it to INDEX formulas.
SUM(INDEX(FIELD)) worked fine.
SUM(INDEX(START POINT):INDEX(END POINT)) behaves like
ADDRESS, in which it works when sheet is openned, and
returns a !REF error when the sheet is closed.
=SUM(INDEX('U:\Daily\LY-02\[scorecard16.xls]SKU-POS'!
A1:AZ400, $H$12,$B22):INDEX('U:\Daily\LY-02
\[scorecard16.xls]SKU-POS'!A1:AZ400, $I$12,$B22))
In working with the SQL.REQUEST, I had hoped to select
the SUM of numbers, working on the range. Since my DATA
is a PIVOT table, I am not able to use column names
reliably. I have started using relative column
names "F1, F2" etc. I can not get my WHERE clause to
return a value... it always returns a N/A error. I
cannot get the correct syntax for WHERE F3 = "1/3/3"
=SELECT F4 FROM AvailableSKUS WHERE to_char
(F3,"MM/DD/YY") = "03/02/02"
I have been stuck on this for a week.
HELP!? PLEASE
--Jason