# named

1. ### Sum the entire column of named range except the first row

Hi Folks, I have a question, is there a way (a formula) that i can put in a first cell of a named column and have the formula sum the entire column EXCEPT the first cell of that same named range? This formula "=sum(index("My Named Range",,11))" results in a circular error because its trying to...
2. ### SUM by Week

I want to enter a formula in sheet name "calculation" B4 that if the Date in Sheet named "Raw Data" Header named "Date" is withing the week 44 date that in the Sheet named "Calculation" D2 sum the data in the sheet named "Raw Data" heater named "Count of Assets in BU"
3. ### if text equal count

I need a formula that i can put in a sheet named Calculation B:54 that if in sheet named Raw Data anywhere column D there the words "AIM 2019" it will add the total numbers in Column G of sheet named Raw Data.
4. ### Name manager - adding data manually as reference

Hello, Normally we create named ranges by referring to excel lists - we select the data range that is located in certain cells in worksheets. I tried to create named ranges by manually adding data to "refer to" part. But it didn't work - all the data that I added appeared in one cell, so not...
5. ### Consolidate Multiple Named Ranges Into One Pivot Table In Excel

Hi all, I am having trouble with the consolidation of my named ranges into the one pivot table to finish a dashboard. I have got all the named ranges into the data source. Each named range is the same shape: that is, each consists of 4 columns. Columns are Amount, Animal, Location, and Colour. I...
6. ### Automatically typing the whole list by the list name

Hi there, I have stucked in the above said. I have named the lists. Like 12 different training given in April month, I named the training list by "April". Now I want to show it like if I select "April" in a drop down list, the names of all training should come in next or desired cells by list...
7. ### Copy date from a different Sheet

I want to be able to copy data from a different sheet to a specific feel in a another sheet. I have a sheet named GSO Inventory Stats and a sheet named Retag metrics. I need to put a formula in Column I, that if Column H in sheet GSO Inventory Stats matches the sheet named Retag Metrics Column...
8. ### Delete ranged names

So I have an interesting one for you all. I have a master workbook I made for a gradesheet and put it in the public folder. When a new class starts the instructor will make a new folder for that class and copy the master book into that folder. It is still in beta mode, so as the classes have...
9. ### Copy Data From Sheets

I have a Sheet Named NEA Status and a sheet Named Inventory Stats I want that if Column D in Sheet named NEA Status Matches Column D in the sheet named Inventory Stats move the data from Column H in the sheet Named Inventory Stats to Column H in the sheet named NEA Status.
10. ### Return cell value based of row Named Range and Column Named Range

Hi All I want to return the cross reference of 2 named ranges. There is a sheet called Front Page where i want to return the result and the information is on a sheet called Commission Row named range = Partner Column named range = Finance Both of the named ranges will be referenced in as...
11. ### Collapse/Expand grouped columns based on cell value

Hi All, I have a worksheet with 52 weeks of personnel planning. These are grouped columns with an empty column in between each week. The groups are in columns E:PL. Each of the weeks are named ranges. Columns A:D have text (ie. work path, area, etc.). For the user, the columns are frozen to...
12. ### Referencing Drop-down list error #n/a

Hello, I have a multi-sheet workbook and I am having problems with a cell in sheet 1 that references another cell in sheet 2. That second cell is populated by means of a drop-down list and I’m sure that’s where my problem lies but I don’t know how to fix it. I keep getting the #N/A error...
13. ### Indirect Function Alternative

I started using the INDIRECT function to create a super dynamic Sumif that references a named range based on criteria. However, I noticed that my calculation time has increased significantly since taking this approach. I read online that this is a very volatile function that performs slowly...
14. ### Delete all defined named ranges from a selection

Is there a way to delete all named ranges from a selection? Here is the selection that I am trying to delete the named ranged from: Range("CompOverviewTable[[#Headers],[Competitor_one]]").Select Range(Selection, Selection.End(xlToRight)).Offset(-1, 0).Select
15. ### How to find where a named range is being used for data validation or as a rowsource?

I have a named range in a large spreadsheet that I suspect is being used for either a) data validation for some cell or b) as a rowsource for some combobox on a userform. Is there a "simple" way to find out where exactly it's being used? My understanding is that trace dependents will only check...
16. ### Matching value in Range("A5") against a list of items

Hello, I have a script with an IF Range("A" & r) value is also in a lookup list AND Range("I" & r) THEN do something. I know I can do a bunch of ORs but that doesn't seem particularly neat. IF Range("A" & r) is in Lookup list named Type AND Range("I" & r) IS NULL Then DoSomething. Any ideas...
17. ### VBA to create a named range on each sheet with name of value in cell A2

Hi there, First time poster, but long term user of the help on this forum I'm in need of some help. I have a workbook with 468 sheets of data. Each sheet has a table in the range of A4:AA500. I need to create a named range for each of the table ranges on every sheet and name it as per the...
18. ### VBA to Filter Pivot Table based on Start/End Date in Cells

I have a pivot table named PivotTable2 on a Worksheet named Three Pivots. One of the FILTERS is named DATE_DUE. I need the filter to have only the last five days checked. Currently I have the start date in cell C5 and the end date in cell C1 (If I don't need the cell reference, that is okay)...
19. ### Macro for opening a pdf

Is it possible to have a macro in my personal workbook that will open a pdf named job list thats in a certain folder.
20. ### VBA event based password for one worksheet

Hi All, I wanted some help regarding VBA for worksheet passwords. I have a worksheet named "Code" in my workbook. Cell A1 in this worksheet has the value COSPASS. I have another worksheet named "Logs" which is locked using the password COSPASS mentioned above. I wanted an event based VBA...