Lookup value, but between dates
I am needing some help on this situation. I have the data structured in this way: A B C Res ID Cost Activity Date 1 1234 $20 1/1/2010 2 1234 $25 1/20/2010 3 7432 $15 2/2/2010 4 2574 $45 10/1/2009 5 7432 $65 1/2/2010 It shows by resource (Col A), Hourly Cost (Col B), and Activity date (Col C). Then I have a second sheet, with this format: A B C Res ID Cost Effective Date 1 1234 $15 12/30/2009 2 1234 $18 1/18/2010 3 7432 $12 1/31/2010 4 2574 $43 9/29/2009 5 7432 $67 12/31/2009 Same format, but it shows the standard cost. Effective Date represents the day when this cost becomes available. What I am trying to do is in the first sheet, add a column with the current Cost (Sheet 2) at the moment of the transaction. Example: Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real cost coming from sheet 2 is $15 (because it is since 30/12/2009 to 1/18/2010, when a new cost becomes effective). Sheet 2 can contain two, three, four... x numbers of times the same resource (with different effective date, of course). Does somebody know how to calculate this??? In the meantime I am trying with Index, Match, Sumproduct, Lookup....... Thanks! Cecilia
|
0 |
|
Chechu
3/24/2010 3:08:05 AM
excel.programming
6508 articles. 
2 followers.
8 Replies
866 Views
Look at Chip Pearson's website. 'Excel Redirect' (http://www.cpearson.com) I think you also want to look at the effective data to be the data which is closest to the activity date since the price may change a couple of times. I cn easily do this with a macro but it appears you are looking for a formula solution? -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=190056 http://www.thecodecage.com/forumz
|
|
0 |
|
joel
3/24/2010 9:06:30 AM
Can you sort the second table starting with =TODAY() and running backwards?
If so searching for the Activity Date within the Effective Date range will
return a value from MATCH that provides a starting row/date for the
subsequent Resource search. You could use CONCATENATE to produce a string
representing the range to be searched, eg
=CONCATENATE("B"&(16+F3)&":C21")
(where F3 is the result of the first MATCH or the function itself). Finally
VLOOKUP, referencing this string using INDIRECT() and set to return the
contents one column to the right of the ResID, would give the cost.
"Chechu" wrote:
> I am needing some help on this situation. I have the data structured
> in this way:
>
> A B C
> Res ID Cost Activity Date
> 1 1234 $20 1/1/2010
> 2 1234 $25 1/20/2010
> 3 7432 $15 2/2/2010
> 4 2574 $45 10/1/2009
> 5 7432 $65 1/2/2010
>
> It shows by resource (Col A), Hourly Cost (Col B), and Activity date
> (Col C).
>
> Then I have a second sheet, with this format:
>
> A B C
> Res ID Cost Effective Date
> 1 1234 $15 12/30/2009
> 2 1234 $18 1/18/2010
> 3 7432 $12 1/31/2010
> 4 2574 $43 9/29/2009
> 5 7432 $67 12/31/2009
>
> Same format, but it shows the standard cost. Effective Date represents
> the day when this cost becomes available.
>
> What I am trying to do is in the first sheet, add a column with the
> current Cost (Sheet 2) at the moment of the transaction. Example:
> Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real
> cost coming from sheet 2 is $15 (because it is since 30/12/2009 to
> 1/18/2010, when a new cost becomes effective).
> Sheet 2 can contain two, three, four... x numbers of times the same
> resource (with different effective date, of course).
>
> Does somebody know how to calculate this??? In the meantime I am
> trying with Index, Match, Sumproduct, Lookup.......
>
> Thanks!
> Cecilia
> .
>
|
|
0 |
|
Utf
3/24/2010 10:10:04 PM
On Mar 24, 6:06=A0am, joel <joel.48b...@thecodecage.com> wrote: > Look at > Chip Pearson's website. =A0 > > 'Excel Redirect' (http://www.cpearson.com) > > I think you also want to look at the effective data to be the data > which is closest to the activity date since the price may change a > couple of times. =A0I cn easily do this with a macro but it appears you > are looking for a formula solution? > > -- > joel > ------------------------------------------------------------------------ > joel's Profile: 229 > View this thread:http://www.thecodecage.com/forumz/showthread.php?t=3D190= 056 > > http://www.thecodecage.com/forumz Joel, thanks a lot for your time on this. The effective date indicates when the new cost becomes available. Thus in the first sheet, I need to lookup, for resource XX, the effective cost during the date where the activity has been made. I have no problem with the idea of a macro!! I just started with a formula, since I might need to explain this to other people, and you know, regular Excel users prefer to see how numbers are driven (even if they don't understand the formula behind). But again, no problem with a macro. I'll post in pbart answer what I habe been doing so far, might help. THANKS a lot! Cecilia
|
|
0 |
|
Chechu
3/25/2010 1:16:32 AM
pbart,
Thanks a lot for your help on this. I understand your suggestion,
except when you say "will return a value from MATCH that provides a
starting row/date for the subsequent Resource search". At some point I
think that the formula I started to work uses some similar approach.
This is how it looks like so far:
INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE
DATE"<=3D"ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the
resource">"ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES
ID"=3D"Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effective
Date&ResID)",0))
The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet
2, sorted by Res ID, Eff Date, then with a serie of If I can determine
the end date for the effective rate.
I know, I know, this is confusing... but looks reasonable??? Still
need to work on the N/A error handling, but that's not complex.
THANKS!
Cecilia
On Mar 24, 7:10=A0pm, pbart <pb...@discussions.microsoft.com> wrote:
> Can you sort the second table starting with =3DTODAY() and running backwa=
rds?
>
> If so searching for the Activity Date within the Effective Date range wil=
l
> return a value from MATCH that provides a starting row/date for the
> subsequent Resource search. You could use CONCATENATE to produce a string
> representing the range to be searched, eg
>
> =3DCONCATENATE("B"&(16+F3)&":C21")
>
> (where F3 is the result of the first MATCH or the function itself). =A0Fi=
nally
> VLOOKUP, referencing this string using INDIRECT() and set to return the
> contents one column to the right of the ResID, would give the cost.
>
>
>
> "Chechu" wrote:
> > I am needing some help on this situation. I have the data structured
> > in this way:
>
> > =A0 =A0A =A0 =A0 =A0 B =A0 =A0 =A0 C
> > =A0 =A0Res ID =A0Cost =A0 =A0Activity Date
> > 1 =A01234 =A0 =A0 $20 =A0 =A01/1/2010
> > 2 =A01234 =A0 =A0 $25 =A0 =A01/20/2010
> > 3 =A07432 =A0 =A0 $15 =A0 =A02/2/2010
> > 4 =A02574 =A0 =A0 $45 =A0 =A010/1/2009
> > 5 =A07432 =A0 =A0 $65 =A0 =A01/2/2010
>
> > It shows by resource (Col A), Hourly Cost (Col B), and Activity date
> > (Col C).
>
> > Then I have a second sheet, with this format:
>
> > =A0 =A0A =A0 =A0 =A0 B =A0 =A0 =A0 C
> > =A0 =A0Res ID =A0Cost =A0 =A0Effective Date
> > 1 =A01234 =A0 =A0 $15 =A0 =A012/30/2009
> > 2 =A01234 =A0 =A0 $18 =A0 =A01/18/2010
> > 3 =A07432 =A0 =A0 $12 =A0 =A01/31/2010
> > 4 =A02574 =A0 =A0 $43 =A0 =A09/29/2009
> > 5 =A07432 =A0 =A0 $67 =A0 =A012/31/2009
>
> > Same format, but it shows the standard cost. Effective Date represents
> > the day when this cost becomes available.
>
> > What I am trying to do is in the first sheet, add a column with the
> > current Cost (Sheet 2) at the moment of the transaction. Example:
> > Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real
> > cost coming from sheet 2 is $15 (because it is since 30/12/2009 to
> > 1/18/2010, when a new cost becomes effective).
> > Sheet 2 can contain two, three, four... x numbers of times the same
> > resource (with different effective date, of course).
>
> > Does somebody know how to calculate this??? In the meantime I am
> > trying with Index, Match, Sumproduct, Lookup.......
>
> > Thanks!
> > Cecilia
> > .- Hide quoted text -
>
> - Show quoted text -
|
|
0 |
|
Chechu
3/25/2010 1:35:26 AM
This is the macro solution. My preference is to always use macro
instead of a complicated formula. Macros are easier to debug and can be
documented. Complicated formulas cannot be documented. Macros also
run more effiecently. The macro could be made into a UDF.
Sub AddEffectiveDate()
Set ActivitySht = Sheets("Sheet1")
Set EffectivitySht = Sheets("Sheet2")
With ActivitySht
.Range("D1") = "Effective Cost"
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
EffectiveDate = 0
ResID = .Range("A" & RowCount)
ActivityDate = .Range("C" & RowCount)
'search Effectivity Sheet
With EffectivitySht
Set c = .Columns("A").Find(what:=ResID, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
NewEffectiveDate = c.Offset(0, 2)
'only take costs before the activity dates
'and take latest date
If NewEffectiveDate <= ActivityDate And _
NewEffectiveDate > EffectiveDate Then
NewCost = c.Offset(0, 1)
EffectiveDate = NewEffectiveDate
End If
Set c = .Columns("A").FindNext(after:=c)
Loop While Not c Is Nothing And c.Address <> FirstAddr
End If
End With
If EffectiveDate = 0 Then
.Range("D" & RowCount) = "Not Found"
Else
.Range("D" & RowCount) = NewCost
End If
Next RowCount
End With
End Sub
--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=190056
http://www.thecodecage.com/forumz
|
|
0 |
|
joel
3/25/2010 10:49:52 AM
Chechu
It looks like you now have alternative lines of investigation to follow up:
the first using inbuilt functionality of the spreadsheet and the second
introducing VBA macros.
I do agree with Joel that there are dangers of hiding errors in complicated
formulae. If you adopt the formula approach, I would suggest you break the
process into small steps using additional columns to hold intermediate
results.
For example, the first MATCH would tell you how many rows of the second
sheet should be ignored because they postdate the activity. That number can
be checked for correctness.
The CONCATENATE in the next column uses this value of 'number of rows to
omit' in order to calculate the part of the range on sheet 2 that remains of
interest (the starting cell will be further down the table than the original)
and displays the result as a string. Again this is something you can check.
Finally a VLOOKUP in the next column can be applied to the range (the
INDIRECT(cell) references the string from the previous column) and will find
all information relating to the first instance of the ResID.
The remaining step before your spreadsheet hits an unsuspecting public is to
'hide your workings' by first hiding the extra columns and, possibly,
deselecting headings from the view menu.
I hope you are not offended by my suggesting how you might best arrange such
a calculation but, like Joel, I do rather shy away from complex formulae.
Good luck
PBart
"Chechu" wrote:
> pbart,
>
> Thanks a lot for your help on this. I understand your suggestion,
> except when you say "will return a value from MATCH that provides a
> starting row/date for the subsequent Resource search". At some point I
> think that the formula I started to work uses some similar approach.
> This is how it looks like so far:
>
> INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE
> DATE"<="ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the
> resource">"ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES
> ID"="Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effective
> Date&ResID)",0))
>
> The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet
> 2, sorted by Res ID, Eff Date, then with a serie of If I can determine
> the end date for the effective rate.
>
> I know, I know, this is confusing... but looks reasonable??? Still
> need to work on the N/A error handling, but that's not complex.
>
> THANKS!
> Cecilia
>
>
>
> On Mar 24, 7:10 pm, pbart <pb...@discussions.microsoft.com> wrote:
> > Can you sort the second table starting with =TODAY() and running backwards?
> >
> > If so searching for the Activity Date within the Effective Date range will
> > return a value from MATCH that provides a starting row/date for the
> > subsequent Resource search. You could use CONCATENATE to produce a string
> > representing the range to be searched, eg
> >
> > =CONCATENATE("B"&(16+F3)&":C21")
> >
> > (where F3 is the result of the first MATCH or the function itself). Finally
> > VLOOKUP, referencing this string using INDIRECT() and set to return the
> > contents one column to the right of the ResID, would give the cost.
> >
> >
> >
> > "Chechu" wrote:
> > > I am needing some help on this situation. I have the data structured
> > > in this way:
> >
> > > A B C
> > > Res ID Cost Activity Date
> > > 1 1234 $20 1/1/2010
> > > 2 1234 $25 1/20/2010
> > > 3 7432 $15 2/2/2010
> > > 4 2574 $45 10/1/2009
> > > 5 7432 $65 1/2/2010
> >
> > > It shows by resource (Col A), Hourly Cost (Col B), and Activity date
> > > (Col C).
> >
> > > Then I have a second sheet, with this format:
> >
> > > A B C
> > > Res ID Cost Effective Date
> > > 1 1234 $15 12/30/2009
> > > 2 1234 $18 1/18/2010
> > > 3 7432 $12 1/31/2010
> > > 4 2574 $43 9/29/2009
> > > 5 7432 $67 12/31/2009
> >
> > > Same format, but it shows the standard cost. Effective Date represents
> > > the day when this cost becomes available.
> >
> > > What I am trying to do is in the first sheet, add a column with the
> > > current Cost (Sheet 2) at the moment of the transaction. Example:
> > > Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real
> > > cost coming from sheet 2 is $15 (because it is since 30/12/2009 to
> > > 1/18/2010, when a new cost becomes effective).
> > > Sheet 2 can contain two, three, four... x numbers of times the same
> > > resource (with different effective date, of course).
> >
> > > Does somebody know how to calculate this??? In the meantime I am
> > > trying with Index, Match, Sumproduct, Lookup.......
> >
> > > Thanks!
> > > Cecilia
> > > .- Hide quoted text -
> >
> > - Show quoted text -
>
> .
>
|
|
0 |
|
Utf
3/25/2010 12:54:01 PM
On Mar 25, 9:54=A0am, pbart <pb...@discussions.microsoft.com> wrote:
> Chechu
>
> It looks like you now have alternative lines of investigation to follow u=
p:
> the first using inbuilt functionality of the spreadsheet and the second
> introducing VBA macros.
>
> I do agree with Joel that there are dangers of hiding errors in complicat=
ed
> formulae. =A0If you adopt the formula approach, I would suggest you break=
the
> process into small steps using additional columns to hold intermediate
> results.
>
> For example, the first MATCH would tell you how many rows of the second
> sheet should be ignored because they postdate the activity. =A0That numbe=
r can
> be checked for correctness.
>
> The CONCATENATE in the next column uses this value of 'number of rows to
> omit' in order to calculate the part of the range on sheet 2 that remains=
of
> interest (the starting cell will be further down the table than the origi=
nal)
> and displays the result as a string. =A0Again this is something you can c=
heck.
>
> Finally a VLOOKUP in the next column can be applied to the range (the
> INDIRECT(cell) references the string from the previous column) and will f=
ind
> all information relating to the first instance of the ResID. =A0
>
> The remaining step before your spreadsheet hits an unsuspecting public is=
to
> 'hide your workings' by first hiding the extra columns and, possibly,
> deselecting headings from the view menu.
>
> I hope you are not offended by my suggesting how you might best arrange s=
uch
> a calculation but, like Joel, I do rather shy away from complex formulae.
>
> Good luck
>
> PBart
>
>
>
> "Chechu" wrote:
> > pbart,
>
> > Thanks a lot for your help on this. I understand your suggestion,
> > except when you say "will return a value from MATCH that provides a
> > starting row/date for the subsequent Resource search". At some point I
> > think that the formula I started to work uses some similar approach.
> > This is how it looks like so far:
>
> > INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE
> > DATE"<=3D"ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the
> > resource">"ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES
> > ID"=3D"Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effectiv=
e
> > Date&ResID)",0))
>
> > The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet
> > 2, sorted by Res ID, Eff Date, then with a serie of If I can determine
> > the end date for the effective rate.
>
> > I know, I know, this is confusing... but looks reasonable??? Still
> > need to work on the N/A error handling, but that's not complex.
>
> > THANKS!
> > Cecilia
>
> > On Mar 24, 7:10 pm, pbart <pb...@discussions.microsoft.com> wrote:
> > > Can you sort the second table starting with =3DTODAY() and running ba=
ckwards?
>
> > > If so searching for the Activity Date within the Effective Date range=
will
> > > return a value from MATCH that provides a starting row/date for the
> > > subsequent Resource search. You could use CONCATENATE to produce a st=
ring
> > > representing the range to be searched, eg
>
> > > =3DCONCATENATE("B"&(16+F3)&":C21")
>
> > > (where F3 is the result of the first MATCH or the function itself). =
=A0Finally
> > > VLOOKUP, referencing this string using INDIRECT() and set to return t=
he
> > > contents one column to the right of the ResID, would give the cost.
>
> > > "Chechu" wrote:
> > > > I am needing some help on this situation. I have the data structure=
d
> > > > in this way:
>
> > > > =A0 =A0A =A0 =A0 =A0 B =A0 =A0 =A0 C
> > > > =A0 =A0Res ID =A0Cost =A0 =A0Activity Date
> > > > 1 =A01234 =A0 =A0 $20 =A0 =A01/1/2010
> > > > 2 =A01234 =A0 =A0 $25 =A0 =A01/20/2010
> > > > 3 =A07432 =A0 =A0 $15 =A0 =A02/2/2010
> > > > 4 =A02574 =A0 =A0 $45 =A0 =A010/1/2009
> > > > 5 =A07432 =A0 =A0 $65 =A0 =A01/2/2010
>
> > > > It shows by resource (Col A), Hourly Cost (Col B), and Activity dat=
e
> > > > (Col C).
>
> > > > Then I have a second sheet, with this format:
>
> > > > =A0 =A0A =A0 =A0 =A0 B =A0 =A0 =A0 C
> > > > =A0 =A0Res ID =A0Cost =A0 =A0Effective Date
> > > > 1 =A01234 =A0 =A0 $15 =A0 =A012/30/2009
> > > > 2 =A01234 =A0 =A0 $18 =A0 =A01/18/2010
> > > > 3 =A07432 =A0 =A0 $12 =A0 =A01/31/2010
> > > > 4 =A02574 =A0 =A0 $43 =A0 =A09/29/2009
> > > > 5 =A07432 =A0 =A0 $67 =A0 =A012/31/2009
>
> > > > Same format, but it shows the standard cost. Effective Date represe=
nts
> > > > the day when this cost becomes available.
>
> > > > What I am trying to do is in the first sheet, add a column with the
> > > > current Cost (Sheet 2) at the moment of the transaction. Example:
> > > > Resource 1234, shows $20 cost on an activity on 1/1/2010, but the r=
eal
> > > > cost coming from sheet 2 is $15 (because it is since 30/12/2009 to
> > > > 1/18/2010, when a new cost becomes effective).
> > > > Sheet 2 can contain two, three, four... x numbers of times the same
> > > > resource (with different effective date, of course).
>
> > > > Does somebody know how to calculate this??? In the meantime I am
> > > > trying with Index, Match, Sumproduct, Lookup.......
>
> > > > Thanks!
> > > > Cecilia
> > > > .- Hide quoted text -
>
> > > - Show quoted text -
>
> > .- Hide quoted text -
>
> - Show quoted text -
Joel and PBart,
I just can say 1.000.000 of THANKS for your help and guidance on this.
I will try first with the Macro, since my Excel is crashing with all
these formulas. Demo version is OK, but real data is too large for my
humble Excel 2003...
PBart, great suggestions, and I really appreciate it. I will follow
your logic if I can=92t make it with the Macro.
Joel, your Macro is the kind of code that we love, short and clear!! I
think that I can customize it based on the real file, I will start
with it.
THANKS!!!
Cecilia
|
|
0 |
|
Chechu
3/26/2010 1:03:25 AM
On Mar 25, 10:03=A0pm, Chechu <cecic...@hotmail.com> wrote:
> On Mar 25, 9:54=A0am, pbart <pb...@discussions.microsoft.com> wrote:
>
>
>
>
>
> > Chechu
>
> > It looks like you now have alternative lines of investigation to follow=
up:
> > the first using inbuilt functionality of the spreadsheet and the second
> > introducing VBA macros.
>
> > I do agree with Joel that there are dangers of hiding errors in complic=
ated
> > formulae. =A0If you adopt the formula approach, I would suggest you bre=
ak the
> > process into small steps using additional columns to hold intermediate
> > results.
>
> > For example, the first MATCH would tell you how many rows of the second
> > sheet should be ignored because they postdate the activity. =A0That num=
ber can
> > be checked for correctness.
>
> > The CONCATENATE in the next column uses this value of 'number of rows t=
o
> > omit' in order to calculate the part of the range on sheet 2 that remai=
ns of
> > interest (the starting cell will be further down the table than the ori=
ginal)
> > and displays the result as a string. =A0Again this is something you can=
check.
>
> > Finally a VLOOKUP in the next column can be applied to the range (the
> > INDIRECT(cell) references the string from the previous column) and will=
find
> > all information relating to the first instance of the ResID. =A0
>
> > The remaining step before your spreadsheet hits an unsuspecting public =
is to
> > 'hide your workings' by first hiding the extra columns and, possibly,
> > deselecting headings from the view menu.
>
> > I hope you are not offended by my suggesting how you might best arrange=
such
> > a calculation but, like Joel, I do rather shy away from complex formula=
e.
>
> > Good luck
>
> > PBart
>
> > "Chechu" wrote:
> > > pbart,
>
> > > Thanks a lot for your help on this. I understand your suggestion,
> > > except when you say "will return a value from MATCH that provides a
> > > starting row/date for the subsequent Resource search". At some point =
I
> > > think that the formula I started to work uses some similar approach.
> > > This is how it looks like so far:
>
> > > INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE
> > > DATE"<=3D"ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the
> > > resource">"ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES
> > > ID"=3D"Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effect=
ive
> > > Date&ResID)",0))
>
> > > The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet
> > > 2, sorted by Res ID, Eff Date, then with a serie of If I can determin=
e
> > > the end date for the effective rate.
>
> > > I know, I know, this is confusing... but looks reasonable??? Still
> > > need to work on the N/A error handling, but that's not complex.
>
> > > THANKS!
> > > Cecilia
>
> > > On Mar 24, 7:10 pm, pbart <pb...@discussions.microsoft.com> wrote:
> > > > Can you sort the second table starting with =3DTODAY() and running =
backwards?
>
> > > > If so searching for the Activity Date within the Effective Date ran=
ge will
> > > > return a value from MATCH that provides a starting row/date for the
> > > > subsequent Resource search. You could use CONCATENATE to produce a =
string
> > > > representing the range to be searched, eg
>
> > > > =3DCONCATENATE("B"&(16+F3)&":C21")
>
> > > > (where F3 is the result of the first MATCH or the function itself).=
=A0Finally
> > > > VLOOKUP, referencing this string using INDIRECT() and set to return=
the
> > > > contents one column to the right of the ResID, would give the cost.
>
> > > > "Chechu" wrote:
> > > > > I am needing some help on this situation. I have the data structu=
red
> > > > > in this way:
>
> > > > > =A0 =A0A =A0 =A0 =A0 B =A0 =A0 =A0 C
> > > > > =A0 =A0Res ID =A0Cost =A0 =A0Activity Date
> > > > > 1 =A01234 =A0 =A0 $20 =A0 =A01/1/2010
> > > > > 2 =A01234 =A0 =A0 $25 =A0 =A01/20/2010
> > > > > 3 =A07432 =A0 =A0 $15 =A0 =A02/2/2010
> > > > > 4 =A02574 =A0 =A0 $45 =A0 =A010/1/2009
> > > > > 5 =A07432 =A0 =A0 $65 =A0 =A01/2/2010
>
> > > > > It shows by resource (Col A), Hourly Cost (Col B), and Activity d=
ate
> > > > > (Col C).
>
> > > > > Then I have a second sheet, with this format:
>
> > > > > =A0 =A0A =A0 =A0 =A0 B =A0 =A0 =A0 C
> > > > > =A0 =A0Res ID =A0Cost =A0 =A0Effective Date
> > > > > 1 =A01234 =A0 =A0 $15 =A0 =A012/30/2009
> > > > > 2 =A01234 =A0 =A0 $18 =A0 =A01/18/2010
> > > > > 3 =A07432 =A0 =A0 $12 =A0 =A01/31/2010
> > > > > 4 =A02574 =A0 =A0 $43 =A0 =A09/29/2009
> > > > > 5 =A07432 =A0 =A0 $67 =A0 =A012/31/2009
>
> > > > > Same format, but it shows the standard cost. Effective Date repre=
sents
> > > > > the day when this cost becomes available.
>
> > > > > What I am trying to do is in the first sheet, add a column with t=
he
> > > > > current Cost (Sheet 2) at the moment of the transaction. Example:
> > > > > Resource 1234, shows $20 cost on an activity on 1/1/2010, but the=
real
> > > > > cost coming from sheet 2 is $15 (because it is since 30/12/2009 t=
o
> > > > > 1/18/2010, when a new cost becomes effective).
> > > > > Sheet 2 can contain two, three, four... x numbers of times the sa=
me
> > > > > resource (with different effective date, of course).
>
> > > > > Does somebody know how to calculate this??? In the meantime I am
> > > > > trying with Index, Match, Sumproduct, Lookup.......
>
> > > > > Thanks!
> > > > > Cecilia
> > > > > .- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > .- Hide quoted text -
>
> > - Show quoted text -
>
> Joel and PBart,
> I just can say 1.000.000 of THANKS for your help and guidance on this.
> I will try first with the Macro, since my Excel is crashing with all
> these formulas. Demo version is OK, but real data is too large for my
> humble Excel 2003...
> PBart, great suggestions, and I really appreciate it. I will follow
> your logic if I can=92t make it with the Macro.
> Joel, your Macro is the kind of code that we love, short and clear!! I
> think that I can customize it based on the real file, I will start
> with it.
> THANKS!!!
> Cecilia- Hide quoted text -
>
> - Show quoted text -
Joel and PBart,
I customized the Macro based on some data quality issues I have, and
it works just PERFECT. Also I learned a lot which is fantastic. THANKS
a lot for your help on this!!!!!
Cecilia
|
|
0 |
|
Chechu
3/27/2010 11:42:57 AM
Similar Artilces:
Payment date issues
Is there a way to avoid the irritation of Money 2005 refusing to make an electronic payment because it thinks there is not enough time to make the payment deadline specified in a recurring payment? Having to edit the occurrence is a real pain in the xxxx. Pls reply to mail address. Thanks. wizard_chef@hotmail.com Money gets this information from the financial institution making the payment -- so perhaps they can help you with the deadlines. -- <stokely@uab.edu> wrote in message news:1120064173.782289.14860@g14g2000cwa.googlegroups.com... > Is there a way to avoid the irritati...
change x axis date scale on multiple charts quarterly
Have multiple charts to update weekly during a calendar quarter, need to change the x axis date scale on every chart quarterly. Can the x axis date range be changed on multiple charts simultaneously ? If your x axis range comes from a series on your spreadsheet, changing the values in the spreadsheet will change all the charts which use that series to define the x axis. -- David Biddulph Paul wrote: > Have multiple charts to update weekly during a calendar quarter, need > to change the x axis date scale on every chart quarterly. > Can the x axis date range be changed on multiple...
Where to modify Auto Numbering Value?
There are some Auto Numbering Values for Quotatio ID, Order ID, Invoice ID and Cases ID which will automatically increase. Dose anybody know what 's the rule of those ID increase? Will the cases ID not enough to hold too many cases because it is looks like: CAS-01003 and cannot change, what if we got CAS-99999 then what will happen to next? Thank you very much! Raymond Hi Raymond, check Autonumbering in System Customization Mike "Raymond" <xin421@yahoo.com> wrote in message news:90cf01c3ea94$31bbda80$a301280a@phx.gbl... > There are some Auto Numbering Val...
Count values in Cells Accross
I want to calculate the values in cell A1 across. Eg. Cell A1 has a value of 36. I want to calculate the values across in the 36 non blank cells located in the same worksheet. Any help appreciated. Thanks in advance Hi see your other post -- Regards Frank Kabel Frankfurt, Germany Shari wrote: > I want to calculate the values in cell A1 across. > > Eg. Cell A1 has a value of 36. I want to calculate the values across > in the 36 non blank cells located in the same worksheet. > > Any help appreciated. > Thanks in advance ...
Fill date series in table
I have an attendance sheet that 1 day per week. I want to extend a series across the top row. (I.E. Jan 1, Jan 8, Jan 15, Jan 22, etc.). The sheets are formatted as tables. When I grab the handle to copy the cells it will only copy, not fill the series. How can I get a weekly series? In A1 enter: 1/1/2010 In B1 enter: =A1+7 and copy across -- Gary''s Student - gsnu200909 If you put Jan 1 in the first cell, Jan 8 in cell next to it and then select **both** of these cells... you can now copy this selection across the row and the series will fill in automatically for...
changing cell value depending on list selection
I have a long coloumn of data which is entered by selecting from a dro down list (data/validation/list)i want a numerical value to b associated with each value from the drop down list. I would also lik this value to be dependent on another value in another cell. ie: A B C one build 1 two three build 2 Coloumns A and B are drop down lists and C is a numerical value tha appears only when Build is in coloumn B and the value depends on wha is entered in coloumn A. Hope this isnt too confusing! Thanks Rober -- Message posted...
Need to change date in field to whatever I want
I have a database for my research. I need to change the date, so that I do not have any restraints on my admissions. I have checked the data field for date of admission and it looks exactly the same in the data field, but for some reason, the date will not allow me to move forward with 2010. I am not sure what to do. Can you please help. This seems to be the only field that does not change to what I need it to change to. I currently am able to use the date for 2010. in other date fields except for one field. Please any suggestions. I am very new in setting or using access. ...
can't move the items. could not complete the operation. One or more parameters value not valid
Hi I am using Outlook 2003, previously used 2002 / XP. I have one PST file (2002 format) that seems to have got corrupt. I moved half the content over fine to a new PST (2003 format) but a number messages give me the following error "can't move the items. could not complete the operation. One or more parameters value not valid". I then created a new PST file (2002 format) and just copied the messages over - it created the folders ok but no messages, right at the end I got the following error "Can't move the items. Unable to move or copy folders. Can...
Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...
Average time from date time column?
I have a column that is in general date format, 6/19/2007 5:34:23 PM. In another column I have a value between 10 and around 50. What I have to do is get the average of this number by time in a day. So if the number is 10 all day, then the average is 10. If the number is changed during the day, say its 5 for half the day then 10 for the other half of the day, then the number I'm looking for is 7.5. If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day then the number would be around 8.5..... if you follow what I'm trying to say. So far I have been a...
Add remark for count of listed value
I made a report with 6 subreports on it to show the total count of the variable values of 6 fields in a table. It's briefly illustrated as follows: Total No. of participant: 50 (Records in the table) Occupation Category: ( a Field in the table) Education 15 Medical 23 Business 12 Registration: ( a Field in the table) Email 18 Phone 12 Other 20 My boss wanted me to include a remark next to the count for the listed value of ea...
is it possible to show a cell value using IF(AND function
I would like to show the value of Cell A1 in Cell A3 , what do I need to replace "=A1" portion of formula The cell format is set for numbers and there is also some conditional formating Formula contained in cell A3 =IF(AND(A1>0.1,A1<99),"=A1","") Displays Result as =A1 Thanks in advance all assistance or guidance greatly appreciated. Hi Almost there!! Just replace the "=A1" with A1 =IF(AND(A1>0.1,A1<99),A1,"") -- Regards Roger Govier "FennisDuck" <Fennis@Menace.AOL.com> wrote in message news:%...
Competitor Lookup
When adding a competitor to an opportunity, the lookup only displays the name. Is there any way that this can be changed? I modified the lookup view, but can't figure out where that view is even used! The Opportunity/Competitor lookup is a special 'many to many' relationship and cannot be customized. -- Philip Richardson | Senior Program Manager | Customization and Settings | Microsoft CRM | Office Business Platform Group | philiprichardson.org "Cindy" <CindyMikeworth@newsgroups.nospam> wrote in message news:%23lYuJF9EHHA.1784@TK2MSFTNGP06.phx.gbl... ...
Voiding Open Invoice with future dated invoice
I have some invoices that were posted with a date of 01-25-2018 How can this be voided Mark, In the first place, why is 2018 created as a fiscal period? Besides that, the course of action will depend on 2 things: a) If you've posted the invoice and have not applied any payments. Under this scenario, 1) if you have not posted the invoice in GL -- it's already posted in payables -- delete in the GL batch for the transaction(s). Now, void the payables invoice(s) and once again delete the batches in GL. 2) if you've posted in GL, just void the payables invoice and post to GL a...
how do I lookup multiple values
Trying to lookup a value based on both Horizon and Vertical columns…. A B C 1 JAN FEB MAR 2 Ice-cream .5 .6 .7 3 Hot Cocoa 1.0 1.1 1.25 4 Coffee 1.5 1.3 1.0 5 Soda .8 .6 .9 How do I look up the price of ice - cream in the month of Jan. or lookup the price of coffee in Mar based on an input from user on both product and month? I can’t figure out which function to use. Please advice. =INDEX(B2:D:10,MATCH("Ice Cream",A2:A10,0),MATCH("Jan",B1:D1,0)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Lisa" <Lisa@disc...
Pivot tables remember values that are not available anymore
Hello everyone I would like to ask you is it is possible to reset the fields in a pivot table to make disapear values that they are not anymore available on the data form the selection-criteria when a fields is drill down example lets make a simple table like :: uno dos tres a q 1 s q 2 d q 3 f q 4 g q 5 a w 6 s w 7 d w 8 f w 9 g w 0 make a pivot table and drilldown "uno" (row fields), drillacross "dos" (column fields) and "tres" as Data-Items so in the selection criteria for "uno" ...
VALUE ERROR #3
I have a sheet that is doing a calculation where I get a VALUE error. In cell g5 the calculation is as follows: =if(E5<>0,D5-E5,0) The problem is that E5 also has a calculation that involes text. This is the calculation ''=IF(ISERROR(INDIRECT("'"&TEXT($A7,"dd-mm-yy")&"'!$J$11")),"",INDIRECT("'"&TEXT($A7,"dd-mm-yy")&"'!$J$11")) Thanks in advance for any help. Are the cells D5 and E5 both displaying numeric values? For example, what is the result of = E5*2 and =D5*2?...
Allow searching of date fields to allow wildcard characters
For fields such as birthdays and expiry dates, customers often want to search the database for a list of contacts with birthdays. It would be great if you would allow wildcards in date field searches so that we could search for all birthdays that occur in a specific month. The year is not always relevant when performing date searches. Birthdays is a common search performed by customers in the financial sector as well as other industries. Currently, we have to add an additional text field to facilitate this type of search. It adds confusion to the users, in some cases double data entry...
Microsoft Query is changing a negative number into a date
I am using Microsoft Query to pull a subset of a large excel file however two of my columns which are suppose to be numbers are not pulling in the negative numbers. If I look at the query in microsoft database the number shows up as a date/time string. I've tried formatting the original number as a number but the query still won't read the negative number. How do I get the query to read a negative number as a number and not a date. Hi Highlight the columns and format as a date "mowens" wrote: > I am using Microsoft Query to pull a subset of a large ex...
Syntax to write a null value inside a sql Insert string?
Is there a way to represent the null value as a string (destination: varchar column) the way that the following string represents a null value for a date? Public Const cNullDate As Date = #12:00:00 AM# Writing to an Access 2003, (conversion) table with VS2005 in VB.NET. Within the table is a field named VSubType1 with a validation rule of: "In ('P','O') Or Is Null" The declare/move/insert statements look like this (shortened example): Dim intRecNbr As Integer = 0 Dim VSubType1 As Object (defined as Text 1 column in Access) Dim V1...
Date and Auto Fill
I'm trying to auto fill a column with dates. Everytime I I use the auto fill command it changes the year rather than the day. I do have "day" selected in the fill series command. What am I doing wrong?? Sounds as though you're talking about Excel, not Access, the database product that's part of Office Professional, and the focus of this particular newsgroup. You'd be better off reposting to a newsgroup related to Excel. When you repost, it would probably help the responder to know what version of Excel you're using, and possibly the operating system on ...
'Date' problem
cell a1: =today() if a1 is 'October 7, 2005' i need b1 to be '10/1/05' b2: '10/2/05' b3: '10/3/05' etc...... Any ideas -- comotoma ----------------------------------------------------------------------- comotoman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2729 View this thread: http://www.excelforum.com/showthread.php?threadid=47423 I assume that in Col B you just want to fill in the dates from the start of OCT (10/1) to the date in A1. With the formula "=Today()" in A1, enter the following formula in B1: =EO...
Date Range on report
Hello, I have a query that asks for a "begining date" and "end date" as parameters. Is there anyway to poulate these two dates on a report so that the user knows the time frame (they just keyed) of the report? I'm trying to summarize results for employees by week. You can refer to the parameters in the report. You can set the ControlSource property for a text box to something like: ="Reporting between " & [Beginning Date] & " and " & [End Date] -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) &qu...
How do I create charts having non absolute reference values?
Not sure what you're asking. If you want to change an axis so it doesn't begin at zero, double click on it and enter your own scaling parameters. If that's not it, try using TWO sentences to explain what you need. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "rvboxel" <rvboxel@discussions.microsoft.com> wrote in message news:97BB5A7C-5AD5-4D84-83A1-02E8B96D2FAE@microsoft.com... > ...
Combobox: value from third in stead of first column
I have a combo box with 3 columns. When I choose one of the combo's rows, the combobox gets the value from the first column. Is it possible to have the combobox get the value of the third column? I tried changing the 'dependent' column property to 3 but that doesn't help. Thanks in advance, John "John" <john@smith.com> wrote in message news:fngek7$km7$1@textnews.wanadoo.nl... >I have a combo box with 3 columns. When I choose one of the combo's rows, >the combobox gets the value from the first column. Is it possible to have >the combobox ge...
Is there a way to avoid the irritation of Money 2005 refusing to make an electronic payment because it thinks there is not enough time to make the payment deadline specified in a recurring payment? Having to edit the occurrence is a real pain in the xxxx. Pls reply to mail address. Thanks. wizard_chef@hotmail.com Money gets this information from the financial institution making the payment -- so perhaps they can help you with the deadlines. -- <stokely@uab.edu> wrote in message news:1120064173.782289.14860@g14g2000cwa.googlegroups.com... > Is there a way to avoid the irritati...
change x axis date scale on multiple charts quarterly
Have multiple charts to update weekly during a calendar quarter, need to change the x axis date scale on every chart quarterly. Can the x axis date range be changed on multiple charts simultaneously ? If your x axis range comes from a series on your spreadsheet, changing the values in the spreadsheet will change all the charts which use that series to define the x axis. -- David Biddulph Paul wrote: > Have multiple charts to update weekly during a calendar quarter, need > to change the x axis date scale on every chart quarterly. > Can the x axis date range be changed on multiple...
Where to modify Auto Numbering Value?
There are some Auto Numbering Values for Quotatio ID, Order ID, Invoice ID and Cases ID which will automatically increase. Dose anybody know what 's the rule of those ID increase? Will the cases ID not enough to hold too many cases because it is looks like: CAS-01003 and cannot change, what if we got CAS-99999 then what will happen to next? Thank you very much! Raymond Hi Raymond, check Autonumbering in System Customization Mike "Raymond" <xin421@yahoo.com> wrote in message news:90cf01c3ea94$31bbda80$a301280a@phx.gbl... > There are some Auto Numbering Val...
Count values in Cells Accross
I want to calculate the values in cell A1 across. Eg. Cell A1 has a value of 36. I want to calculate the values across in the 36 non blank cells located in the same worksheet. Any help appreciated. Thanks in advance Hi see your other post -- Regards Frank Kabel Frankfurt, Germany Shari wrote: > I want to calculate the values in cell A1 across. > > Eg. Cell A1 has a value of 36. I want to calculate the values across > in the 36 non blank cells located in the same worksheet. > > Any help appreciated. > Thanks in advance ...
Fill date series in table
I have an attendance sheet that 1 day per week. I want to extend a series across the top row. (I.E. Jan 1, Jan 8, Jan 15, Jan 22, etc.). The sheets are formatted as tables. When I grab the handle to copy the cells it will only copy, not fill the series. How can I get a weekly series? In A1 enter: 1/1/2010 In B1 enter: =A1+7 and copy across -- Gary''s Student - gsnu200909 If you put Jan 1 in the first cell, Jan 8 in cell next to it and then select **both** of these cells... you can now copy this selection across the row and the series will fill in automatically for...
changing cell value depending on list selection
I have a long coloumn of data which is entered by selecting from a dro down list (data/validation/list)i want a numerical value to b associated with each value from the drop down list. I would also lik this value to be dependent on another value in another cell. ie: A B C one build 1 two three build 2 Coloumns A and B are drop down lists and C is a numerical value tha appears only when Build is in coloumn B and the value depends on wha is entered in coloumn A. Hope this isnt too confusing! Thanks Rober -- Message posted...
Need to change date in field to whatever I want
I have a database for my research. I need to change the date, so that I do not have any restraints on my admissions. I have checked the data field for date of admission and it looks exactly the same in the data field, but for some reason, the date will not allow me to move forward with 2010. I am not sure what to do. Can you please help. This seems to be the only field that does not change to what I need it to change to. I currently am able to use the date for 2010. in other date fields except for one field. Please any suggestions. I am very new in setting or using access. ...
can't move the items. could not complete the operation. One or more parameters value not valid
Hi I am using Outlook 2003, previously used 2002 / XP. I have one PST file (2002 format) that seems to have got corrupt. I moved half the content over fine to a new PST (2003 format) but a number messages give me the following error "can't move the items. could not complete the operation. One or more parameters value not valid". I then created a new PST file (2002 format) and just copied the messages over - it created the folders ok but no messages, right at the end I got the following error "Can't move the items. Unable to move or copy folders. Can...
Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...
Average time from date time column?
I have a column that is in general date format, 6/19/2007 5:34:23 PM. In another column I have a value between 10 and around 50. What I have to do is get the average of this number by time in a day. So if the number is 10 all day, then the average is 10. If the number is changed during the day, say its 5 for half the day then 10 for the other half of the day, then the number I'm looking for is 7.5. If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day then the number would be around 8.5..... if you follow what I'm trying to say. So far I have been a...
Add remark for count of listed value
I made a report with 6 subreports on it to show the total count of the variable values of 6 fields in a table. It's briefly illustrated as follows: Total No. of participant: 50 (Records in the table) Occupation Category: ( a Field in the table) Education 15 Medical 23 Business 12 Registration: ( a Field in the table) Email 18 Phone 12 Other 20 My boss wanted me to include a remark next to the count for the listed value of ea...
is it possible to show a cell value using IF(AND function
I would like to show the value of Cell A1 in Cell A3 , what do I need to replace "=A1" portion of formula The cell format is set for numbers and there is also some conditional formating Formula contained in cell A3 =IF(AND(A1>0.1,A1<99),"=A1","") Displays Result as =A1 Thanks in advance all assistance or guidance greatly appreciated. Hi Almost there!! Just replace the "=A1" with A1 =IF(AND(A1>0.1,A1<99),A1,"") -- Regards Roger Govier "FennisDuck" <Fennis@Menace.AOL.com> wrote in message news:%...
Competitor Lookup
When adding a competitor to an opportunity, the lookup only displays the name. Is there any way that this can be changed? I modified the lookup view, but can't figure out where that view is even used! The Opportunity/Competitor lookup is a special 'many to many' relationship and cannot be customized. -- Philip Richardson | Senior Program Manager | Customization and Settings | Microsoft CRM | Office Business Platform Group | philiprichardson.org "Cindy" <CindyMikeworth@newsgroups.nospam> wrote in message news:%23lYuJF9EHHA.1784@TK2MSFTNGP06.phx.gbl... ...
Voiding Open Invoice with future dated invoice
I have some invoices that were posted with a date of 01-25-2018 How can this be voided Mark, In the first place, why is 2018 created as a fiscal period? Besides that, the course of action will depend on 2 things: a) If you've posted the invoice and have not applied any payments. Under this scenario, 1) if you have not posted the invoice in GL -- it's already posted in payables -- delete in the GL batch for the transaction(s). Now, void the payables invoice(s) and once again delete the batches in GL. 2) if you've posted in GL, just void the payables invoice and post to GL a...
how do I lookup multiple values
Trying to lookup a value based on both Horizon and Vertical columns…. A B C 1 JAN FEB MAR 2 Ice-cream .5 .6 .7 3 Hot Cocoa 1.0 1.1 1.25 4 Coffee 1.5 1.3 1.0 5 Soda .8 .6 .9 How do I look up the price of ice - cream in the month of Jan. or lookup the price of coffee in Mar based on an input from user on both product and month? I can’t figure out which function to use. Please advice. =INDEX(B2:D:10,MATCH("Ice Cream",A2:A10,0),MATCH("Jan",B1:D1,0)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Lisa" <Lisa@disc...
Pivot tables remember values that are not available anymore
Hello everyone I would like to ask you is it is possible to reset the fields in a pivot table to make disapear values that they are not anymore available on the data form the selection-criteria when a fields is drill down example lets make a simple table like :: uno dos tres a q 1 s q 2 d q 3 f q 4 g q 5 a w 6 s w 7 d w 8 f w 9 g w 0 make a pivot table and drilldown "uno" (row fields), drillacross "dos" (column fields) and "tres" as Data-Items so in the selection criteria for "uno" ...
VALUE ERROR #3
I have a sheet that is doing a calculation where I get a VALUE error. In cell g5 the calculation is as follows: =if(E5<>0,D5-E5,0) The problem is that E5 also has a calculation that involes text. This is the calculation ''=IF(ISERROR(INDIRECT("'"&TEXT($A7,"dd-mm-yy")&"'!$J$11")),"",INDIRECT("'"&TEXT($A7,"dd-mm-yy")&"'!$J$11")) Thanks in advance for any help. Are the cells D5 and E5 both displaying numeric values? For example, what is the result of = E5*2 and =D5*2?...
Allow searching of date fields to allow wildcard characters
For fields such as birthdays and expiry dates, customers often want to search the database for a list of contacts with birthdays. It would be great if you would allow wildcards in date field searches so that we could search for all birthdays that occur in a specific month. The year is not always relevant when performing date searches. Birthdays is a common search performed by customers in the financial sector as well as other industries. Currently, we have to add an additional text field to facilitate this type of search. It adds confusion to the users, in some cases double data entry...
Microsoft Query is changing a negative number into a date
I am using Microsoft Query to pull a subset of a large excel file however two of my columns which are suppose to be numbers are not pulling in the negative numbers. If I look at the query in microsoft database the number shows up as a date/time string. I've tried formatting the original number as a number but the query still won't read the negative number. How do I get the query to read a negative number as a number and not a date. Hi Highlight the columns and format as a date "mowens" wrote: > I am using Microsoft Query to pull a subset of a large ex...
Syntax to write a null value inside a sql Insert string?
Is there a way to represent the null value as a string (destination: varchar column) the way that the following string represents a null value for a date? Public Const cNullDate As Date = #12:00:00 AM# Writing to an Access 2003, (conversion) table with VS2005 in VB.NET. Within the table is a field named VSubType1 with a validation rule of: "In ('P','O') Or Is Null" The declare/move/insert statements look like this (shortened example): Dim intRecNbr As Integer = 0 Dim VSubType1 As Object (defined as Text 1 column in Access) Dim V1...
Date and Auto Fill
I'm trying to auto fill a column with dates. Everytime I I use the auto fill command it changes the year rather than the day. I do have "day" selected in the fill series command. What am I doing wrong?? Sounds as though you're talking about Excel, not Access, the database product that's part of Office Professional, and the focus of this particular newsgroup. You'd be better off reposting to a newsgroup related to Excel. When you repost, it would probably help the responder to know what version of Excel you're using, and possibly the operating system on ...
'Date' problem
cell a1: =today() if a1 is 'October 7, 2005' i need b1 to be '10/1/05' b2: '10/2/05' b3: '10/3/05' etc...... Any ideas -- comotoma ----------------------------------------------------------------------- comotoman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2729 View this thread: http://www.excelforum.com/showthread.php?threadid=47423 I assume that in Col B you just want to fill in the dates from the start of OCT (10/1) to the date in A1. With the formula "=Today()" in A1, enter the following formula in B1: =EO...
Date Range on report
Hello, I have a query that asks for a "begining date" and "end date" as parameters. Is there anyway to poulate these two dates on a report so that the user knows the time frame (they just keyed) of the report? I'm trying to summarize results for employees by week. You can refer to the parameters in the report. You can set the ControlSource property for a text box to something like: ="Reporting between " & [Beginning Date] & " and " & [End Date] -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) &qu...
How do I create charts having non absolute reference values?
Not sure what you're asking. If you want to change an axis so it doesn't begin at zero, double click on it and enter your own scaling parameters. If that's not it, try using TWO sentences to explain what you need. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "rvboxel" <rvboxel@discussions.microsoft.com> wrote in message news:97BB5A7C-5AD5-4D84-83A1-02E8B96D2FAE@microsoft.com... > ...
Combobox: value from third in stead of first column
I have a combo box with 3 columns. When I choose one of the combo's rows, the combobox gets the value from the first column. Is it possible to have the combobox get the value of the third column? I tried changing the 'dependent' column property to 3 but that doesn't help. Thanks in advance, John "John" <john@smith.com> wrote in message news:fngek7$km7$1@textnews.wanadoo.nl... >I have a combo box with 3 columns. When I choose one of the combo's rows, >the combobox gets the value from the first column. Is it possible to have >the combobox ge...