indirect()

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...