How to return a range of values in a drop-down.
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:
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.
The bottom line and...Out of Range
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?
fname = Application.GetOpenFilename
Drop the line:
The opened workbook is the active workbook.
"Jeff" <Jeff@discussions.microsoft.com> wrote in message
> This is my VBA macro. I'm getting a "Subcripti...Using SUMIF to add data between a range of dates
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?
With start date in B20 and end date in B21 try this:
"Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen
news...Unable to delete cell range.
Operating System: Mac OS X 10.5 (Leopard)
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
...Summing a range within a range
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
For the SUM:
For the COUNT:
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...Display range name when a cell within the range is selected
How to display the name of a range when a single cell within that named range
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...Percentage on x axis and period range on y axis
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
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 Beasley wrote:
> 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...How do you point to a named range in linked workbooks?
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.
> 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 ...subtracting a range from another range and getting a range result
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
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
remove caps from e...Cell Value as Named Range Reference
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.
http://www.wimgielis.be = Excel/VBA, soccer and music
"ste...refering to a name range area as a reference value multiple times on a worksheet at different locations
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
Is there some reason you can't post your question here instead of asking us
to look elsewhere?
Microsoft MVP Excel
"Blinds Nottingham" <email@example.com> wrote in
news:firstname.lastname@example.org...highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy:
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.
"S Himmelrich" wrote:
> I know how to select a range of cells and copy:
&g...Check a range for a value
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
Any help greatly appreciated......Jason
Something like this will do it:
and will not count blanks as equivalent to zero.
Hope this helps.
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 ...Pivot Table
This is my first post here...I hope that i've chosen the right
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
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....
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
= ?? = ?? = ??
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 ...Conditional Formatting (How to format a range depending on another range)
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
Thanks so much for your time and knowledge,
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...Sample Range of Data
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.
...Select range in VBA through variabel
Hi I try this code, but I will not work.
sub selectnamedrange(fblock as Integer)
frow_string = "f" & fblock & "_frow"
' 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....
Any hints appreciated
Works fine for me. Are you sure that fblock is 1?
(replace somewhere in email address with gmail if mailing direct)
"...Between/ range function
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")
(remove nothere from the email address if mailing direct)
"sanpanico" <email@example.com> wrote in message
> How could I write a function that would return a number i...Dynamic ranges and reporting
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
A B C D
1 startmo Feb-08
2 Noofmonths 3
4 Jan-08 Feb-08 Mar-08 Apr-08 May-08
5 2 4 6
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...Defined range difficulty
The defined range below extends the range beyond the cells where the data
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
Anyone familiar with all of this?
Thanks if you can help.
You obviously have data in C1:C77 thus it will be counted, you can use
> The defined range below extends the ran...VBA Code to select and format range
I want to select a range of cells and format the range of cells based on a
The last couple lines in my code are:
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...How can I apply the ROUND function to a range of cells in a workbo
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
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.
Ken....................... Microsoft MVP - Ex...Trendline equation in chart is wrong
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...