Hi, sheet1 data 2 a 5 b 12 c sheet2 data 2 3 4 I would like to write a macro as follows: if I find the value in column a of sheet 1 IN column a in sheet 2, hide that column in sheet one (loop for the range of cells). I have the following so far: I am clearly having trouble figuring out the find portion. Thanks. Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error GoTo Exits: If Selection.Rows.Count > 1 Then Set Rng = Selection Else S...

Ok, if any of you guys can help that'd be great. I have a rather large column thats full of different model numbers. I want to be able to list each unique model number and how many times it appears in the list. The model numbers are combinations of numbers and text. Any help would be awesome. Thanks in advance -- ab500 ------------------------------------------------------------------------ ab500's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24456 View this thread: http://www.excelforum.com/showthread.php?threadid=380527 A pivot table would be ideal:...

Is there a formula or function that will return the cell address? Example: using cell range B1..B10 in which one cell will have an input value of 1. If cell B2 has a value you 1, is there a formula that will look at all cells in the range and return "B2" indicating that B@ is the cell w/the value? Thanks. Hi Dan =CELL("address",INDEX(B1:B10,MATCH(1,B1:B10,0))) Cheers JulieD "Dan" <anonymous@discussions.microsoft.com> wrote in message news:170401c49c08$a7835510$a401280a@phx.gbl... > Is there a formula or function that will return the cell > ad...

This is a multipart message in MIME format. --=_alternative 0051FBC080256E20_= Content-Type: text/plain; charset="us-ascii" Hello. I am using Excel 2000 with Windows 2000. I have a spreadsheet that will not update any shading. The cells are not locked and the spreadsheet is not protected. When I try to change cell colour, the screen shows the change but a print preview or print hardcopy does not. I have copied the worksheet into another blank worksheet and the formatting works fine. Although I have a workaround I am curious as to why this is happening. Can anyone shed any light ...

Hello and thank you so much in advance for helping me - I am working o a complex project that requires me to identify certain text within string of variables and label the cell as "Home Stereo", Electronics etc. A2 is http://w.126x.com/?kw=home%20electronics&src=excel&ref=help A3 is http://w.126x.com/?kw=home%stereo&src=excel&ref=help In B2 I would like to enter a formula that says if in A2 the word "home" and "electronics" appear then B2 would say "Home Electronics". And I would like in the same formula to assign different labels so...

From an Access Query. I've got a list with the unique ID's for reports, and the next field indicates what Language they were written in. This was from a Many - Many relationship in Excel, and many of the reports were written in many different languages. ReportID CountryID 1000020 Td 1000020 Tz 1000020 Ug 1000020 Zm 1000020 Zw 1000035 Ao 1000035 Mz I've got over 10,000 records like this and would like an array formula that for each Report ID (and i'...

Here is my question: I have two cells, side by side on the same row. One has an amount in it, the other has a list of names, comma delimited. I need to transpose the names so that there is one row per name, with the amount next to it (same amount for each row). How can I start this going? Thanks much. Use data>text to columns, delimited and comma as delimiter, make sure you have as many empty column to the right as there are names, then click finish. Now copy all the cells with the parsed names, select the first cell where you want them to start and do edit>paste special and select ...

This formula should work: =AVERAGE(OFFSET($B$2,(ROW(A1)-1)*3,,4)) this averages range B2:B5 copying down will automatically change it to: =AVERAGE(OFFSET($B$2,(ROW(A2)-1)*3,,4)) which averages range B5:B8 -- Best Regards, Luke M "penny" <abby@hotmail.com> wrote in message news:... > Message-ID: <32af0b05d7ed4af89105200502fb35a4@newspe.com> > X-Mailer: http://www.umailcampaign.com, ip log:68.45.155.120 > Newsgroups: microsoft.public.excel.newusers > NNTP-Posting-Host: 22.bb.5446.static.theplanet.com 70.84.187.34 > Path: TK2MSFTNGP01.phx.gb...

I want to use Conditional Formatting. I opt for "formula". The condition should be that a cell F9 that is formatted to Date 14-Mar-07 (see cell format) need to be filled with a date (any date). If empty then FALSE. What is the formulah I need to fill in. DATE(F9<>0) failed for me. This is the condition that is set for cell H9. Thank you. Bart Excel 2003 =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) -- --- HTH Bob (there's no email, no snail mail, but somewhere sho...

frm_courses, sfrm_participants When I enter a new course into the frm_Courses and begin to enter names into the sfrm_participants, I'd like for those attendees that have attended a course with the same name/code to be identified in some way. Other info: sfrm_participants is displayed in datasheet vew only I have a duplicate attendance query (qry_Duplicate_Attendance) that returns Attendee name and dates of this attendee having attended, but I haven't figured out how to put these things together in a manner that identifies the attendee on the sfrm_participants. Coniditional f...

in the drop-down box when you make a selection such as "repair" "customer service" or "rental" I want it to return contact info that would be different for each possible choice but to display in the adjacent cell. Seems that that this should be easier than it is turning out to be. Any suggestions??? Use a lookup formula in the cell with contact information, so it looks up the appropriate value from a table based on the selection in the dropdown. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - ...

I have a roughly 25 rows of formulas that i need to copy to a new tab that reference numbers within the same worksheet. How do i get these newly copied formulas within the new tab to still reference all of the information in the original tab? On Apr 24, 5:10=A0pm, Cheese man <Cheese m...@discussions.microsoft.com> wrote: > I have a roughly 25 rows of formulas that i need to copy to a new tab tha= t > reference numbers within the same worksheet. > > How do i get =A0these newly copied formulas within the new tab to still > reference all of the information in...

i am having continuing problems with the following: I have a spreadsheet which lists project status. One of the columns shows the installation subcontractor. I want to have a drop down box where you select the contractor, and once selected, the list automatically (or by use of a macro button) filters to that selected. I have put the drop down box in, then created a vlookup to convert each contractor to a number, and placed a hidden column with this info. Where i am stuck is how i get the data filter to look at my link cell from the drop down box. Any help is appreciated Richard R...

As previously posted, I am trying to build a formula that updates its cell references automatically based on what line the formula is entered. My formula will be more complex but for the sake of simplicity, here's an example of what I'm trying to achieve... A1 = 100 A2 = 25 A3 = C3 B1 = 50 B2 = 10 B3 = C3 C3 = formula that adds A1+A2 or B1+B2, depending on the row number. Please remember that this is a simple example and that my actual formula will be more complex and therefore, an IF statement will be too long for what I want to achieve. In other words, my formu...

I have a set of data in sheet 1 and I need to transfer to sheet 2. I have used VLOOKUP to transfer 10 selected rows of data from sheet 1 which displays in sheet 2, APART from coloured cells. These cells contain no formula's, they are just shaded, but VLOOKUP does not transfer this colour (it just displays 0). Is there a way to get the coloured cells to show in sheet 2? Not using formulas or UDFs..as they can only return values/calculated results not formats.... You will need to write a macro to do this. -- Jacob "The Message" wrote: > I have a set of d...

If I am in cell K5, how can I select B5:J5 in order to copy and paste i somewhere else? Similarily, If I am in cell K500, how can I select B500:J500 in order to copy an paste it somewhere else? The range I want to copy will always be in the same row as my activ cell and always from columns B to J. Thanks, Mo -- Mo ----------------------------------------------------------------------- Moe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1250 View this thread: http://www.excelforum.com/showthread.php?threadid=39297 See this example http://www.rondebruin.nl/co...

I've got a bunch of cells set up to be the sum of 2 specific cells. The formula I'm using is ($G$216+$G$3617) for example. I now need to duplicate this in column I so my new formula should be ($I$216+$I$3617) but when I cut and paste from G to I, the formula is still referencing G, which is to be expected. Is there a quick way to update the formulas so they reference I instead? Thanks. Mark Find/replace -- Jim "Mark Christensen" <markc@ssbrakes.com> wrote in message news:OeSCRjX9HHA.484@TK2MSFTNGP06.phx.gbl... | I've got a bunch of cells set up to be th...

When writing a function in a cell, it always refers to that cell, regardless if it moves. (Rows and columns are inserted) When writing in VBA how do I make VBA track the cell's movement, so that when I insert rows and columns with VBA code it is still referring to the same cell? Here is what I am using: MyVAR = Cells(20, 6).Value 'If I insert rows and columns it still refers to cell 20,6 Thanks in advance for any help with this! You could name that cell (insert|name|define) Then you could use: myVar = worksheets("sheet1").range("mynamehere").value f...

Good Afternoon, I need to create a series of Case conditions based on the value of a cell on a certain worksheet in my open workbook..For the life of me I cannot get the syntax right and I'd appreciate any help i could get. My condition is on a worksheet named "Lookup_Values" Cell "M3" is a value used as a case condition. what I want to do is retrive that value and use it to compare against each case like below. RPT_Date = ("Lookup_Values!M3") Select Case True Case RPT_Date = "January" Perform my actrions Exit Sub ...

Hi. How to do the following: 1) Return the cell reference of the cell eg: Type this formula in cell A1: =return_cell_reference_of_that_cell() {The above is a fictitious formula only} The answer will be A1. Preferably, it is great for me to decide on how the reference is displayed, eg: - absolute (ie $A$1) or - column-absolute (ie $A1) or - row-absolute (ie A$1) - relative (ie A1) 2) Return cell reference(s) of the target eg: Type this formula in cell B1: =return_cell_reference(target_cell) {The above is a fictitious formula only} eg: =return_cell_reference(A3) Answer: A3 =return_cell_refe...

I am sure the answer to this is simple, but I have searched trie Excel's Help, the NG's [via Google] and the Forums here excelforum.com and can't seem to find the right information. I am currently using Excel to produce sheets which accompany th documents our Auditors ship to storage, (we call these Transmittals). Basically, I want a formula in one cell, (part of a number of row repeated at the top of each page), to refer to a cell no matter what may do to the worksheet as each Auditor's list is taken from a Maste which I edit accordingly, (i.e. I delete any irrelevant rows...

I am working on a spreadsheet, one of the columns is to enter a product name. The products have to be specific as they are seleced from a VLookup table. I have used select from list on the validation, and specified the range to choose from, but everytime I click on the down arrow to select the product I am at the bottom of the list. Does anyone know how to set this so I could enter a letter ie. 'b' and go to the b's on the list of 't' to go to the t's on the list to save scrolling up and down. Any suggestions would be appreciated When you open the Data Val...

I have a function named mysub and with parameters that has to be supplied to the function. I want that when the cell where the function is to be inserted, the user can type the arguments of the function sub after they have run the sub. Lets say, that after the sub is run, they get =mysub(..) with the cursor in between the parenthesis and so that they can move with typing of the arguments/parameter of the function. So, in summary, the advantage is that, the user should not type =mysub through the keyboard or they donot even need to remember that provied that there are large numbe...

I want my FALSE statement to have a "<" infront of the number reference by the calculation, and don't know how to make the FALSE have text an an equation. =IF(D9>1,(D9*D8),"<"(D9*D10)) I can get my FALSE to have just a < sign if I do this: =IF(D9>1,(D9*D10),"<") I can get my FALSE to have the number calculated by the equation if do this: =IF(D9>1,(D9*D8),(D9*D10)) But how come I can't get it to read "<"(#value) by using the firs equation listed above? Thanks for the help in advance -- jcob -------------------------...

Hi Guys, Hopefully you can help.... If i have entered data and a background colour into cell A1 how do I auto make cell A3 copy what is in cell A1 (background colour include)?? Hope you can help, Thanks, Nick In A3 you can enter this formula: =A1 or better still: =IF(A1="","",A1) However, a function cannot change the format or colour of a cell - it can only return a value. One possibility you might consider, though, is to use conditional formatting on cell A1 to give it the colour (eg if it is not blank, then make background colour green). Then you could apply t...