indirect()

  1. P

    Please help – optimizing heavy workbook containing volatile functions

    Dear all, I have a workbook that recently became heavy and unstable. The simplest of data entries and calculations are now extremely slow and laggy. Furthermore, this is a workbook that will need to be constantly updated with more data. I’m looking for suggestions on how to optimize what I...
  2. G

    Having trouble with INDIRECT formula to find intersecting rows and columns

    Hello, I am new to the forum, and hope someone can help. I need to create a "dashboard" for my end users that will automatically return a result like my sample below. <tbody> A B C D Over_4_Years Over_6_Years Over_8_Years 1 Salary_3 70,000 72,000 74,000 2 Salary_2 60,000 62,000...
  3. K

    Data Validation to only allow a 8 digit HEX value on several cells.

    Hello, I'm trying to use data validation to validate if the user provides a 8 digit HEX value. In example data validation in cell K2: Allow: Custom Formula: =AND(LEN(K2)= 8;ISNUMBER(HEX2DEC(K2))) This works but I can't manage to replace the formula with ROW() in order to use it for all my...
  4. A

    aggregate array based on condition

    I am trying to get this done with a single array formula: For row in a column array, if the array row tests positive for a condition, calculate the average of the next N rows in a different array of equal size. Each of these averages would be placed in an array, and then the average of this...
  5. B

    Automatic Date Based Formulas Without VBA

    So I now have a small business that uses cash accounting. Since I don't need much in the way of accounting, I use an Excel workbook for the numbers. Overtime I needed to see sums and averages based on time--7 days, 30 days, etc. To accomplish this, without using the dreaded--for me--VBA, I used...
  6. M

    Lookup from an index that contains dynamic pages

    I'm going to do my best to explain this issue. I have been searching for days for an appropriate answer. So, here goes. I have a workbook that is used for productivity tracking. There is a master worksheet (Master), and separate worksheets for each day of the year (mm-dd-yy). The productivity...
  7. N

    Get data validation list from other sheet of desired choice

    hello everybody, I want to get a data validation list in dress form sheet in cell J1 after choosing a fixed condition in cell H1. I have created a formoula in sheet2 but it dosent work as I paste it in data validation options list of refer to box. pls help me to get the right formula which I...
  8. D

    Help with an INDIRECT Query

    Dear All, I trying to work my way through excel but have hit a road block. I have a formula: =SUM(INDIRECT("'All_Delays'!R6C30:":R206C30,FALSE)) Could someone explain how to interpret this as my understand of INDIRECT was that you had a Column Name eg. R and then the reference field C30, so...
  9. R

    Summary for Date & Instructor Across several sheets in one workbook

    Hello everyone, Below is the worksheet I'm wanting to summarize information from. I'm wanting to summarize HOURS for each DATE and for each INSTRUCTOR across all (33) worksheets, named Week 1 to Week 33. Below is the summary sheet I have created. I'm wanting to extract info from the...
  10. R

    INDIRECT() not working:"Can't Find Object or Library" error

    Hello all, Firstly, thank you for your time. I am attempting to create a dynamic dashboard that will allow the user to select a business unit from a drop-down list, then that selection will populate a table with information from the appropriate tab. I am trying to use INDIRECT () to...
  11. R

    Help with 3D Formula to extract text using INDIRECT() and IF()

    Hi All - I'm working with a workbook that has 20 spreadsheets (tabs) and I want to search all 20 and extract a given text that is in 1 cell in each spreadsheet if the critera is met. I'm using the below formula to extract and sum (24hr) time in all 20 sheets, but I can't seem to modify it to...
  12. L

    INDIRECT() randomly breaks with ROW()

    I am trying to do dynamic formulae creation and have successfully done in the past, but I am facing a very strange issue whereby using the ROW() formula within the INDIRECT() formula breaks it and it gives a #VALUE error. If I enter 3 values in column A as below: A1: 1 A2: 2 A3: 3 I can then...
  13. H

    Make a formula more efficient and lose volatility

    Hello people, I created some time ago a spreadsheet that my team uses to keep a record of some work that we do. The basic format is that each task takes up a row of the spreadsheet, with an Id, Title etc and a cell containing any notes that people may add to the task. This notes field has text...
  14. C

    Trouble getting charts to work correctly from template

    The goal here is to get a template page which will be duplicated and renamed, and still have all of its charts ready to work without changing any data settings. However, when I create new copies, the graphs continue reading data from the original document. The INDIRECT command has been...
  15. J

    Links to Other Workbooks - Updating / Refreshing - INDIRECT vs Hardcoded

    I have an issue with links to other workbooks updating (or actually not updating). I am using Excel 2010 on a PC with WIN 7. Department managers complete financial projection files (workbooks). I have prepared a summary file (workbook) that will pull data from each of the departmental files...
  16. I

    Drop Down Dependant for a whole column

    Hi All, Im stuck!! Im trying to create a form for which column E is issues and column F is sub catergories of those issues. So the user isnt faced with hundreds of sub cats' each time, Im taken to dependant drop downs. Now comes my predicament, I can get the system working for the 1st cell...
  17. J

    Simple link to external xls file

    a1: 210.xls a2: 211.xls a3: 250.xls .... an: 335.xls In my master xls file, I want to link a cell from each of the above files listed in column A. If I type (say to cell B1) =[210.xls]sheet1!$B$6 I can get the value of cell "B6" of "sheet1" of file "210.xls" Now my question: Is there a way to...
  18. S

    using Indirect() to reference value in another sheet

    I am trying to utilize paramemeters in the Indirect() function to look up values in another Excel file which is located every month in another folder reflecting that month, eg "\2011\May2011\". The file name is the same, the path differs from month-to-month. The problem is, when I have assembled...
  19. D

    Need to Pull Rows of Data from Several Worksheets to a Master Sheet

    Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> <o:p></o:p> I need input on a formula that would be able to pull data from rows on several worksheets. The Master worksheet should be a summary worksheet of the other worksheets that meet a criteria. Also...
  20. B

    Indirect or Index command without recalculating forcing a save

    I am trying to use either of these two functions =INDEX (PFS!$A$1:PFS!$F$1000,(ROW()/2-2)*14+4,4)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> =INDIRECT("PFS!D"&(ROW()/2-2)*14+4)<o:p></o:p> to access cells specified by my current row position...

Some videos you may like

This Week's Hot Topics

Top