Excel Name Trick #7: INDIRECT function, Names and LOOKUP

I've got a workbook that utilizes a summary sheet that draws a lot of data from about 40 other sheets where the calculations are performed.

If I name my sheets a standard #1, #2, #3, etc. I have no problems. It would be helpful if I could name the sheets #1 (Allowances), #2 (Site), #3 (Concrete), etc. but when I do this, the INDIRECT formula loses track of what sheet I'm referring to.

Is there a way to use this extra identifier on my sheet name and still use the INDIRECT function?

Thanks for your help!

I am familiar with the use of the INDIRECT function to retrieve data from different sheets in a workbook. However, is there a simple way of obtaining a list of all sheet names in a workbook (I have about 50) rather than typing them into the sheet individually?

I can see them in the workbook properties but can't copy and paste them.

Any help appreciated.

Hello,

I want to create a Chart that does not directly reference cell-ranges (i.e.
A1:A6),
but excel-names that make the reference sheet-independent.
My aim is to be able to copy one chart to other worksheets, which have their
dataareas at the same places like the source-sheet.

Problem: This works fine in cells but not in charts
Excel name definition:
=INDIRECT("R3C2";0):INDIRECT("R3C5";0)

Any idea about this?

Thanks in advance,
Holger.

Hello,

I want to create a Chart that does not directly reference cell-ranges (i.e.
A1:A6),
but excel-names that make the reference sheet-independent.
My aim is to be able to copy one chart to other worksheets, which have their
dataareas at the same places like the source-sheet.

Problem: This works fine in cells but not in charts
Excel name definition:
=INDIRECT("R3C2";0):INDIRECT("R3C5";0)

Any idea about this?

Thanks in advance,
Holger.

Hi,
I have this formula in cell D2 of worksheet,where the function will return a 1 if
a holiday is found in the range of dates and a 0 if the holiday is not found.But in the range though there are no holidays,still the formula is '1' in cell D2.

dear all

need to learn about indirect function in excel

appreciate if you can give an example on how to use it and the purpose of using.

or if you can send me a link with example to study it.

thanks in advance.

Hi All,
I have a main spreadsheet that contains a column listing cell references that appear on a worksheet in a 2nd spreadsheets. So the columns of data appears with the following
A1 = B55
A2 = B66
A3 = B44
I have written VBA code to pull the cell references from cells A1 to A3 in the main spreadsheet. I am then trying to write code that pulls the value in the 2nd spreadsheet based on the reference text string as pulled from the main spreadsheet. So I want to pull in the value of cell B55 in the second spreadsheet. I know this is easy to do in Excel using the INDIRECT worksheet function but I am not sure how to do it in Excel VBA.

Dim Cell Value as string
Dim Actual_Value_Second_File as String
CellValue= Sheets("Main").Range("A1")).Value
Actual_Value_Second_File = Sheets("Second").Range(CellValue).Value

Where A1 = B55 in the Main 1st spreadsheet
and I am tryiing to find the value in the 2nd spreadsheet in Cell B55 on the set Second

Any suggestons? I greatly appreciate any help.

Hello Excel Experts,

I am trying to use the indirect function in a table reference to return the value at the intersection of the row and column, like so:
=Table5[#This Row] Table5[ indirect(C7) ]

However despite many variations or attempts with the match formula, this isn't working. The lovely excel popup box with "the formula you typed contains an error" appears every time. It then returns to highlight the second instance of "Table 5" in the formula.

I need get this result in numerous situations in my workbook and I am hoping someone out there has an alternative solution.

You can see an example of the table below:
Select Scenario: Season
State Fruit Season
#VALUE!
ACT Apple Winter
NSW
NSW Pear Spring
NT
NT Orange Summer
SA
SA Banana Autumn
QLD
QLD - -
TAS
TAS - -
VIC
VIC - -
WA
WA - -
"Season" is a data validation list that includes state, fruit and season. When this value is changed I want the list of values below it to update according to the values in table 5.

The formula works if i specify the column, but then as you can see, when the scenario changes, the values don't.

Your thoughts/ideas are appreciated!

Roxanne

When you are making a formula say

=A5 + A6+A7

when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.

I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.

I can't figure it out!

Does anyone know what this is?

Please help!!

Thanks,
Michael