How do I make a cell value update to equal the furthest value to the right in the same row?
This cell just needs to look along the right in its own row and grab the value furthest away to the right. It will be a letter value, not a number. eg, AB or AC or AD or AE. It will not (in this case) need to look further than about 150 columns or cells.
I have 5 cells that data will be entered into:
1. AA6 2.AJ6 3.AS6 4.BB6 5.BK6
I'd like a formula that will return the value in the cell that was at the furthest point in the sequence above that wasn't a blank, so
if aa6=1, aj6=2, as6 was blank,bb6=3 it would return 3
if aa6=1, aj6= 2 and the rest were blank it would return 2 (the value in aj6)
Sheet2 has columns with dates as headings. I add data under each date as appropriate. I would like a summary page for Sheet1 that contains only the most recent column data from Sheet2 (and other sheets, of course). For instance, if I have dates for columns A-E on Sheet2, but only A and B have data under them, I would like Sheet1 to contain the data in Sheet2!B, but not Sheet2!A because that is old data. Sheet1 will only contain the most recent data while sheets 2 through "n" will have historical data. Attached is an example of what I mean...
View 5 Replies View RelatedI have a table with the follow values (simplified version): A1:E1 have text headers B1:E1 have formulas to pull in text values. Some are blank. Some have repeat values. F1 has the value I want to look up. I need F2 to look at A2:E2 and compare to F1. It needs to pull in the corresponding A1:E1 value for the furthest to the right A2:E2 value.
Example:
Column A B C D E F
Row 1 Jan Feb Mar Apr May Imp1
Row 2 [blank] Imp1 Imp2 [blank] Imp1
F2 should return "May" as a value.
Screen shot attached.
screenshot.png
I have a register of items that sometimes get updated, the register is stored as
From row 9:how ever many hundreds of items we have
Columns A:E
Data about the item that does not change with new revisions
Column F
Current Revision
Columns G:Z
Revisions in order (ie if there had been 8 revisions
'-, A, B, C, D, E, F, G) (with higher revisions left blank)
each of which is hyperlinked to the relevant file for that revision.
What I would like is a formula in Column F that will print
1) the letter of the highest revision (Revisions are always sequential)
2) Will hyperlink the column F to the correct revision...
I used to know how to do the first half of this formula but the spreadsheet got tinkered with and now I can't remember how to do it.
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09
I am trying to write a macro that will make the bottom cell in a column equal to the top cell. The top cell will not always be in the same row and there won't always be the same number of cells.
View 1 Replies View Relatedhow to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?
View 5 Replies View RelatedIs there a way to create a multiple dropdown (Similar to Windows Start menu) so that the user can select the item and its properties then the selections be placed into multiple cells? For example, i select wood from drop down list and i get another drop down list asking for the type of wood, I select MDF and another drop down ask for thickness. when I finished my selections, then the selected Items copy themselves into 3 different cells.
I have a drop down in (A1) cell and I select an item from it. I want (A2) cell to check the name in (A1) then call up another drop down i already made in (A2).
I have several hyperlinks that may or may not contain data, the linked cell will display "0" if there is no data. When I print I dont want the "0"s printing.
How do I make the cell display nothing when a hyperlink cell is nothing?
I want to make a cell blank if nothing is written in another cell. I have used this formula in C4: =IF(ISNUMBER(C3),IF(C3<180,C3+180,C3-180),"")
View 1 Replies View RelatedHow can I make a cell mandatory, if another Cell states XX?
For example:
If column B has "agency" then column C must be completed stating which agency was used?
However sometimes column B could say "no agency" therefore column c doesn't need filling!
I have a monthly forecast document that is updated by an operator monthly. At the end of the month, the operator presses a "Copy Accrual" macro that copies accruals for the month and stores them on a seperate tab. After sending the document back to the Finance team (necessary step due to software complications), Finance will send the operator back an updated file at beginning of next month. The operator than presses a "Paste Reversal" macro that is supposed to input the negative of the accrual stored in the seperate tab. The problem is that it is posting as the same value (not negative of value). I tried recording a Macro to fix this, but it did not work. An example of the code that came up with is:
Worksheets("Weekly Forecast").Select
Range("B9").FormulaR1C1 = "=-Accruals!RC[-4]"
Range("H9").Select
Selection.Copy
Range("H10:H33").Select
ActiveSheet.Paste
Thoughts on what to do? I think the two things that are throwing me off is the negative, and moving between the different sheets.
I am using a “borrowed” formula from a coworker that I do not really understand however it is working for me, sort of.
=+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V17,$W17,$X17,$Y17,$Z17,$AB17,$AC17,$AD17,$AO17,$AZ17)))),I F(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="FFFFFFFFFFFFF FFFFF","FAIL","PASS"),"FAIL *")
What this does is read the range of cells and immediately fails a quality check if it is in a critical cell, or fails if multiple non critical items fail.
I want to use an if/then to make cell blank if there is no data in one particular cell (which means no quality inspection performed). Here is what I tried:
IF(V18="","",+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V18,$W18,$X18,$Y18,$Z18,$AB18,$AC18,$AD18,$AO18 ,$AZ18)))),IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="F FFFFFFFFFFFFFFFFF","FAIL","PASS"),"FAIL *"))
The leaving the cell blank part of the formula works however it allows the remainder of the formula to only work if “fail” appears in the first cell it reads (in this case V18). If the “fail” appears in any of the other critical cells, it does not work at all.
Also as a side project not sure what the substituting to $R$2 does so if anyone knows how to clean this up, let me know.
When I click a cell (say A1) I want a specific message to appear in another cell (say B1); when I click A2, a different message should appear in B1 (basically B1 will be my display zone). The reasons I need it as such are that the sheet is a calendar and so putting all of the messages in would look ridiculous, and I really need a static position (B1) for the messages.
View 9 Replies View RelatedI have an excel file where the user has to enter his user name and password. But the password that is entered into the cell should appear as Wildcards(*) to everyone (Like our Windows login). Is there any way to do it.
View 1 Replies View RelatedIs it possible to have an "If this cell equals this amount, then make a picture appear" scenario?
Say there's a blank sheet, but if cell A1 is 1, then there will be a picture (or shape) in the center of the sheet.
I want to make a list from data table in Sheet1. In order to make the list I use Quantity value in B column at data table.
For example;If quantity is 50,List will be 50 rows with the data columns.
I have tried the following code, but doesn't work unless I protect the sheet:
Range("A1:A4").Select
Selection.Locked = True
Selection.FormulaHidden = False
If I protect the sheet, then it works fine, but how can I make a cell read only
WITHOUT protecting the sheet?
So I'm trying to use the SEARCH function in excel to search for a particular text in a cell. I am trying to create a macro for it.
View 9 Replies View RelatedDim varFind As Variant
Set varFind = Cells.Find(What:="Closed", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not varFind Is Nothing Then Range(varFind.Address) = Hidden
something like
Then Range(varFind.Address) = Range(varFind.Address).row.Hidden
I made a drop down list in cell H11 for Marital status (married,defacto,single,widow). Those are the 4 options in the drop down list.
Now im trying to make in another cell IF (H11="Married" OR "Defacto,950,false)
but wont work, i know im wrong but i learn by trial and error or last resort MREXCEL and m ytrial and error didnt work.
I need a VBA code to make everything entered into a particular tab, in excel 2007, all capital letters.
I've seen it done, i just don't know how to write the code.
I've seen it done, and had the formula on a couple documents, but had a loss of data. What I'm looking for is the formula to enter into a cell for copying the name of the worksheet into that cell.
View 6 Replies View Relatedhow do I make a highlighted cell blink on and off?
View 9 Replies View RelatedI have a large spreadsheet with many pieces of equipment in it. There are a few piece that I would like to highlight should thier computer license go out of date, or even better 30 days prior. I have a Feild with the Expire dates but it is at the end of the spreadsheet not in plain sight. Is there a way to make my Equipment description column highlight when the date in the other column reaches the current date (or 30 Days before). I have cut and pasted the part of the spreadsheet into a new file to make it easier to read and hid all the none important feilds.
View 5 Replies View RelatedI am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.
This is the formula I have at the moment:
=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")
A B C
10.1810.82
I'm looking for an IF function formulae.Currently, the IF function of my cell is =IF($A8=B$1,1,0), value if true=1 , if false= 0.
But how do I make the cell A8 ( MRT , Taxi ) show a true value of 1 ?
Becos MRT , Taxi is also under MRT, but I don't know how to make it a true value of 1.
I'm trying to create a formula in a cell that references another cell but I only need it to reference the decimal. How would I go about doing this and is it even possible?
View 3 Replies View Related
