indirect array

I am building a workbook that will build a BOM off of several different selection variables (21) from the user. the user output is a list of table names that correspond to the tables i want to vstack(). this is what i have so far. The aim is for this workbook to contain no macros. i have the...

Hi, i have this table (could not upload mini sheet) on multible sheets week is merged cells but here i have just wrote 1/2 in all cells Week: 1 1 1 1 1 2 2 2 2 2 user aa bb cc dd ee aa bb cc dd ee task 1 1 4 2 3 2 2 8 4 6 9 task 2 3 5 9 3 2 7 4 2 8 3 sum...

How can I use text in cell for formula with array. For example in row 2, the formula includes an array. In row 3 I want to use the contents of cell R3 to indicate the array. But this doesn't work. I tried INDIRECT.

Hello, I'm trying to use some strings to reference arrays so that I can use one cell instead of four, to visualize it better follows the example: A B C D E F G H I 1 =SUM(Sheet2!A1:D1) =SUM(Sheet2!A2:D2) =SUM(Sheet2!A3:D3) =SUM(Sheet2!A4:D4) =Sheet2!A1:A4 =Sheet2!B1:B4 =Sheet2!C1:C4...

Hi, I am attempting to use two countif conditions across multiple sheets (which I have named "Tabs" in name manager) to count how many times a job is raised for an item with a certain reference number in a particular year of its ownership. I have used a no. of forum posts to get me to where I...

All, Please see below and let me know what you think. Right now my formula works and is looping back the correct data based on A2. However, what I want to do is turn that A2 into an array of cells (i.e. &$A$2:$A$100&), so when a new project is added to that column A (say it is on A3) it will...

Hi all, I have an excel file with multiple rows and columns used for planning jobs. The columns contain job descriptions, job numbers, Worker names etc. Few jobs require 2 people to complete and have 2 names on them ex: Luke and Mike. Out of all the jobs i want to extract the jobs for specific...

Hi everyone, I am stuck trying to figure out some formula in Excel. I have a column of some descriptions of a transaction and I need to aasign to them names (for example, to whom they belong or just for the purpose of making sense out of them). (Table 1) By looking at them, I found some...

Hi friends I have the following data with me: 31-May-2019 30-June-2019 31-Jul-2019 ........ ........ 31-Oct-2023 While I would need these dates as values (for my other formulas in sheet), I want to "display" Quarter and Financial year number against selected dates on the Dashboard. For e.g...

Hi everyone and thanks in advance I have a workbook with 382 sheets. 381 of the sheets are all the same format I need to VLOOKUP from A2 in the 1st sheet to find that value in cells K7:L56 of all the other 381 sheets returning column L I've used...

I have a column of data with varying length and I use the formula below to return a value. Is there a way to have excel determine the last cell in the column without using VBA? My assumption (I've tried a few times) is to use INDIRECT, but it keeps returning a numeric value (column is names)...

I'm trying to find the Largest number (in column A) across a number of worksheets, but I need the names of the worksheets to be dynamic. I can do this with a single worksheet, but using a colon separator within the worksheets seems to cause the formula to have a #REF ! error. I have the names...

Hi, I have a piece of code below that works a treat when exporting tabs to a new file as values only. However I ran into some trouble today when i changed one of the columns to have indirect formulas and now for some reason this code does not like it, it just copies them all across as blank...

Hi All, This is my first post. I have this array formula {=INDIRECT(Category)} I am trying to fetch the list of data from two different tables into B5:B25 as the number of data may increase in time. Here Category is a name of a table. In this Table there are two Categories, 1. Student 2...

I am new to arrays in excel and having a bit of trouble. I have a worksheet "Raw Data" with a range which references the worksheet names in the workbook. I want to sum up all the values from the worksheets which are listed in the Raw Data range. I am using an array Indirect function and...

Hello, I have got a Excel Spreadsheet which upon opening runs a macro which opens all .tsv files in a folder and creates them as Worksheets in Excel with the same name as the file. The name of the sheets are based on Dates and will be changing regularly, once it's over 8 weeks old it will be...