Why are the two codes below not getting the right formula for my worksheet event change formula?

Why are the two codes below not getting the right formula for my
worksheet event change formula? The remaining ones do work, but these
two are not getting the right results.

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

I'm trying to get both column G & H to show a combined value of data
in other columns.

Column G should show result by combining data from column E & B, while
column H should show result by combining data from column F & C.

Column G is showing: =RC[-2]&RC[-5] when it should show
numbers...something like 654154845 if 6541 is in column E and 54845 is
in column B.

Same result with column H.

----------------------

My full worksheet event change/ selection code I have is below is as
follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, D As Range
Set D = Intersect(Range("A:A"), Target)
If D Is Nothing Then Exit Sub
For Each C In D

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))"
' - For Column J
Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!
R2C1:R4573C3,2,FALSE))" ' - For Column K
Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))-
IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
Target.Offset(0, 13).Value = "1" ' - For Column N
Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
[-1])" ' - For Column Q

Next C

End Sub

----------------------------

I appreciate your continued assistance!!

Thanks!
0
Damil4real
11/18/2009 4:54:16 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
1071 Views

Similar Articles

[PageSpeed] 58

On 18 Nov, 10:54, Damil4real <damil4r...@gmail.com> wrote:
> Why are the two codes below not getting the right formula for my
> worksheet event change formula? The remaining ones do work, but these
> two are not getting the right results.
>
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
>
> I'm trying to get both column G & H to show a combined value of data
> in other columns.
>
> Column G should show result by combining data from column E & B, while
> column H should show result by combining data from column F & C.
>
> Column G is showing: =RC[-2]&RC[-5] when it should show
> numbers...something like 654154845 if 6541 is in column E and 54845 is
> in column B.
>
> Same result with column H.
>
> ----------------------
>
> My full worksheet event change/ selection code I have is below is as
> follows:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim C As Range, D As Range
> Set D = Intersect(Range("A:A"), Target)
> If D Is Nothing Then Exit Sub
> For Each C In D
>
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
>
> Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))"
> ' - For Column J
> Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
> Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!
> R2C1:R4573C3,2,FALSE))" ' - For Column K
> Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
> Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))-
> IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
> 0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
> Target.Offset(0, 13).Value = "1" ' - For Column N
> Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
> Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
> [-1])" ' - For Column Q
>
> Next C
>
> End Sub
>
> ----------------------------
>
> I appreciate your continued assistance!!
>
> Thanks!

I got it to work.

Thanks!
0
Damil4real
11/18/2009 5:28:11 PM
Sounds like you have columns G and H formatted as text, in which case the formula is entered as a 
string and not a working formula.  Select G and H and format those columns as general, or change 
your code to

With Target.Offset(0, 6).Resize(1,2)
    .NumberFormat = "General"
    .FormulaR1C1 = "=RC[-2]&RC[-5]"
End With

You can enter the same formula into multiple cells with one line of code...

HTH,
Bernie
MS Excel MVP


"Damil4real" <damil4real@gmail.com> wrote in message 
news:3198189f-f4de-461b-830e-a3b246740340@r31g2000vbi.googlegroups.com...
> Why are the two codes below not getting the right formula for my
> worksheet event change formula? The remaining ones do work, but these
> two are not getting the right results.
>
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
>
> I'm trying to get both column G & H to show a combined value of data
> in other columns.
>
> Column G should show result by combining data from column E & B, while
> column H should show result by combining data from column F & C.
>
> Column G is showing: =RC[-2]&RC[-5] when it should show
> numbers...something like 654154845 if 6541 is in column E and 54845 is
> in column B.
>
> Same result with column H.
>
> ----------------------
>
> My full worksheet event change/ selection code I have is below is as
> follows:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim C As Range, D As Range
> Set D = Intersect(Range("A:A"), Target)
> If D Is Nothing Then Exit Sub
> For Each C In D
>
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
>
> Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))"
> ' - For Column J
> Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
> Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!
> R2C1:R4573C3,2,FALSE))" ' - For Column K
> Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
> Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))-
> IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
> 0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
> Target.Offset(0, 13).Value = "1" ' - For Column N
> Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
> Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
> [-1])" ' - For Column Q
>
> Next C
>
> End Sub
>
> ----------------------------
>
> I appreciate your continued assistance!!
>
> Thanks! 


0
Bernie
11/18/2009 5:31:38 PM
There is a bigger problem in your code - if you actually enter something into multiple cells (Target 
has more than one column or row) then you will not get your expected results.  You need to change 
Target to C in each of the lines within this loop:

For Each C In D

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

Target.Offset(0, 9).FormulaR1C1 = 
"=IF(ISNA(VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))"
' - For Column J
Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation"
Trip '!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,2,FALSE))" ' - For 
Column K
Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
Target.Offset(0, 12).FormulaR1C1 = 
"=IF(ISNA(VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))-IF(ISNA(VLOOKUP(RC8,'Vacation 
Trip'!R2C1:R4573C3,3,FALSE))=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For 
Column M
Target.Offset(0, 13).Value = "1" ' - For Column N
Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC[-1])" ' - For Column Q

Next C

HTH,
Bernie
MS Excel MVP


"Damil4real" <damil4real@gmail.com> wrote in message 
news:3198189f-f4de-461b-830e-a3b246740340@r31g2000vbi.googlegroups.com...
> Why are the two codes below not getting the right formula for my
> worksheet event change formula? The remaining ones do work, but these
> two are not getting the right results.
>
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
>
> I'm trying to get both column G & H to show a combined value of data
> in other columns.
>
> Column G should show result by combining data from column E & B, while
> column H should show result by combining data from column F & C.
>
> Column G is showing: =RC[-2]&RC[-5] when it should show
> numbers...something like 654154845 if 6541 is in column E and 54845 is
> in column B.
>
> Same result with column H.
>
> ----------------------
>
> My full worksheet event change/ selection code I have is below is as
> follows:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim C As Range, D As Range
> Set D = Intersect(Range("A:A"), Target)
> If D Is Nothing Then Exit Sub
> For Each C In D
>
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
>
> Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))"
> ' - For Column J
> Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
> Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!
> R2C1:R4573C3,2,FALSE))" ' - For Column K
> Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
> Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))-
> IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
> 0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
> Target.Offset(0, 13).Value = "1" ' - For Column N
> Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
> Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
> [-1])" ' - For Column Q
>
> Next C
>
> End Sub
>
> ----------------------------
>
> I appreciate your continued assistance!!
>
> Thanks! 


0
Bernie
11/18/2009 5:36:07 PM
Columns G & H are probably already formatted as text. Try this:

Target.Offset(0, 6).NumberFormat = "General"
Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).NumberFormat = "General"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

Hope this helps,

Hutch

"Damil4real" wrote:

> Why are the two codes below not getting the right formula for my
> worksheet event change formula? The remaining ones do work, but these
> two are not getting the right results.
> 
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
> 
> I'm trying to get both column G & H to show a combined value of data
> in other columns.
> 
> Column G should show result by combining data from column E & B, while
> column H should show result by combining data from column F & C.
> 
> Column G is showing: =RC[-2]&RC[-5] when it should show
> numbers...something like 654154845 if 6541 is in column E and 54845 is
> in column B.
> 
> Same result with column H.
> 
> ----------------------
> 
> My full worksheet event change/ selection code I have is below is as
> follows:
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim C As Range, D As Range
> Set D = Intersect(Range("A:A"), Target)
> If D Is Nothing Then Exit Sub
> For Each C In D
> 
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
> 
> Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))"
> ' - For Column J
> Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
> Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!
> R2C1:R4573C3,2,FALSE))" ' - For Column K
> Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
> Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))-
> IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
> 0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
> Target.Offset(0, 13).Value = "1" ' - For Column N
> Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
> Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
> [-1])" ' - For Column Q
> 
> Next C
> 
> End Sub
> 
> ----------------------------
> 
> I appreciate your continued assistance!!
> 
> Thanks!
> .
> 
0
Utf
11/18/2009 5:45:03 PM
Please don't multi post, you asked and got an answer to this in Programming

"Damil4real" wrote:

> Why are the two codes below not getting the right formula for my
> worksheet event change formula? The remaining ones do work, but these
> two are not getting the right results.
> 
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
> 
> I'm trying to get both column G & H to show a combined value of data
> in other columns.
> 
> Column G should show result by combining data from column E & B, while
> column H should show result by combining data from column F & C.
> 
> Column G is showing: =RC[-2]&RC[-5] when it should show
> numbers...something like 654154845 if 6541 is in column E and 54845 is
> in column B.
> 
> Same result with column H.
> 
> ----------------------
> 
> My full worksheet event change/ selection code I have is below is as
> follows:
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim C As Range, D As Range
> Set D = Intersect(Range("A:A"), Target)
> If D Is Nothing Then Exit Sub
> For Each C In D
> 
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"
> 
> Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))"
> ' - For Column J
> Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
> Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!
> R2C1:R4573C3,2,FALSE))" ' - For Column K
> Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
> Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
> R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))-
> IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
> 0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
> Target.Offset(0, 13).Value = "1" ' - For Column N
> Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
> Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
> [-1])" ' - For Column Q
> 
> Next C
> 
> End Sub
> 
> ----------------------------
> 
> I appreciate your continued assistance!!
> 
> Thanks!
> .
> 
0
Utf
11/18/2009 6:02:01 PM
Reply:

Similar Artilces:

Postal Bar Codes
When i do a mail merge i don't get the option to insert a postalo bar code. I do get the option in word. What can i do. I generally use the mailo merge and use outlook orexcell as my data source. Thanks, Hirsh Hi Hirsh (HLevy@mris.com), in the newsgroups you posted: || When i do a mail merge i don't get the option to insert a || postalo bar code. I do get the option in word. What can || i do. I generally use the mailo merge and use outlook || orexcell as my data source. You would have to use Word. Microsoft Publisher doesn't have this feature. The only postnet ability ...

synchiing two computers?
Is there a way to synch Outlook on two computers? I end up with some email on my laptop and some on my desktop - it would be nice if the two could be synched so that I could have all of my email on both computers. Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Bill Andersen asked: | Is there a way to synch Outlook on two computers? | I end up with some...

Compare two cells in two different files and return answer
I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact mat...

Error Code 80070490 03-22-10
I've tried understanding all the other posts with the same problem but I'm not computer smart at all and I'm completely lost. I have Windows Vista and I've read that you have to restore your system to fix this problem, but first off I have absolutely no idea how to do that, second, I just have way too much stuff on my computer that I can't imagine losing. I'm fine with restoring my system if someone could help me figure out how, and if someone could tell me someway I could save all my stuff, or move it and put it back on after. I have mostly software that ...

Mouse event question
Does anybody know if is there an event triggered in the CWnd class when a mouse "enters" that class, I mean, for example, when the mouse moves out from a view, or returns to a mainframe from the desktop? So far, the only workaround I've found is to use the OnSetCursor event to signal the position of the mouse, but this is not the optimal, since is triggered each time the mouse moves, making the application slow, and, as I said, I only need a signal that tells me when the mouse leaves one window and enter to another. Thanks a lot for your attention. OnMouseMove is what you nee...

Getting to Outlook express from Outlook 97, for newsgroups
Hi, Ive got both Outlook 97 and Outlook2000 on different machines, along with Outlook express. Have made a shortcut button to get to news from Out2000, but don't know how to access News from within Out87. Is there a way. -- Gerry Mitas Ryemont Abbey, Leixlip, Co. Kildare Phone: 01 6104509 Fax:01 601 4716 Info@mitas.ie www.mitas.ie _______________ DISCLAIMER: This e-mail may contain proprietary information, and is covered by copyright. It may be legally privileged, and is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, co...

Conditional IF formula using multi-dimensional arrays
I am attempting to find a value in a 30 column (1000+ row) array that is conditioned on finding an exact value match in adjacent column and a not-to-exceed value in the same row in the 30 column array. If this were limited to single column arrays, I would not have a problem, but the issue arrises because I do not know which column the not-to-exceed value will be in so I must use the full 30 column array. Here is my formula, which I am hoping someone would be able to help me with: {=MAX(IF((BI3:BI5000=A1)*(BJ3:CM5000<=A2),BJ3:CM5000))} Thanks for your help, Your formu...

Pivot table for two different data variables
I have the following table Name type Cost overhead amy C1 23 12 bob C2 129 17 cat C2 36 45 doo C2 100 32 I owuld like to create a pivot table that looks similar to: the standard pivot table. (Please ignore type for now.) name Data Total amy Sum of overhead 200 Sum of cost 12 bob Sum of overhead 130 Sum of cost 23 cat Sum of overhead 90 Sum of cost 19 doo Sum of overhead 87 Sum of cost 34 Total Sum of overhead 507 Total Sum of cost 88 But with overall total (overhead + cost) for ...

Try to move Exch db files, then store doesn't get mounted anymore
any idea on how to solve the problem below ? Exch2003 SP2. I've done these steps in several servers, but one particular server/system fails to get the Mailbox and Public Folder Stores mounted after moving the database files from one partition to the other. The steps: I stopped the Public Folder Store. I click the respective restore, "Properties", I go to "database" tab. There I inform location of where I want to move the database files to (E:\program files\Exchsrvr\mdbdata). I've got the message that files have been moved OK. Then I attempt to mount store, a...

code for odd character
I have an odd character that shows up in docs that are from an export from another program. I need to replace it with nothing but I don't know the ascii code for the character and cant' find it in any tables. Is there something in word that will let me highlight a character and then return it's code? The character is a little arrow that points to the right. (not this: >). It's not a keyboard character and I can't copy and paste it itno my vba code. Possibly a tab character? (See http://word.mvps.org/FAQs/Formatting/NonPrintChars.htm) If so, the code ...

COUNTIF (?) on two criteria
I have a spreadsheet that logs work activities and records team members' completion of project units. Column B records team member names. Column C has project unit status - 'pending', 'beta' or 'completed'. It's easy to set up a formula to count how many units are allocated to each team member - =COUNTIF(B1:B1000,"Joe"). But what I need to do is set up formulas to count how many units Joe has completed, or has pending or at beta, and this means assessing data in both columns - which COUNTIF won't do. This doesn't strike me as an especially...

Two IF Statements with two answers
In cell J9 I need a formula that would do the following: If G9="Yes" then F9, If G9="No" then -f9 Note: G9 has a drop down list with Yes, No answers. If there are no other alternatives that yes and no =IF(G9="Yes",F9,-F9) otherwise =IF(G9="Yes",F9,IF(G9="No",-F9,"") Regards, Peo Sjoblom "heater" wrote: > In cell J9 I need a formula that would do the following: > > If G9="Yes" then F9, If G9="No" then -f9 > > Note: G9 has a drop down list with Yes, No answers. > =IF(G9=&qu...

product code
I had to reformat my hard drive, I tried to install Offic Professional 2003 and it ask for the product code. I entered it and office installed, the first time I went to Word it ask for the product code again, when I entered it it says it is invalid. Is there a different product code. this newsgroup answers questions about building form objects in MS Access relational database software. suggest you repost your question to a more appropriate newsgroup, perhaps Office2003 or Word. hth "lmakm" <lmakm@discussions.microsoft.com> wrote in message news:B8B3B4AF-B0B1-4FA0-9E54-7...

Locking Formulas to Cells
I don't know if I am using the correct terminology but this is what want to do: I have placed formulas in multiple columns that calculat my sales numbers for a bid. The problem I am running into is that change the bids for every person and when I clear a cell is clears th formula from it as well. Is there a way for me to clear cells withou deleting the formula I have placed inside it? I know that I can jus grab the first cell in the column and drag it down to re-load th formula in that column but I don't want to have to do that. I want th formulas permenant and the data I enter...

Formulas #32
I have a workbook with a variety on of worksheets. I've created a worksheet that gets information from each of the worksheets and displays it on the same worksheet. I use the following formula in one of my cells: ='June 3'!$B$4 I can get the information that I need. Now, that I have this worksheet set up, I would like to use it in other workbooks, but they have different names on each of their worksheets. Is there a way to write the formula so it knows to take the information from the current worksheets in the workbook in which the formula page has been pasted??? Right...

Two axis
Positioning the Vertical axes: I have a chart where there are two axes. The first implementation produced the two scales on the right side, one inside, the other outside the chart area. Unusual, but luckily it fits well the chart. I tried to duplicate the feat with another dataset (similar in structure) and did not even get the second scale to show right, left or center (data plotted with two different scales allright). Where is the info to control these functionalities? Tx, Pierre On Wed, 29 Oct 2003 17:48:13 +0100, pl.carry <pl.carry@wanadoo.fr> wrote= : > Positioning the Vertic...

plot two groups of numbers on one series of xy coordinates.
how to create a plot from separate x and y values. i.e. y values do not correspond to the same set of x values. example x1= 5, y1=10; x2= 8, y2=15 I can read your question 2 ways; A) you data looks like this x y 5 10 8 15 etc. Select the data and make an XY chart B) you have two sets of data x1 y1 5 10 10 12 17 15 21 20 and x2 y2 3 8 7 14 12 19 18 24 Make an XY chart of the first data series Select all the data (including headers) for second set, and use Copy Click the chart to activate it Use Edit | Paste Special .... indicate New Ser...

two variables
I have a query that asks for collcode, collcode2 and booth. On any given day an employee may be collcode or collcode2. I would like to know how many times a certain emp has been paired with another employee. So how do I ask if an employee has been collcode/collcode2 and who the other person was? I hope this question makes sense. Thanks for any help Query one: SELECT CollCode, CollCode2, Booth FROM YourTable UNION ALL SELECT CollCode2, CollCode, Booth FROM YourTable Query Two SELECT CollCode, CollCode2, Count(CollCode) AS TIMESPAIRED FROM QueryOne GROUP BY CollCode, CollCode2 IF y...

How to convert a formula into a value only?
Select your range edit|copy edit|paste special|values geol2222 wrote: -- Dave Peterson ec35720@msn.com Good call, Dave. Other thing you can do to make it an easier shot: 1) Tools 2) Customize 3) Click on "Commands." 4) Choose the category "Edit." 5) Under "Edit," there's a custom button called "Paste Values" that you can drag to your toolbar for ready, easy use. That's if you're going to use the button all the time. Just a thought. Cheers, Geoff "Dave Peterson" wrote: > Select your range > edit|copy > e...

Changing of Cell protections after saving Excel File (2002)
This problem occurs when I protect a document using a macro 4.0 function: =PROTECT.DOCUMENT(TRUE,,,TRUE,TRUE). When I use the function within a macro4.0 macro, on an original file, everything works fine. The sheet has unlocked cells, and when the sheet is protected, it allows me to access those cells. But if I save the file, or save.as another name, then the fun begins. The enable selection of the sheet( view codes) has gone from 0-xlNoRestrictions to -4142- xlNoSelection. This locks me out of doing anything in the sheet. When I unprotect and then re-protect the sheet using the T...

VBA code and tips
Hi All, I am compling a list on my site of use code and tips. There will als be the oppurtuniy to download spreadsheets with example code on to b freely used. If you would like to submit any code tips or files please contact m [at] = @ info[at]frontlineuk.co -- Message posted from http://www.ExcelForum.com :rolleyes -- Message posted from http://www.ExcelForum.com ...

Find two PCI instance but load Init() one time
Hi all, I'm developing a stream interface driver for my PCI device under WinCE 6.0 and this driver works very well currently. Now, I got a problem when testing two the same PCI devices with my stream driver. The registry can find two PCI instances (i.e. PCI\Instance\mydrv1, PCI\ Instance\mydrv2) but system only call my XXX_init() one time. Does anyone know what the problem is? The following information is my registry setting: ================================================================== [HKEY_LOCAL_MACHINE\Drivers\BuiltIn\PCI\Template\mydrv] "Dll"="myd...

Text colour keeps changing to grey
I'm finding that the text within several of my spreadsheets (though they were all based on one original master spreadsheet), sometimes shows as grey when the spreadsheet is opened. I'm able to get it to go back to normal by going to Tools-Options-Color-Reset, but is there any way of stopping it from doing it in the first place? thanks Sarah ...

RATE function source code
Hi, I need to mimic what the RATE function in Excel does behind the scenes. I know what to enter into the RATE function. Basically, I am writing a program in another programming language that needs to find an interest rate when given the inputs that are used for the RATE function. Does anyone have any idea if it is possible to see the real equation for the RATE function or if you know what it is can you post it please? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.c...

Better Way to Code IF Statements?
Is there a better way than this to code IF statements in a worksheet? Code: -------------------- =IF(H8=1, A1, IF(H8=2, A2, IF(H8=3, A3, A4))) -------------------- As the code shows, the common reference cell is H8 and the IF statement checks it's value (from a range of 1-3) and then makes the target cell equal one out of a range of cells depending on the outcome. This is a short example but I have some very long and complex IF statements that use this method and was wondering if there was a way to simplify e.g. by using array formulas or something similar? Something along the l...