I recieved on my last thread (link below), the code provided worked fine.
Previous thread I submitted
I now have a problem with the .value attribute. When comparing Excel finds two cells unequal in '.value' then writes '.values' in changes sheet. When using =A1=B1 the result is TRUE. I don't want equal valued cells to be picked up how can I stop this? (Macro code included in previous thread if needed)
I would like to attribute a number to each digits 1 in position.
 IJKLMNOPQRSTUVWXYZAAABACADAEAF1Â
12345678910Â Â Â Â Â Â Â Â Â Â Â Â Â 2Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3Â Â Â Â Â Â Â Â Â Â Â Â 4Â 0111110010Â Â 234569Â Â Â Â Â 5Â 1110010000Â Â 1236Â Â Â Â Â Â Â 6Â 1010010110Â Â 13689Â Â Â Â Â Â 7Â
The field is a concatenation of three other fields and only the first of three parts should be boldfaced -- the second and third part should be normal faced.
Is there an easy way to do this in excel 2007? Right now, the formula is:
=CONCATENATE(UPPER(TRIM('Raw Data'!H26)),CHAR(10), TRIM('Raw Data'!G26)," [",TRIM('Raw Data'!J26),"]")
and the part in red, is to appear boldfaced.
my problem involves displaying rows that meet certain ‘criteria’, on a separate worksheet, in real time. The criteria will be determined by a selection made from a dropdown list in the row in question.
ILLUSTRATION: Let us say (for example) that I am dealing with a worksheet for MEN’S DATA. Each man’s data will be on a separate row. On each row, one of the cells has a dropdown list having 2 options, SHORT and TALL. I have a separate report worksheet for TALL men. I mean, I want every row for which I select the TALL option, to be copied to my TALL report worksheet. I want the selection of the TALL option to be a kind of trigger that promptly copies the row to the TALL worksheet, once I make the selection from the dropdown list.
I have a conversion chart of decimals to fractions I made in Excel. I want to copy and paste as an image so I can shrink it to fit at the top with the frozen headings.
I tried to take a picture of it on the monitor, but the photo captures the screen fuzzy pixels.
I tried to copy and paste but could not find where I could paste as a special attribute. Or a picture on the internet with the decimal first and the fraction second in 1/16ths
I want to show which rows of data have been changed by incrementing a revision attribute. For example, if a user changes the contents of a cell anywhere between rows 2 and 13 and col 1 and 9 then the revision attribute in col 10 would increment from 1 to 2 (for the affected row). If another change affects the same row then the rev attribute would increment to 3, and so forth. I don't care which cell was changed only that something on that row was touched.
I thought the CHANGE event was a dead ringer for triggering some VBA code to control this but, since part of the change event code writes the revision value, this triggers another CHANGE event causing an endless loop until something (??) kicks in and stops it after 220 iterations. Is there a way to inhibit the change event just prior to updating the cell containing the version attribute?
Rather than post the code here I have submitted the workbook that includes the whole setup and code. I should also mention I looked at all the other worksheet events and I do not see any "triggers" that would fire each time a cell content is changed. As a side note, is there a way to step into the code of a change event? F8 does nothing.
0 01
0 02
0 03
1 03
0 05
2 06
0 07
in column 2, something may have multiple numbers, but it must have something in column 1 that is equal to 0.
ex. everything above is ok except the 2-06 because it needs a cooresponding 0-06. I need some way to flag these so that i know when there is something in column 2 without a cooresponding 0 in column 1
I use an Array Constant in an IF statement, rather than using OR?
I have tried the following without success:
=IF(A1={0,1},"Something","Something Else")
Which would look like this using OR:
=IF(OR(A1=0,A1=1),"Something","Something Else")
Is it possible? If so, what's the right syntax?
I have used the function INDIRECT in 1 of my files.
The disadvantage is that both files (source and target) have to be open.
Is there a substitute for INDIRECT that works with a closed source file?
I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:
View 2 Replies View RelatedI'm using sumif to get the total number of OT minutes/hours rendered by an employee. In a company with 1K employees, doing so proves to be tedious as I need to rename the criteria for each formula. Is there an easier way to do this? See attachment for more details. I'm using Excel 2003.
View 2 Replies View RelatedI have a lot of data that I need to have organized out but as far as I can tell an IF statement is the only thing that could work. I work at a casino and there are about 6,000 games that I need to be able to sort through. The games are classified by sections A - Q. Each section contains anywhere from 30 - 50 banks, and each bank contains anywhere from 4 - 20 games. This is what I am trying to accomplish.
Have a drop down that lists zones A - Q. If the user chooses A, for the next column to populate with a drop down of all of the banks in A. Then for the user to choose the bank they want and then have the next colum populate a drop down with the games in A>bank1. So basically...
Zone>Bank>Machine
then once you choose the machine, for the columns to the right to auto populate the information on that game such as themes ect.
Zone>Bank>Machine>Information on that machine
I have another sheet that contains the inventory of all of the games that I am using as my "database". I am able to get this working great with IF statements, but I obviously need to be able to use more than 8. Here is my current code...
=IF(A164="A",ABankList,IF(A164="B",BBankList,IF(A164="C",CBankList,IF(A164="D",DBankList,IF(A164="E" ,EBankList,IF(A164="F",FBankList,IF(A164="G",GBankList,IF(A164="H",HBankList))))))))
Exit Sub alternative. I have the following
View 5 Replies View RelatedI am trying to make an IF statement and need to enter 12 if's. Excel will only let me go to 7. Example, if Jan is in AE11 the copy the number from B96, if Feb is in AE11 then copy number from c96. Everything works up to July.
View 2 Replies View RelatedI use a spreadsheet to keep track of stock at work.
The invoices are put into rows with the stock code and qty next to each other.
IE:
Invoice No | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty ect..
I am currently using an If function to check all the cells for an instance of each stock item in a total of four columns.
I was creating a formula in excel yesterday that used nested IF's but I soon found out that the maximum number you can use is 7. So I did a bit of digging and found out that I would be better of using a Select Case in VB.
What I want to do is this:
=IF((SEARCH("case1",A1)),"text1",IF((SEARCH("case2",A1)),"text2",IF((SEARCH("case3",A1)),"text",...etc until I have listed every possible case and I want to do this for a range of cells A1:A1000 or however many entries I have.
I am assuming I would have to use code similar to the solution in this thread:
[url]
The only thing is I cannot translate this to what I am doing. Do I need to write a loop to go through the entire range of cells I want to check? How do I determine if a specific string is contained within a cell? Once the macro is written do I refer to it from a cell or run it from within the macro menu? Please bear with me as I am new to this and have not coded in VB since school.
For each cell, instead using a comment, I need another way to show a large amount of principaly letter texts, without using hypervincules, and ofcourse comments.
By the way, the same text I enter or modify put in this "way", I need to modify in the MSWord document and viceversa.
I appreciate the linking do not open another app, because the large amount of them I need to work with.
Dim myRow As Long
Dim myCol As String
Dim Search As String
Sheets("Master Holdings").Range("H2")
Application.Screenupdating=false
mycol="E"
For myRow=6000 To 1 Step -1
If Activesheet.Cells(myrow, mycol).Value Search Then
Rows(myRow).Delete
End If
Next myRow
Application.ScreenUpdating=True
End Sub
Is there a better way to perform this code?
im trying to figure out how to loop or if there is an alternative to an if statement in this situation here...
in cell b2 i need to say
"if the height =< 12 then return 0
if 15==21 then 1
if 24==36 then 2
if height >=39 then 3"
I have a sumproduct that evaluates # of conditions and returns the count when everything is met. But with the amount of data I have it is very slow to recalculate. I'm trying to find an alternative. I've read through a bunch of threads about different ways to avoid sumproduct but I can't get them to work. Here is the criteria I'm working with (simplified) .....
View 9 Replies View RelatedI have this excel project that captures identification numbers using a scanner. once the scanner gets the info, it will mark the date/time the person was scanned. my problem is once the info scanned is not on the list, it will continue searching till i do an escape or ctrl-break.
heres the
Private Sub cmdOK_Click()
ActiveWorkbook. Sheets("list 2007").Activate
Range("F25").Select
Do
If ActiveCell.Value <> txtID.Value Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = txtID.Value
ActiveCell.Offset(0, 2).Value = Now()
Range("F25").Select
txtID.Value = ""
txtID.SetFocus
End Sub
In 1 of my spreadsheet I make use of the function INDIRECT to access cells in another spreadsheet. This works fine but has the disadvantage that both file have to be open.
It seems that INDEX can do the same, but: the sourcefile doesn't have to be open. I have tried it and this works.
However: the directory of the file I'm working on will change in the near future (maybe more than once)
Therefore I want 1 central place with the directory and file names and use these in the INDEX function
This is where I get into trouble
I'm not sure if it is possible, but if it is, some advice is needed on how to do this.
I have a worksheet that has 5 columns of data, all of which are text. I am looking for a way to present/display the data in a manner similar to that of a pivot table. I'm pretty sure an actual pivot table is no good to me since I'm dealing with text, but I'm looking for something that is functionally the same.
In other words, I would love to be able to "pivot" my data and display the different relationships between the different columns. If a pivot table would display text in the "data items" field, that would be perfect..
I am trying to use the command below to report what type of bonus is to be awarded. .05% - 1% bonus, commission based.
=IF(E7>=0.12,1,IF(AND(E7>=0.115,D11<=0.1199),".09",IF(AND(E7>=0.11,E7<=0.1149),".08",IF(AND(E7>=0.10 5,E7<=0.109),0.07,IF(AND(E7>=0.10,E7<=0.1049),"0.06",IF(AND(E7>=.095,E7<=.099),".5",IF(AND(E7>=.09,E 7<=.0949),".04",IF(AND(E7>=.085,E7<=.0899),".03",IF(AND(E7>=.08,E7<=.08499),.025, IF(AND(E7>=.07,E7<=.0799),".15",IF(AND(E7>=.065,E7<=.0699),".005")))))))
It is telling me there are too many levels. I am not an Excel expert, so I am trying to figure out an alternative to this command, I am sure one is available.
Is there a more efficient method to what i have attached? i.e. if E4=anything between B2 and B10 then C2 is displayed. Between B11 and B16 then C11 is displayed.
View 4 Replies View RelatedThe below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data:
K28 = Last month balance
Column E = Type of expenditure
Column D = Values
Column B = Payments
K23 = Currency conversion rate
During the execution of some code I come to a point where the properties of a range of cells needs to be converted to numeric (is at that point text).
Normally I do that by adding a 1 in an cell nearby and use the copy/paste special method.
I was wondering if I can do that in a different way.
Is it possible to define a MultiplyFactor as 1, and use this in paste special part.
Something like: .....
Does the following formula make sense and if not, what do I need to change? I'm receiving a "#VALUE" error. =MROUND(IF(A9=1,SUM(D1*0.1), 0) (IF(A9=2, SUM(D1*0.1/2), 0)), (IF(A9=3, SUM(D1*0.1/3), 0)), (IF(A9=4, SUM(D1*0.1/4), 0)),6)
It is possible to have multiple IF,THEN statements in one formula? A9 and D1 are the variables driving the formula. Let me know if I need to provide a detailed explanation.
I have a table of approx 20k customers (column A) and their $ sales (column B)for a time period. I need to sum the sales of approx 50 customers. I have the names of the 50 customers to be summed in one column so i can reference them and easily change out a customer if need be.
View 5 Replies View RelatedJust a curios question is it possible to have a button on a macro that if you press it would run one set of instruction, but if the user was holding a key down say the shift key and pressed the same button, it would run a second set of instruction
EG
If [Shift] key active then
Run command 1
else
Run command 2.