In the formula below the AND function cannot recognise the named range "KolomFFundering". I need the named range because when I insert a row the range must also change. Is there any way to rewrite this formula without losing it's function? The range of "KolomFFundering" is F13:F23.
=IF(AND(J12<>"";KolomFFundering<>"");"verwijder getal uit kolom 'hoeveelheid'";IF(KolomFFundering<>"";SUM(KolomFFundering);IF(P12<>"";P12/L$227;"")))
How can i return a blank cell if the formula is looking at a blank cell with IF function?
E.g. IF(A1=" "," "," ")
(This is part of a bigger statement which returns a date if and when there is a date in the cell, if there isn't it comes up with an answer even though it should be blank)
i have a bit of a problem with the sum() array function.
im trying to get the sum of an array based on 2 conditions.
my excel sheet looks as follows; i have 10 columns, which define also the names of these columns.
AccountLink - AccountLevel - Master_Sub_Account - Debit - Credit - Account_Type - Period - Project - ProjectCode - ProjectName
I want to sum for an AccountType the debit, where project is 3.
the formula i use is:
=SUM(IF(Account_Type=L24,IF(Project=M24,Debit)))
(with {} brackets of course, and CSE'd).
BUT, it is giving me a #NUM error.
why? (when i replace the column names with the actual ranges, i.e. F1:F211 for AccountType, it works)... could this be due to the fact that the named ranges are composed of the full columns or something? And, is there a way to solve it that it will work with these named ranges...?
is it possible to change the named range used in a formula based on the value selected in a dropdown. I have 4 named ranges NR1, NR2, NR3 and NR4. I would like to have a VLOOKUP that uses the named range that has been selected in a drop down. So if I change the drop down option I would get a different result as it will be looking at a different range.
It is not possible to put all of the values together as one named range as the intention is that I would select the range on a hidden and protected sheet so people would only see what they need to but I only have to manage one master sheet instead of having to create a new sheet for each range.
How would I used the indirect function to reference a named range in the workbook in formula creation.
For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:
=sum(indirect(A1))
In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.
I'm using Excel 2007.
I have several named ranges with 4 column each. The named ranges have all the same names but with the tow last digits ranging from 00 to 50 by increments of 5 (ie 00, 05, 10, 15 etc.). The 4 columns for each of the named ranges have the following headings; 10, 100, 50 ,70. I have two cells with drop down lists with the list of the suffixes of the named ranges in cell e5 (ie 00, 05, 10, etc) and in g5 a drop down list with the 4 headings for the columns (10, 100, 50, 70)
I want to refer to these 2 values so that in column B, I can get the values of the column chosen for the given named range. For example, the column "100" for the named range THRESHOLD_10. In cell C1, I entered the following formula ="THRESHOLD_"& E5 which gives me the named range name. I entered the following function in the first cell of column B = index(C1,2,G5) but get a #REF! answer. If I write the following formula =index(THRESHOLD_10,2,G5) I get the right value.
how to get about it, and indirect(C1), but still gets a #REF! message. When I evaluate the formula, the named range appears with quotes "" which might be the reason that it cannot work. I am nearly there...but for the syntax and cannot get that working!
I have a split named range covering non-contiguous areas, ie:
Name: status_web
Range: =$F$14:$I$122,$F$700:$I$746,$F$798:$I$830,$F$905:$I$933
This appears to work fine when selecting the name however it returns a #VALUE! error when using it in a formula, eg: = COUNTIF(status_web, "*p*"). Is there any way to get this to work, or do I need a separate named range for each contiguous section? NOTE: Not sure whether this extra fact would through a spanner in the works, but I am referencing this named range from a separate workbook.
Passing Named Range into User Defined Function
MrExcel.com | Excel Resources | Excel Seminars | Excel Products mcm91201
Depending on time of day and computer I am sitting in front of I am using:
WinXP Pro SP2 with Excel 2003
Win7 Pro SP2 Excel 2007
Win7 Pro SP2 Excel 2010 on PC
Win7 Pro SP2 Excel 2010 on Mac Mini running Boot Camp
OSX Excel for Mac 2011
I have only tried this on Win7 Pro SP2 Excel 2007 but need it to work on all.
I enter the values 0, 1, 2 ... 89, 90 in cells A1 to A91
I select A1:A91 and name the range 'angle'
I create a user defined function:
Public function sindeg(value As Double) as Double
sindeg = sin(worksheetfunction.radians(value))
end
I want 'value' for the function in a cell to be replaced by the corresponding value in the same row (or column) in the named range 'angle'. For example (using commas as column separators). This works for Excel functions like sin, cos, radians, etc.
********** Worksheet Contents **********
A1 = 00, B1 = sin(radians(0)), C1 = sin(radians(A1)), D1 = sin(radians(angle)), E1 = sindeg(0), F1 = sindeg(A1), G1 = sindeg(angle)
A2 = 01, B2 = sin(radians(1)), C2 = sin(radians(A2)), D2 = sin(radians(angle)), E2 = sindeg(1), F2 = sindeg(A2), G2 = sindeg(angle)
A3 = 02, B3 = sin(radians(2)), C3 = sin(radians(A3)), D3 = sin(radians(angle)), E3 = sindeg(2), F3 = sindeg(A3), G3 = sindeg(angle)
......
A91 = 90, B91 = sin(radians(90)), C91 = sin(radians(A91)), D91 = sin(radians(angle)), E91 = sindeg(90), F91 = sindeg(A91), G91 = sindeg(angle)
Column A = input. Columns B, C, D, E and F all calculate the same value by row. Column G fails with a #VALUE. In row 1 the value of angle[1] = 0 therefore column D = C = B = sin(0) = 0. In row 2 angle[2] = 1 therefore B = C = D = 0.017452
How can I get the user defined function sindeg(value) in column G to accept the named range variable 'angle' like the Excel function radians(value) accepted it in column D?
This functionality should work horizontally as well as vertically. For example enter 'angle' A1 to CM1 then have sindeg(angle) filled from A2 to CM2. It should also work in the case where the named range 'angle' is a single cell.
I am sure that this is a simple variable type definition problem in my user defined function: should the input variable be defined as type Range? Or something more exotic?
The brute force approach is to have the function determine the input value by passing in the named range, working out dimensions, calculating offset between the cell the function is in and top (left) of named range, then counting down (right) to pick the correct value. However I cannot see adding all that code to EVERY function. Occam's Razor says there has to be an easier way since Excel built in functions seem to do it readily.
I have seen one of your posts in which you make a graph update based on dynamic named ranges. Here is my problem, I already defined the name of my ranges as:
ChurnDiario =OFFSET(Clientes!$E$25,0,0,1, COUNTA(Clientes!$E$25:$AH$25))
Ejex =OFFSET(Clientes!$E$8,0,0,1,COUNTA(Clientes!$E$8:$AH$8))
The issue is that when I tried to enter the values and category (x) axis with:
Values: =offset(ChurnDiario,0,0)
Category (X) axis labels: = offset(Ejex,0,0)
On both, excel replies “That function is not valid”. I’ve tried, but still can’t make it work.
Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
View 2 Replies View RelatedI'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.
Picture2.jpg
Using the following code when updating just one criteria with several charts
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then
[valMeasurePicked] = ActiveCell.Value [code].....
which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.
if I can use a named criteria as well as a named range. In essence what I am looking to do is count certain cells that meet the criteria in a certain named named range,
View 9 Replies View RelatedMerge two columns into one list in excel
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
Here's the formula from the example:
Code:
=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))
I've played around with it, but could not come with any that worked.
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select
i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop
I have a list of data with a column of dates in the format ddmmyy, that I'm trying to get Excel to recognise as dates. What's the best/easiest way to do this? I've tried text->columns then concatenating back together with dashes or periods in between (like normal date format), but Excel doesn't recognise it as dates...
View 3 Replies View RelatedI am using countif to count a range of cells D10:D34 returning a PB as a result of a formula in Col D.
The countif formula (=Countif(D10:D34,"PB") is in cell D39 and the result in D39 populates a cell in another worksheet. The formula in Col D also returns =PB results. I have placed another Countif formula in cell D41 (result to also populate a cell in another worksheet) to count all =PB's using (=Countif(D10:D34,"=PB"). The second countif doesn't work correctly. It counts all PB's and =PB's.
How can I get VBA to recognise if a certain report is already open?
Basically, what I want to do is if the report is open then just copy the sheet across else if the report is not open the open the rpeort and copy the sheet across.
I'm only struggling with the first part, I can get it copying over fine but its the "if the report is already open" part I can't get my head around...
I have a report that has been running in it's current state for several months with no issue. However, today it has decided that it doesn't understand the word 'selection'.
eg: selection.copy gives compile error.
Now... obviously NOT USING selection is preferred. However, let's put that aside for now because there are LOTS of these in this report and it's never had an issue until today.
Is there some setting that could have gotten changed or something?
It's happening on multiple computers and we're running Excel 2003 (standard) with SP3.
I'm trying to make a spreadsheet to organise my bank statements. I have raw data from online banking and I want to sum all the transactions that have "SAINSBURYS" in, for instance. I am using SUMIF to add the totals only if "SAINSBURYS" on the totals page matches "SAINSBURYS" on the raw data page. The problem I'm having is that the raw data cell doesnt just display "SAINSBURYS" but "SAINSBURYS LONGWATER BCG 12345678" for example, and so i cant find a way for SUMIF to recognise that it contains "SAINSBURYS".
View 5 Replies View RelatedIn my attachment, I had try to create a PO system that will show a dialouge box and capture input data of the dialogue box into the worksheet.
However, I am not able to make the macro recognise any manual deletion from the worksheet (e.g. delete any row in between the full data). Hence, when I input new data, the row will start from the last register counting of rows (i.e. Range("g1") in my attachment.) and it will leave a blank row after the last row of data and so on...
I will also like to know if I need to fix the "date" input to select from a "calendar" pop-up and input the date format into (e.g. year into column a, month into column b and day into column c) 3 separate columns. Is it possible?
I ask because I would like to change the value of a cell within A:A to TODAY() if the user right clicks on it - or with some keyboard combination.
View 9 Replies View Relatedwhenever I select a series of dates for a line graph x axis Excel does not recognise the values as dates for the purposes of the scale tab in graph options. I suspect it may be because the dates are the result of formula in the relevant cells.
View 7 Replies View RelatedI can't figure out why I can't get values in D column, sheet1. Table array is on sheet4. (Ignore Error in column E... I just cut data from paste sheet because it was too large). Also, can't figure why is so huge :-S But that's not problem right now...
View 3 Replies View RelatedI have a table (approx 10 rows x 10 columns) that I am trying to lookup. I have to first look across the top of the table (cols 2-10) to find a name, then look down for a value (exact match) within than array and then find the corresponding value (in that position) in the 1st column. I have used 'name manager' to name these arrays (in columns, rows 2-10).
I have setup the names of the arrays with a drop down list (as per some utube videos). When I try to evaluate the match function (with the array name as a cell reference) i get #value. When I directly type in the name of the array into the match function I get a correct answer.
Should I be using other functions such as indirect, choose, etc instead?
I have named range in column A = holfrom and another in column B = holtill
When I try to use NETWORKDAYS(holfrom;holtill) I get an error VALUE
Is it even possible to use this function with named ranges
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies View Related