INDIRECT function to reference cell ranges of other worksheets

0
votes

I am struggling to deduce a way to make dynamic INDIRECT references to cell ranges on other worksheets. Would appreciate any suggestions, details are:

The workbook includes 4 worksheets (Product1, Product2, Product3, Warehouses). The Warehouses sheet contains the following formula to populate an inventory list for each warehouse from the three product worksheets (derived from http://exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/). This is the formula in cell B3:

=IFERROR(INDEX(INDIRECT(B$2&"!B$3:B$400"),SMALL(IF(INDIRECT(B$2&"!$C$3:$C$400")=$B$1,ROW(INDIRECT(B$2&"!B$3:B$400"))-ROW(INDIRECT(B$2&"!B$3"))+1),ROWS(Product1!$B$3:$B3))),"")

Where:

Warehouses-->$B$1 = Warehouse1 or Warehouse2

Warehouses-->B2, C2, D2 = Column headers for Product1, Product2, Product3

Product Sheets-->Column B = Serial #

Product Sheets-->Column C = Location (Warehouse1, Warehouse2)

Currently, I have to amend the last part of the formula for each row: ROWS(Product1!$B$3:$B3), ROWS(Product2!$B$3:$B3) , ROWS(Product3!$B$3:$B3).I am trying to dynamically link it to the column header like the other parts of the code (e.g. ROW(INDIRECT(B$2&"!B$3:B$400")). I'm stuck though because the range $B3 has to change with each row, whereas the others are static and are fine enclosed in the quotations.

This effort is important because I want less capable users to be able to copy the formula to new columns without having to amend it. Appreciate any thoughts on this!

2
excelexcel-indirect

2 Answers

1
votes

There are a number of ways to introduce an incrementing number into the INDIRECT function's string concatenation. I prefer a simple ROW function like ROW(1:1) which returns 1, 2, 3, ... when filled down. In your case,k yu would start at ROW(3:3).

... -ROW(INDIRECT(B$2&"!B"&ROW(3:3)))+1),
..., ROWS(INDIRECT(B$2&"!B3:B"&ROW(3:3))),"")

Just a reminder; leaving the absolute $ designations on the cell addresses that are described with text may be useful as a visual reminder as to what may advance to the next (relative) or not (absolute) but the text representing the row or column is never going to actually change so they are at least a bit superfluous.

0
votes

You can modify your INDIRECT() formula to take into account the row number of current cell, for example:

=INDIRECT(B$2&"!B$"&ROW()&":B$"&(ROW()+397))

Alternatively, you could use OFFSET() function to shift your reference with each row:

=OFFSET(INDIRECT(B$2&"!B$3:B$400"),ROW()-3,0)