Replacing INDEX(INDIRECT(DYNAMICCELL)

[xtrato]

Hello all, im new to posting but certainly not to the forums!

IN any case , i have a quite a pickle - so a run retail stores with a lot of raw data , i have created a sheet to track that data on a daily basis - and i've created "Monthly Workbooks"

In each workbook i have sheets from 1-30 or 31 to represent the day of the month ! - as to happens to all - this sheet grew and now its practically running all raw data ;

so i have a different sheet where i keep track of SPECIFIC targeted data , this data is presented and stored as shown the picture

each cell you see here References the Sheetnumber Say 1,2,3,4 etc , and this is used throughout other sheets, the formula IN C5 is:

=IFERROR(INDEX(INDIRECT("'"&C$3&"'!NX1:QD300"),XMATCH($B5,INDIRECT("'"&C$3&"'!NY1:NY300")),XMATCH($B$2,INDIRECT("'"&C$3&"'!NX1:QD1"))),0)

IN D5 is:

=IFERROR(INDEX(INDIRECT("'"&D$3&"'!NX1:QD300"),XMATCH($B5,INDIRECT("'"&D$3&"'!NY1:NY300")),XMATCH($B$2,INDIRECT("'"&D$3&"'!NX1:QD1"))),0)

and so on going left and right - the references are DYNAMIC - it is like that throughout the entire workbook - how can i use VBA to replace such monstrosity for i have thousands of these formals and my workbook is now a slowpoke.

Thank you all!!

I also Posted this on EXCELFORUMS ; but no help yet: hopefully i can get direction.

https://www.excelforum.com/excel-programming-vba-macros/1410547-replacing-index-indirect-dynamiccell.html

 

[xtrato]

Daily_EXAMPLE.xlsm
	B	C	D	E	F
1	date	1/1/2023	1/2/2023	1/3/2023	1/4/2023
2	NetAct	1	2	3	4
3	acctotal	1	2	3	4
4	1	TRUE	FALSE	FALSE	FALSE
5	LPMA340	2	0	0	0
6	LPMA506	0	0	0	0
7	LPNJ852	5	0	0	0
8	LPNY369	1	0	0	0
9	LPMA376	0	0	0	0
Boxes

Cell Formulas
Range	Formula
B4	B4	=COUNTIF(C4:AH4,TRUE)
C4:F4	C4	=ISREF(INDIRECT("'"&C3&"'!A1"))
C5:E5,C7:F9,F5:F6,C6:D6	C5	=IFERROR(INDEX(INDIRECT("'"&C$3&"'!NX1:QD300"),XMATCH($B5,INDIRECT("'"&C$3&"'!NY1:NY300")),XMATCH($B$2,INDIRECT("'"&C$3&"'!NX1:QD1"))),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:AG10,C12:AG151,D11:AG11	Cell Value	contains "HIt"	text	NO
C3:AI3	Cell Value	contains "HIt"	text	NO
C2:BN2	Cell Value	contains "HIt"	text	NO
B2	Cell	does not contain a blank value	text	NO
B2	Cell	does not contain a blank value	text	NO
B1,CW9,B3,B5:B76,B4:AG4,AH5:AH151	Cell Value	contains "HIt"	text	NO

 

[xtrato]

Hi , good afternoon , does anyone have any tips on this matter?

Or referencing an Dynamic cell without using INDIRECT?

 

[JEC]

Could be done with VBA ofcourse. But we need your workbook.

 

[JEC]

Saw your file on Excelforum, I gave a solution there

 

[Fluff]

Please post your solution here as well. Thanks

 

[JEC]

Sure, no problem ofcourse!

Sub jec()
 Dim xp, ar, a, j As Long, jj As Long, jjj As Long
 xp = Range("B3:AG" & Range("B" & Rows.Count).End(xlUp).Row)
 
 For j = 2 To UBound(xp, 2)
   If Evaluate("isref('" & xp(1, j) & "'!A1)") Then
      ar = Sheets(xp(1, j)).Range("NX1:QD300")
      For jj = 3 To UBound(xp)
        For jjj = 1 To UBound(ar)
          If xp(jj, 1) = ar(jjj, 2) Then
            a = Application.Match(Range("B2"), Application.Index(ar, 1, 0), 0)
            If IsNumeric(a) Then
              xp(jj, j) = ar(jjj, a)
              Exit For
            End If
          End If
        Next
      Next
   End If
 Next
 
 Range("B3:AG" & Range("B" & Rows.Count).End(xlUp).Row) = xp
End Sub