When any cell in row 19 (eg K19) equals X I want the cell in row 25 3 cells before K19 (i.e. H25) to eqaul Y.

Any tips will be greatly appreciated.

Cheers!
0
bolo181 (1)
3/12/2008 2:35:02 AM
excel 39879 articles. 2 followers. Follow

2 Replies
417 Views

Similar Articles

[PageSpeed] 5

Re: When any cell in row 19 (eg K19) equals X
I want the cell in row 25 3 cells before K19 (i.e. H25) to equal Y.

One way to get it done ..

In A25:
=IF(ISNA(MATCH("X",19:19,0)),"",IF(MATCH("X",19:19,0)<4,"",IF(COLUMNS($A:A)+3=MATCH("X",19:19,0),"Y","")))
Copy right across to IV25

Above of course, assumes that there's only a single "X" within row 19 at any 
time. If you got more "X"'s in row 19, it'll return only the result for the 
1st one (from the left)

p/s: You should always post your question in the message area. The subject 
line is just that -- a brief liner hinting at the question -- it's not meant 
to be the posting.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---  


0
demechanik (4694)
3/12/2008 9:01:14 AM
Max demechanik@yahoo.com left this in microsoft.public.excel:

> Re: When any cell in row 19 (eg K19) equals X
> I want the cell in row 25 3 cells before K19 (i.e. H25) to equal Y.
> 
> One way to get it done ..
> 
> In A25:
>
=IF(ISNA(MATCH("X",19:19,0)),"",IF(MATCH("X",19:19,0)<4,"",IF(COLUMNS($A:A)+3=MATCH("X",19:19,0),"Y","")))
> Copy right across to IV25
> 
> Above of course, assumes that there's only a single "X" within row 19
> at any time. If you got more "X"'s in row 19, it'll return only the
> result for the 1st one (from the left)
> 
> p/s: You should always post your question in the message area. The
> subject line is just that -- a brief liner hinting at the question --
> it's not meant to be the posting.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

A simple IF(X=K$19, X,"") ought to do the job (if i got the order of the
arguments right).  Then spread it across the columns of interest.

0
3/15/2008 7:34:30 PM
Reply:

Similar Artilces:

Writing Numbers Outside Rows
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to leave numbers #1, #2, #3, etc outside the rows I have made but can't get the cursor to go there to type the numbers. The cursor only goes inside the row of the column. How do I get the cursor to go outside the row of the column? <br> thanks. Hi, it is unclear what exactly you are trying to do. Do you want to create a numbered list, or do you want to number the lines of your current document? If it is the latter, click on Format>Document, switch to the Layout tab and click ...

Business Portal 3.0 install error
Hi, I am getting the following error " Failed loading Packager Data Element (PD_REQUISITION) for Install Feature (Requisition)" MbfPackager Failed Loading Packager Data. Any help is appreciated. Thanks ...

coloured cells #2
Hi, I have a user that created a spreadsheet that is highlighted by colours in different cells. On his home computer he can see the colours but at work the spreadsheet becomes pure black and white. If he prints the spreadsheet at work, it is in colour. He used to see the colours on the screen at work also. He is using Excel 2002. Any ideas? Joyce Look if he has set this Click Start, click Control Panel, and then click Accessibility Options. Click the Display tab, and then click to clear the Use High Contrast check box. Click OK to close the Accessibility Options dialog box. -- ...

sorting error on non-merged cells
Excel 2002, WinXp Home My spreadsheet has columns for Number, Date, Text, etc. When I select a column to sort, Excel expands the selection to include all cells (as it should). When I try to sort I get the error message about merged cells having to be the same size. I looked at KB291063 but that doesn't help. The cels are NOT merged so why do they have to be the same size? What size is the error refering to (# of characters, absolute value, ?)? How do I un-merge non-merged cells so I can sort? Firstly I would never rely on Excel to expand a selection to include the data I wa...

tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when f.e. this phone is away from the office. This is PDA phone. On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" <fwitkowski@gmail.com> wrote: >How can I use exchange to send tasks to cell phone. I want to do when >f.e. >this phone is away from the office. This is PDA phone. If you're using a Smartphone you can sync tasks already with ActiveSync. "Mark Arnold [MVP]" <mark@mvps.org> wrote: >On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" ><fwitkowski@gmail...

Formatting text in a cell
Is there a way to key text in one cell that is multiple lines? We would like to be able to insert new lines where we want them. Thanks for the help!! -- akkrug Use the Formula Bar: 1. click in the formula bar 2. in the formula bar, move the cursor to the desired point of insertion 3. touch ALT-ENTER 4. enter the new line. -- Gary''s Student - gsnu200855 "akkrug" wrote: > Is there a way to key text in one cell that is multiple lines? We would like > to be able to insert new lines where we want them. > > > Thanks for the help!! > -- > ak...

Finding combinations of cells that sum to near a value
Starting with a column of ~30 numbers, I'm looking for a way to have Excel find all combinations that sum to a target value +/- a specified range width. (For example, I may be looking for all combinations that sum to 1000 +/- 10.) I've found some code from Tushar Mehta that finds a list of all possible matches that sum to a number, but it doesn't allow me to put in a range to find sums to within a delta of this value. I tried modifying the code to add this in, but I couldn't get it to work right. Here is the code: http://www.tushar-mehta.com/excel/templates/match_values/index....

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

Return range from row number
Hi I have a table from which I need to find specific values based on the rows they reside in. I need to search the (n)th row to return the column number where a specific number (x) is located. Top row of table is G29:CZ29 A1=value to match (x) from another worksheet A2=row to search (n) from another calculation Is there any way to achieve this by converting the known row and its endpoints into a "range" to be inserted in the MATCH function? MATCH(A1,"range",0) e.g. if A2=93, the "range" I need to search is G122:CZ122 Thanks in advance Andy One way: ...

separating multiples digit in a cell into individual cell
I have a series of number sets. When I copied from the MS Word and pasted them into Excel, all of the numbers in the set are pasted into one cell. I need those numbers to be in an individual cell. How do I do it? I think MS Excel 2003 was able to do it. I currently have Excel 2007. Please help. Thank you very much. EggHeadCafe - Software Developer Portal of Choice A Wrapper for the Dispatcher class of Threading Namespace to manage thread items http://www.eggheadcafe.com/tutorials/aspnet/bce7889e-d2cf-42b8-a6af-2f01a383cff6/a-wrapper-for-the-dispatc.aspx Hi, You may use Data >...

cell in cell formulation possible???
Let's say I have a string of text in a cell, something like: "We may charge you $XXX fee if item is not returned in good condition." Is it possible to make that XXX a formula/calculation (based upon information entered in another cell)? ="We may charge you $" & format(A1,"#,##0.00") & " fee if item is not returned in good condition." HTH, Gary Brown "Abi" wrote: > Let's say I have a string of text in a cell, something like: > > "We may charge you $XXX fee if item is not returned in good condition." &g...

Do Not Want to count blank Cells
I copy pasted an Access dynaset (e.g., results of a crosstab query) into an Excel spreadsheet. I tried to use the function =counta(a4:z4) to count the number of entries (nonblank) in row 4. But the result is giving me 26 because it is counting blank cells. Is there a way to count the entries without counting the blanks or a way to easily reformat or change the blank cells so the formula will not count the blanks. I do not want to have to manually delete every blank cell in a table 26 columns by 2000 rows. Thank you, Steve CountA does what you want. It seems that you must have s...

Hiding rows and columns in XL 07
How do I get the top column row, which contains only the column letters, and the row number, which is the narrow column to the left of Column A, to disappear when I print? thanks everyone for your help. -mlenard Click File > Page Setup > Sheet tab Uncheck "Row and Column headings" > OK (Its probably checked) Success? Hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "mlenard" wrote: > How do I get the top column row, which contains only the column letters, and > the row num...

moving rows
I have a large report in tab or csv format that creates 3 rows of data in Excel that I want to move to a single row. Cut and paste will take forever. What can I do? Hi what delimiter is used in the *.csv file and what is set in your Windows registry? Some workarounds: - use 'Data - Text to columns' to separate the data - rename the *.csv file to *.txt as then the textimport wizard is started and you can specify the delimiter -- Regards Frank Kabel Frankfurt, Germany "cdshon" <cdshon@discussions.microsoft.com> schrieb im Newsbeitrag news:2A52D215-D555-4587-A8C...

How to matching substring from a Cell
Dear Sir/Madam, I am now having a column likes below Column A My Name is David Chan Marry David Williams He is David and is a boy I want to find out Column A cells with contact the word "David', then display "T" on Coloum B if found or "F" is not found. The result should be as following: Column A Column B My Name is David Chan T Marry F David Williams T He is David and is a boy T How can I do it ? I have tried if( ), but it only match the c...

Outlook 2007 E mail folder short cuts
I have created folder short cuts however after using them for a number of days I started Outlook and was not able to use them. The short cut names are there but when I click on the names the folders do not open up. All the other functions such as calendar, contacts, tasks, notes work fine. Any suggestions ? ...

moving cells
I have a roster with 30 names on a fixed schedule for 15 weeks i want to move the names through the schedule. Thanks Hi Paul, I would try an Excel web search on your Google toolbar or at http://www.google.com/advanced_search?hl=en for excel rotation OR rota schedule OR timesheets and a Google Groups search on a dropdown on your Google toolbar or at http://groups.google.com/grphp?hl=en&tab=wg&q= rotation OR rota schedule OR timesheets group:*excel* to get you started then ask specific questions if that does not provide enough information. --- HTH, D...

Software doesn't want to start
Hi, My MS Money 2002 Standard version (installed by the manufacturer on a Dell computer) doesn't want to launch. THe message a get when trying to launch the program is: Microsoft Visual C++ Runtime Library // Runtime Error! // Program: C:\Proram Files\Microsoft Money\System\msmoney.exe // abnormal program termination I tried to reinstall the program and still having the same issue. Can anyone help Thanks Ramez Try the following article: http://support.microsoft.com/default.aspx?scid=kb;en-us;305254 "ramez" <ramezc@aol.com> wrote in message news:0cf301c3778e$8a656...

Attaching Comment to cell contents rather than cell
I have a spreadsheet that is regularly upadated overwriting columns, however I need to enter comments on the sheet referenced on the content. The next time I update the sheet the referenced cell will move down a row, but I need the comment to stay with the content rather than the cell. Any suggestions? Many thanks If the values that you're commenting are unique, you may want to toss the comments and create a table in another worksheet. Value Comment Then use an =vlookup() formula to return the "comment" to the adjacent cell. El Damo wrote: > > I have a spreadsh...

Can a cell be turned on or off?
A1=B1*C1+D1 A2=B2*C2+D2 A3=B3*C3+D3 A4=B4*B4+B4 I would like to be able the have the total in any one of the A cells to add up only if the cell is click on. In other words, can a cell be turned off or on as needed? -- Joe Right click sheet tab>view code>insert this Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Column <> 1 Then Exit Sub 'B1*C1+D1 tr = Target.Row Target.Value = Cells(tr, 2) * _ (Cells(tr, 3) + Cells(tr, 4)) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@g...

display 3 columns of 50 rows in stead of 1 column per row in Gridview/Repeater/Listview/Datalist
Hi there, I may have 3 columns in a table or list<name>). ID - INTeger Name - String AgeGt18 Checkbox (true false) a normal databindercontorl like Gridview/Repeater/Listview/Datalist displays per row. But I would like to display 3 columns. e.g. ID Name AgeGt18 ID Name AgeGt18 ID Name AgeGt18 1 Michael True 4 Michael3 True 7 Michael9 True 2 Frans l False 5 Michael1 False 8 Michae14l True 3 Frans l False 6 ...

Pause and Input to cell
Help, Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, then the macro would place the input data in a cell, let's say d9. How do you do this in Excel. Val, InputBox. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Val Steed" <vals@msn.com> wrote in message news:uEGWBOXTEHA.1168@TK2MSFTNGP11.phx.gbl... > Help, > > Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, > then the macro would place the input data in a cell, let's say d9. > >...

How do identify a blank cell in a formula
Example: IF(s69=blank,"Void".... What I want to say is if a cell, (s69in this example) is blank, enter the word "Void" but the above way don't work - I don't know how to enter that in this IF formula. Barb, here is one way =IF(ISBLANK(S69),"Void","") -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Barb123" <Barb123@discussions.microsoft.com> wrote in message news:47...

CTreeView #3
I don't know what happened to my other post so I am going to try again. I am trying to add a CTreeCtrl to a application with a CLeftView object. I have been trying to find so sample code of how to insert the root and the sub root items with some explaination of what each line of code is doing, or some basics of how to work with a CTreeVew & CTreeCtrl. If someone could direct me to more information I would appreciate it very much. Thanks ! -- Just Al CTreeView is pretty irrelevant here; all that matters is the CTreeCtrl. To insert an element, you do (I'm going to restrict th...

How do I use Conditinal Format for another cell?
Assuming Cell A1 is the value "Red" or "Green" or "Yellow" How could I have a formula on Cell "C1" containing Conditional formatin the same as below if A1 is "Red" then Bacground of A1 turns to red if A1 is "Blue" then Bacground of A1 turns to Blue if A1 is "Yellow" then Bacground of A1 turns to Yellow I assume that your statement if A1 is "Red" then Bacground of A1 turns to red should be if A1 is "Red" then Bacground of C1 turns to red ??? Assuming that to be true Select C...