A
Arvi Laanemets
I have some workbooks with similar design, where some weekly production
information is collected. When the workbook is opened, the presence of
current week entries on sheets is checked, and when needed, then rows for
missing weeks are added - after that users can enter their departments
production info into added rows. On graph sheet is displayed production
dynamics for selected number of weeks.
The table of weeks with start and end times and number of working days and
working hours is calculated in separate workbook, and every departments
workbook gets a copy of it through ODBC query (there are a lot of complex
formulas needed to calculate right number of working days an hours, where
holidays and some pre-holidays working days with shorthened working hours
must be counted, and some additional sheets are needed with a lot of info -
so all this is quite time-consuming and I preferred to do all this
separately).
So I have a (hidden) sheet 'Weeks', where in column A are weeks in format
'yyyy.ww' (ww is week number). Column A is formatted as text, and query
properties are set to preserve the cell formatting.
There is a sheet 'WorkingTime', with week numbers in column A too (a row for
every week). Week numbers are in same form as on 'Weeks' sheet, and are
formatted as text too.
The named range I have the problem with is defined as
Gr_RowX=IF(MATCH(Gr_LastWeek;WorkingTime_Week,0)<Gr_Weeks,Gr_Weeks,MATCH(Gr_
LastWeek,WorkingTime_Week,0))
where:
Gr_LastWeek=INDEX(Week_Week,Gr_LastRow)
which returns the current week from 'Weeks' table, i.e. "2004.34" today;
WorkingTime_Week is the dynamic named range with week numbers from column A
on WorkingTime sheet;
Gr_Weeks is the number of weeks to display on graph, read from SetUp sheet.
So Gr_RowX returns the position of current week in WorkingTime table, except
when the number of rows in WorkingTime table is less than Gr_Weeks. The
problem is, that sometime this works and sometime not. Then I have to modify
the name:
Gr_RowX=IF(MATCH(Gr_LastWeek*1;WorkingTime_Week;0)<Gr_Weeks;Gr_Weeks;MATCH(G
r_LastWeek*1;WorkingTime_Week;0))
and it works agay - until next week, when I have to modify it again to
original form! Sometime Excel interprets week numbers in range
WorkingTime_Week as numbers, sometime as text (remember, that cell format
for all week numbers is text!). It's quite annoying, and worbooks are
practically useless because it.
Has someone an idea how to avoid this problem?
Thanks in advance!
information is collected. When the workbook is opened, the presence of
current week entries on sheets is checked, and when needed, then rows for
missing weeks are added - after that users can enter their departments
production info into added rows. On graph sheet is displayed production
dynamics for selected number of weeks.
The table of weeks with start and end times and number of working days and
working hours is calculated in separate workbook, and every departments
workbook gets a copy of it through ODBC query (there are a lot of complex
formulas needed to calculate right number of working days an hours, where
holidays and some pre-holidays working days with shorthened working hours
must be counted, and some additional sheets are needed with a lot of info -
so all this is quite time-consuming and I preferred to do all this
separately).
So I have a (hidden) sheet 'Weeks', where in column A are weeks in format
'yyyy.ww' (ww is week number). Column A is formatted as text, and query
properties are set to preserve the cell formatting.
There is a sheet 'WorkingTime', with week numbers in column A too (a row for
every week). Week numbers are in same form as on 'Weeks' sheet, and are
formatted as text too.
The named range I have the problem with is defined as
Gr_RowX=IF(MATCH(Gr_LastWeek;WorkingTime_Week,0)<Gr_Weeks,Gr_Weeks,MATCH(Gr_
LastWeek,WorkingTime_Week,0))
where:
Gr_LastWeek=INDEX(Week_Week,Gr_LastRow)
which returns the current week from 'Weeks' table, i.e. "2004.34" today;
WorkingTime_Week is the dynamic named range with week numbers from column A
on WorkingTime sheet;
Gr_Weeks is the number of weeks to display on graph, read from SetUp sheet.
So Gr_RowX returns the position of current week in WorkingTime table, except
when the number of rows in WorkingTime table is less than Gr_Weeks. The
problem is, that sometime this works and sometime not. Then I have to modify
the name:
Gr_RowX=IF(MATCH(Gr_LastWeek*1;WorkingTime_Week;0)<Gr_Weeks;Gr_Weeks;MATCH(G
r_LastWeek*1;WorkingTime_Week;0))
and it works agay - until next week, when I have to modify it again to
original form! Sometime Excel interprets week numbers in range
WorkingTime_Week as numbers, sometime as text (remember, that cell format
for all week numbers is text!). It's quite annoying, and worbooks are
practically useless because it.
Has someone an idea how to avoid this problem?
Thanks in advance!