How to define indirect range for INDEX

**hansge** · 01-30-2022, 04:44 AM

I've many years with Excel but there is allways someting new.

I'm trying to use INDEX for a diagram.
to define the matrix for the INE

(Sorry German Excel)
On page Daten I use the name manager to define:
LicenseTableAvailable -> =INDEX(Daten!$B$2:$Q$1124;0;VERGLEICH(Diagramm!$B$2;Daten!$B$1:$Q$1;0))
LicenseTableCurrentUsage -> =INDEX(INDIREKT(Diagramm!IG1);0;VERGLEICH(Diagramm!$B$3;Daten!$B$1:$Q$1;0))

And in the diagram I use
=Daten!LicenseTableAvailable
=Daten!LicenseTableCurrentUsage

A fix matrix works (Daten!LicenseTableAvailable), but it does not work to get the matrix from a cell (Diagramm!G2)
What is the trick?

**Glenn Kennedy** · 01-30-2022, 04:48 AM

I think it would be a huge help if we could see a sample file!

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

**hansge** · 01-30-2022, 05:08 AM

I uploaded the example spreadsheet (don't know where i can see it)

**BMV** · 01-30-2022, 06:02 AM

Diagramm!IG1 - there is no data

**hansge** · 01-30-2022, 06:31 AM

If you lokk into the name manager, the cell is corrected to G2

**BMV** · 01-30-2022, 07:34 AM

If you select a cell and look into the name manager, select another cell and look into again. The references will be changed also. Use absolutу reference with $ in stand of relative.

INDIRECT - it is better to replace to INDEX():INDEX()

Formula:

Please Login or Register  to view this content.

**hansge** · 01-30-2022, 08:57 AM

Hello BMV, thank you very much for the help. I think I understand the principle.
In this case I don't need the values of the A:A column but the adress of the cells for the matrix of the diagram

**Glenn Kennedy** · 01-30-2022, 09:05 AM

I'm still a bit confused. What do you want to plot as the X axis and as the Y axis?

**hansge** · 01-30-2022, 09:22 AM

The diagram is on the second page. X axis is the Date:Time, Y axis is the number under the license value (B ... Q) preselected by Diagramm!B2 and B3

**hansge** · 01-31-2022, 10:59 AM

Спасибо.
Hello BMV, thank you for your help.
As I need the Index as input for a diagram, I need the cell adresses instead of only the values.
I tried with
ADRESSE(VERGLEICH(INDEX(Daten!$A:$A;VERGLEICH(Diagramm!$E$2;Daten!$A:$A));Daten!$A:$A);1)

=INDEX(ADRESSE(VERGLEICH(INDEX(Daten!$A:$A;VERGLEICH(Diagramm!$E$2;Daten!$A:$A));Daten!$A:$A);1):ADRESSE(VERGLEICH(INDEX(Daten!$A:$A;VERGLEICH(Diagramm!$E$3;Daten!$A:$A));Daten!$A:$A);1);0;VERGLEICH(Diagramm!$B$3;Daten!$B$1:$Q$1;0))

But that does not seem to work. Any tips?

**hansge** · 02-01-2022, 08:54 AM

OK did find a way to get the correct cell adresses for the diagram to be dynamic

How to define indirect range for INDEX

LinkBack

Thread Tools

Rate This Thread

Display

How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Re: How to define indirect range for INDEX

Thread Information

Users Browsing this Thread

Similar Threads

Try define indirect range for INDEX

[SOLVED] How to define data range index from number in another cell?

[SOLVED] is it posible to define data range of a chart with a formula? (indirect for example)

Index formula calling define name with non-adjacent range

[SOLVED] Nest mutiple indirect functions to define a range.

Define chart range using indirect reference

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions