Indirect Formula Reference Another Sheet. So, in cell b2, the function was =indirect (a2&!”!b2”), which refers to cell red!b2. Show activity on this post.

However, it requires special handling if the worksheet name contains spaces or a date. The worksheet name has an apostrophe in front of it and an apostrophe and exclamation mark after it. In this case, you can use the indirect() function, which is available in both excel […]

### You Can Do Sheets (Sheet1).Range (B2).Formula = =Indirect ( & Char (34) & ' & Char (34) & Sheets (Sheet1).Range.

You just have to specify the workbook's name is addition to the sheet name and cell address. When we clicked the fill handle, the cell reference before the ‘&’ in each cell got updated to the next sheet name. Domenic's suggestion to use a choose function formula to directly return a range is a good one;

### This Step By Step Tutorial Will Assist All Levels Of Excel Users In Using The Indirect Function To Reference Another Sheet.

With the use of ampersand (&), the other necessary symbols have been used to refer to a particular range of cells from another worksheet. = sum(indirect(' & b6 & '! & c6 )) which returns the sum of the named range data on sheet1. If a worksheet has a space in the name, you must build the reference to the worksheet by using apostrophes around the worksheet name, followed by an exclamation point and then the cell address.

### However, It Requires Special Handling If The Worksheet Name Contains Spaces Or A Date.

In other sheets, i could simple have a helper column and use the indirect function such as: Function, which returns an address in text format. Show activity on this post.

### In This Example, The Tables Are All On One Sheet To Show The Formula More Easily.

However the formulas would become quite lengthy if they need to reference all 75 sheets. To reference a named range on another sheet, you can use the indirect function with the required sheet syntax. The index value in row 2 tells the function which element of the list to use, i.e.

### Input A String “Sales “ In The Cell G45.

For example if you have data in the same format split over multiple worksheets and you want to select data from different sheets dynamically. These tables can be on any sheet in the workbook and provide a cleaner method than referencing sheet names like the previous example. In this example, we have sheet names in column b, so we join the sheet name to the cell reference a1 using concatenation:

Categories: live worksheet