I have a table of some numbers, and along the left side is a column representing what each row of data is. So in this case, it is a list of letters. What I'm doing is in each column of the data, I find the maximum number, and then I want to have it list below that what letter that maximum number is from. Here is an example:
View 2 RepliesI am manually scrolling through Excel right now to find all blank cells in column L. Some cells have dates, others are blank. IF L is BLANK, I go to row K and copy/paste row K to row L. IF K is BLANK, I got to row I and copy/paste to row L.
Is there a statement I can put into row L to basically say "If blank copy/paste from row K. If row L and K are blank go to row I and copy/paste to L." ???
I have a range in column A. which will vary time to time.
I need to find the row reference(say A99) that is end of the range. Then I need to use that row ref in another formula.
How to do this without VBA?
I have a file here included which has 8 + columns. I normally have to go through each column and find when the references start and delete everything below it. If i could have a macro find where the references start in each column and delete on down that would be a huge time saver.
#REF!
#REF!
#REF!
#REF!
Thats the example of the reference i run into, i was thinking of a macro to find where the REF starts and delete that cell and everything on down.
I am building a Workbook which takes data from SheetA and inserts it into SheetB.
Part of the data is only entered when a positive value exists.
I then do an export from SheetB.
The problem is that I need to get the column number and pass it to the cell reference based on the field name in row 1.
Dim sFindstring As String
Dim rFindcell As range
Dim iR As Integer
Dim iC As Integer
sFindString = " Find this string in the cell"
'Using cells find the findstring
Set rFindCell = Cells.Find(What:=sFindString, After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'OK so look here
iR = 3
'I am trying to pick up the column number
iC = rFindCell.Column
rFindCell throws an object or with block variable not set error. Is there some property that I need to set.
So I created a formula to genterate from one program to the other. I am use to being able to click at drag the formula and have it create a new one such as 1,2,3,4,5. This formula is a little more complex and does not do that.
Here is the formula.
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
When I highlight and drag them I want it to create 121113,121213, and so on. It just keeps creating
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
I have two workbooks because I can't figure out a chain formula that I need to be able to use in reverse.
First workbook:
Row A1 = x
Row B1 = A1*1.25
Row C1 = B1/0.55
Row D1 = C1/0.42
Second workbook (the same formula, just reversed):
Row A1 = B1/1.25
Row B1 = C1*.55
Row C1 = D1*.42
Row D1 = x
I would essentially like to put information in either A1 or D1 and have the other 3 boxes fill out automatically; is this possible or am I stuck using two workbooks...? Would a circular formula be able to work in my case?
I have attached a sample file with two sheets.
Sheet 1 has two columns, 'A' represents a parcel number, and 'B' represents an indicator for the plat name in which the parcel number is located.
Sheet 2 also has two columns. 'A' represents the same indicator as Sheet 1 Column 'B' and Column 'B' is the formal name for the plat.
What I hope to achieve is Sheet 1 Column 'A' to be the parcel number (as it currently is)and Column 'B' to be the formal name for the plat (instead of the number).
As you will notice, the plat numbers on Sheet 1 Column 'B' can repeat many times. A plat number of '0' would mean it is not associated with a plat and therefore should return a blank.
The 'real' file has over 275,000 parcel numbers. All total, there are approximately 8800 differnent plat names.
Due to my 'lacking' skill level, I need it to be a macro as I have yet to learn other methods.
I'd like the users to be able to change some detail in a couple of places and have it updated throughout the spreadsheet. Basically, the user can change the line name in any of the input sheets and the code changes the sheet name, and searches for the reference to the old name in the overview sheet and changes it accordingly.
The problem I have at the moment is that I would also like the user to be able to change the line name from the overview sheet too... I am having trouble thinking how to have similar code in the "Overview" sheets Worksheet_Change event without getting into a big constant loop... e.g. if the line name is changed via code on the individual input sheets won't that then trigger the first code, which will trigger the second etc. etc. I have the following code in the ThisWorkbook section:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sName As String
Dim sOldName As String
Application. ScreenUpdating = False
sOldName = ActiveSheet.Name
If Target.Address <> "$B$1" Then Exit Sub
sName = ActiveSheet.Range("B1")
On Error Goto ErrorHandler
ActiveSheet.Name = sName
On Error Goto 0
Sheet8.Select 'this is the overview sheet
Cells. Find(What:=sOldName, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate........................
Im trying to pull together a formula to achieve the below. What i have is a spreadsheet to be used by staff in different countries so im looking to make it easy to modify the categories used without the user needing to do anything complex with formulas.
The right hand side of my file (C:E) shows a code for each year we have covered a country with the year as a prefix (2014 - Blah). The left (A:B) is a summary showing the latest year we covered that country.
Current formula:
{=IF(COUNTA(C2:D2)<=0,"","✔-"&MAX(LEFT(C2:D2&"0",4)*1))}
The current formula works fine if you set the columns to where the data actually is but for ease of use i need it to look at the data in rows 1:2 and match up the Continent/Country and then MAX the dates.
I use online banking for paying my monthly bills. I get different confirmartion for each pay transection. Pls look attached Excel file. Now I want to find specific bills expense with amount from all my 12 months bank statements. How can I get this task done.
Note: Each time get different confirmation which appears on my bank statement beside paid bill name. for example January 2009 statement GAS Z8A.... Feb 2009 GAS S2W. I hope this clarify. What I have to do if I want to get all my 12 months GAS money withdraw from bank statement. I use csv file for bank statement.
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
I've got a list of numbers between 0 and 1
eg.
0.25
0.59
0.73
0.15
0.99
in column A
if I want column B to state "low (0.00-0.33), medium (0.34-0.67) or high(0.68-1.00)" for a corresponding value in column A, how would I do that?
The data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.
The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.
My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.
The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
I'm trying to set up a dashboard with a table and some formulas so that I can just copy a data export into one tab and have the table perform various Vlookups, charts, etc. The trouble I'm having is that some of my cells contain a mix of text, and I'm looking for specific text only. Example:
Address |Tags | Result
Email 1 |t1 t2 t3 t4 | Email 1
Email 2 |t1
Email 3 |t2 impact1 | Email 3
Email 4 |impact2
Email 5 |impact2 t2| Email 5
My data source has thousands of rows, and I'm looking at the Tags column. I want to use a Vlookup that gives me the email when [Tags] contains "t2", but my search is getting confused with "impact2". Is there a formula to search for "t2", but not get confused with "impact2"? I considered using =NOT, but I don't want to exclude cells just because they contain "impact2". If there was a formula for =IGNORE, that would be marvelous. My formulas are getting longer & longer, but they always seem to break in the end.
Basically I'm trying something like:
=IF(OR(ISNUMBER(SEARCH("* t2*",Table1[Tags])),SEARCH("t2*",Table1[Tags])),"Yes","No")
(Once I get the IF figured out, I'll use Vlookup to bring out the Address).
I have a value on column A , I need to find the reference on a range from column G to O so value from O pulls to column B
Example
Columna A :Has a box ref CJB2441ALAJ
Column B: I need the answer to show
Colomn G to O have a country name just for that reference
So If use Vlook up nothing shows up.
Is there another way to look this value within the range that I need?
I have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
I need to find a formula that will find letters in a referance, for example i have referances like - MNE DJM & ZZPAR i need to find a formula that will find me the ZZPAR looking for "ZZ" i then want this to tell me what tpYe of referance number it is and put this into column Z.
Normal referance number like DJM and MNE i want this to show as "BROMLEY"
aND ZZPAR as "Chester"
I have tried something like the following but this is not working
I want to get value from reference one column and return value by corresponding row from another column. This can be done by vlookup column. But it find value from 1st column and return value from given relative column. That mean find from Left column and return value from right side given column number.
But I want find from Given column number and return value from given left column. For Further detail see the Attached file.
I am writing a function that opens up an external excel document, identifies a string in a certain range and then i also want it to return a cell reference for that string including the number of rows beneath the cell that the string was found in.
View 6 Replies View RelatedI needed to know how to find the closest match to a reference number
regardless of whether its larger or smaller. I did a search and found a post
back in March that said to use the following:
=SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list)
I applied it to my application and it works, I just have no idea why. Can
anyone explain this formula to me or tell me where I can find a good
resource.
If column B is not empty, I want the corresponding cell in the same row of column A to contain the value "1".
View 9 Replies View RelatedI Basically need to use DATA in Column D of my file to add a ROW and then use Column C to name that new ROW added...
Example: [URL] ........
Currently, I have a standard vlookup forumla that looks like =Vlookup(B2,Sheet2!A:G,5,false)
However, the data on sheet two is ever expanding--each month, specifically. And I need to return the most recent (so, rightmost filled) column. Right now, the column is 5, but next month it will be 6 and so on and so forth.
I'm having some difficulties in defining my formula. I'm pretty sure that this should be possible.
I have a table containing values and dates. For each change in dates, I should summarize every value for that date. I cannot use the autosummarize function, because I do not want the sum to be beneath the values, rather in a column to the right of the values.
I have created a formula which checks for a change in date: =IF(C15=C16;0;1) where the C column contains the dates. Basically I say that the Index column should contain a 0 if the date is the same as previously registered, 1 if it is a new date.
The index column is used in some conditional formatting rules, so I would like to keep it anyway.
i want to search OK7V0T in all cells and return the cell reference beside matching data row
serial 1serial 2serial 3
box-4OK4KCUOK4KI0 OK4LLS
box-4OK60ZDOK62AP OK62QU
box-4OK6TA5OK6TBP OK6TFW