Is there a way to have Excel return a range of values in a drop down in one cell based on input from another cell. As an example: Cell A1 has the text "PVC Pipe" I want cell A2 to get input from A1, read through a table, and return the corresponding values (sizes) of PVC pipe. The results in A2 would be the following in a drop down: 4" 6" 8" If cell A1 contained the text "Metal Pipe" it would return values (sizes) of metal pipe from a table and not display the sizes of PVC pipe. 10" 12" 15" The bottom line and...

Hello, This is my VBA macro. I'm getting a "Subcription out of range #9" error message on: Windows(fname).Activate Can someone help fix it, if possible? Regards, fname = Application.GetOpenFilename Workbooks.Open filename:=fname Windows(fname).Activate Hi Jeff, Drop the line: > Windows(fname).Activate The opened workbook is the active workbook. --- Regards, Norman "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:E599F406-0915-4F63-9244-822FA00994CE@microsoft.com... > Hello, > This is my VBA macro. I'm getting a "Subcripti...

Hi, I am developing a cashflow spreadsheet, and need to add a range of values (in column B) based on the criteria that they are relating to a set week, ie in column B has the amount to be paid, and column C has the date the amount is due. I need to find out the total amount due between 2 dates. Does anyone know how I can do this? Hi With start date in B20 and end date in B21 try this: =SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5) Regards, Per "Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen news...

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel New to excel and following a video tutorial. I am using auto-fill to create a range of numbers. The cells are selected and I try to delete all the content in the range by pressing the delete button per the tutorial but only the first cell in the range is deleted. Please help a newbie. Are you using a laptop or a condensed keyboard by any chance? If so, the key labeled 'delete' is the equivalent of 'backspace' & does delete only the content of the active cell in the range. You need to hold ...

I need the formula that will sum a range within a range. Example: the primary range consists of the following numbers: 1,2,3,4,5,6,7,8,9,10 and I need to know how many numbers are >3 and <8. First I need to know the sum ot the qualifying numbers; second I need to know how many items there are. Stumped and exhausted...anyone's help will be greatly appreciated! -- Need to Know Try these... For the SUM: =SUMIF(A1:A10,">3")-SUMIF(A1:A10,">=8") For the COUNT: =COUNTIF(A1:A10,">3")-COUNTIF(A1:A10,">=8") -- ...

Hi there, I have searched through this forum cannot find a solution to what I need so here goes (or perhaps I have missed it). I have a workbook containing 11 worksheets - one for each country with the first worksheet containing a set of standard charts which are reused for each country. I am currently manually amending the data range and selecting the worksheet and highlighting the data for each country (so have to do this 10 times). The data is in the same location on each country worksheet so really the only amendment is the worksheet name. How do you recommend that I speed up t...

How to display the name of a range when a single cell within that named range is selected. Kathy one way: This displays the named range(s) that the cell belongs to, if any, in the Status bar. Put this in the ThisWorkbook code module of your workbook. Private Sub Workbook_SheetSelectionChange( _ ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim sRanges As String Dim nmName As Name Dim bInRng As Boolean On Error Resume Next For Each nmName In ThisWorkbook.Names Debug.Print nmName.Name, nmName.Re...

Hi I'm trying to plot the length of time staff have worked for a company on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a seperate column that has converted the months worked into each of these catagories. I then would like to show on the x axis the percentage of staff that fall into each period. I would certainly appreciate any assistance! Wayne Wayne Beasley wrote: > Hi > > I'm trying to plot the length of time staff have worked for a company > on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a > seperate column that has converted t...

Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Is this what you're looking for...? In workbook 2 enter "=[Book1]Sheet1!$A$2" in cell A2. Do the same for cells B2 through K2 with the correct respective links. Hope this helps. -Chad "KG" wrote: > Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I > point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Or, if you don't like to ...

I have two ranges of XYZ coordinates. One is named R1 and the other R2. Is there any way in Excell 2002 to bascially say R2-R1=R3 so that R3 is a range the same size as R1 and R2, where each cell in R3 is is subtraction of the corrisponding cells in R1 and R2? Sort of like this: r1= 3,4,5 r2=3,4,5 r3=6,8,10 of course I need to do this on a two dimentional range, there are multiple XYZ points. Are the three values of R1 (3,4,5) in separate cells? Are you adding or subtracting? Please spell out the problem in more detail -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from e...

Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

I know how to select a range of cells and copy: Range("O2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy but now 1. applying the calculation, which should be value of cell * 1000 2. special past values only to same selected sells ? You enter 1000 in a separate blank cell and copy it then highlight the range to be multiplied and past special-> multiply. that will multiply all the cells by 1000 in the pasted range. Regards, OssieMac "S Himmelrich" wrote: > I know how to select a range of cells and copy: &g...

I have what seems a pretty straightforward question - How, in *one* formula, can I check a range for a value? i.e. Check whether any cell in the range A1:A9000 contains a zero,and return 'Yes'. Any help greatly appreciated......Jason Something like this will do it: =IF(COUNTIF(A1:A9000,0)>0,"Yes","No") and will not count blanks as equivalent to zero. Hope this helps. Pete On Nov 23, 12:41 am, Jay <s...@dummyaddress.spam.com> wrote: > I have what seems a pretty straightforward question - > > How, in *one* formula, can I check a range for a ...

This is my first post here...I hope that i've chosen the right category.... My problem is similiar to other people's when it comes to consolidating ranges with Pivot Table (sorry for my english...). I've searched the forum but I haven't found either solution or workaround for my problem. My data is divided into 2 sheets, because i need to use more than 255 columns (about 433). It looks like this: col1(ID) col2(Chain store category) col3(address) col4(date) col5-col255 (product's data). One product uses 9 columns like: space on a shelf, price, comments.... Bacause...

I am trying to use Excel to check if column B = column A then = c. Which works fine for one row, but I would like to sum the results. x x 1 y 1 y 6 x x 2 y 1 y 5 x x 3 y 6 y 4 x x 4 y 3 x 3 x x 5 y 9 x 2 x x 6 y 1 y 1 x x 7 y 8 y 0 x x 8 y 2 x 9 x x 9 y 1 x 8 = ?? = ?? = ?? Thanks, Rob -- hooper222 ------------------------------------------------------------------------ hooper222's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30108 View this thread: http://www.excelforum.com/showthread.php?threadid=497943 Sounds like you can use ...

I'd like to format Columns C, D, E, F, and G based on whether their cell content is greater than the cell in the same row in column B. It seems that the conditional format is always based on a single cell, not a range like this. Can anyone explain to me how to accomplish this? Thanks so much for your time and knowledge, Josh Just make sure you "anchor" your formula. I selected C2:G99 and with C2 the activecell I applied format|conditional formatting I used a formula of: =c2>$b2 The $b tells excel to always look at column B. No $ in front of the 2 means that excel wil...

I have created a sample using data analysis but I want the sample to include all of the columns, not just the sample #. For example if I use data analysis to select a random sample from a worksheet it will return just the sample area I selected. It appears I can only sample on numeric values so I have and the numbers 1, 2, 3....in the first column. What I would like to see is when it returns the sample that it returns all columns not just the first one. Hope I made sense. Jamie ...

Hi I try this code, but I will not work. sub selectnamedrange(fblock as Integer) dim frow_string frow_string = "f" & fblock & "_frow" Range(frow_string).Select ' Which is translated into Range("f1_frow").Select ' f1_frow is a named range in the current worksheet..... ' The sub contains more code, but this is the part that does not work.... end sub. Any hints appreciated Mr. Smith Works fine for me. Are you sure that fblock is 1? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "...

How could I write a function that would return a number in one cell, if a number in another cell falls between a certain range? (if A1 is between C1:D1 then E1, else "0") (if .23 is between .039:.002 then 15, else "0") =IF(AND(A1>=C1,A1<=D1),E1,0) and =IF(AND(A1>=.002,A1<=.039),E1,0) -- HTH RP (remove nothere from the email address if mailing direct) "sanpanico" <sanpanico@discussions.microsoft.com> wrote in message news:F5A58078-1AE7-43B1-A773-0C75A386AFF2@microsoft.com... > How could I write a function that would return a number i...

I cannot get my dynamic ranges to work. I've followed several articles but can't get it to work for the row/column combination I have going. A B C D E F 1 startmo Feb-08 2 Noofmonths 3 3 4 Jan-08 Feb-08 Mar-08 Apr-08 May-08 5 2 4 6 9 11 Desired end result 6 MAR-08 Apr-08 May-08 7 6 9 11 Row 4 is a named range called "allmonths" I'd like to have formula...

The defined range below extends the range beyond the cells where the data ends. The start of the range is Centre!$C$77 the end of the range is C1054. The last cell containing data is C807. The data in C is the result of a formula and is in the range C77:C1000 =OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1) Anyone familiar with all of this? Thanks if you can help. Pat You obviously have data in C1:C77 thus it will be counted, you can use =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536),1)) Regards Peo Sjoblom "Pat" wrote: > The defined range below extends the ran...

Hi All, I want to select a range of cells and format the range of cells based on a "offset" feature... The last couple lines in my code are: ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Where the active cell reference is a moving target depending on the amount of data in the worksheet. How do I now say - select this cell, plus other cells in this range of cells and format them with a border, and a color? I can't get the syntax right. I I record the macro, it only gives me this: range("I32:L37&q...

I have a large worksheet full of formulas which all need to be rounded to the nearest thousand. Without adding the "ROUND" command to every formula individually (which would take forever) is there a way to apply rounding to a whole range? You can just use formatting to visually round what you are seeing, and if you have other formulas that refer to that whole range, then you can adjust those to round the data that they are getting from that range. Give us some more detail and we can be a bit more specific. -- Regards Ken....................... Microsoft MVP - Ex...

I'm having trouble plugging the equation from the trendline of a chart back into the Worksheet to get calculated interpolated values. In essence, I added a 4th order polynomial regression line to an X-Y chart, and then used the displayed equation in the worksheet, working off the X values to get the Y values for the regression curve. It doesn't work. The calculated values are way, way off (several orders of magnitude). I tried it again, lopping off 977 from the plotted Y values, in case dealing with large numbers was causing problems. Again, no joy. I came across an earlier thread desc...