SOLVED

Trouble with Getting Data using Indirect from varying rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2239851%22%20slang%3D%22en-US%22%3ETrouble%20with%20Getting%20Data%20using%20Indirect%20from%20varying%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2239851%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20going%20bonkers%20trying%20to%20set%20excel-up%20as%20per%20my%20requirements.%26nbsp%3B%20I%20have%20attached%20my%20excel%20file%20here%20which%20is%20Office%20365%20version%2C%20Windows%2010.%26nbsp%3B%20My%20problems%20are%3A%3C%2FP%3E%3CP%3E1.)%20in%20the%20summary%20sheet%2C%20under%20Technical%20Indicators%2C%20each%20indicator%20should%20auto-fill%20the%20last%20result%20from%20the%20Data%20sheet%2C%20which%20it%20is%20not.%26nbsp%3B%20The%20rows%20keep%20changing%20based%20on%20the%20period%20selected%20in%20the%20Time%20Frame%20in%20the%20summary%20sheet.%26nbsp%3B%20But%20it%20is%20not%20showing.%26nbsp%3B%3C%2FP%3E%3CP%3E2.)%20I%20am%20unable%20to%20Forecast%20as%20I%20get%20an%20error%20saying%20the%20date%20is%20inconsistent%20but%20the%20dates%20are%20those%20when%20the%20Stock%20Markets%20were%20on.%3C%2FP%3E%3CP%3E3.)%20I%20want%20the%20charts%20to%20be%20plotted%20based%20on%20the%20time%20frames%20entered%20in%20the%20summary%20sheet.%3C%2FP%3E%3CP%3EThanks%20a%20million.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2239851%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2240152%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20Getting%20Data%20using%20Indirect%20from%20varying%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2240152%22%20slang%3D%22en-US%22%3Ecan%20you%20provide%20more%20details%2C%20exactly%20which%20columns%20you%20need%20to%20extract%20from%20data%20sheet%20to%20summary%2C%20%26amp%3B%20which%20columns%20you%20need%20for%20plot%2C%20can%20you%20make%20a%20small%20sample%20so%20we%20can%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2240376%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20Getting%20Data%20using%20Indirect%20from%20varying%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2240376%22%20slang%3D%22en-US%22%3EHi%20Chahine!%20Thanks%20for%20responding%20fast.%20I%20need%20to%20extract%20from%20these%20columns%20in%20the%20data%20sheet%20to%20summary%20sheet%20(their%20names%20are%20the%20same)%3A%3CBR%20%2F%3EVWAP%3CBR%20%2F%3EADX%3CBR%20%2F%3EOBV%3CBR%20%2F%3ERSI%3CBR%20%2F%3EMACD%3CBR%20%2F%3EThese%20are%20also%20the%20columns%20that%20need%20to%20be%20plotted%20automatically%20based%20on%20the%20time%20frame.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2241621%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20Getting%20Data%20using%20Indirect%20from%20varying%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2241621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1009290%22%20target%3D%22_blank%22%3E%40PowerLord%3C%2FA%3E%26nbsp%3Byour%20excel%20file%20is%20not%20working%20properly%20in%20formulas%2C%20so%20am%20not%20able%20to%20arrange%20it%2C%20maybe%20there%20are%20some%20formulas%20which%20are%20like%20loops%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202021-03-29%20055151.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F267786iE371BB9798C6A2BE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Annotation%202021-03-29%20055151.png%22%20alt%3D%22Annotation%202021-03-29%20055151.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2241764%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20Getting%20Data%20using%20Indirect%20from%20varying%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2241764%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20was%20some%20issue%20with%20links%2C%20so%20i%20turned%20it%20off%3C%2FP%3E%3CP%3Eand%20it%20worked%3C%2FP%3E%3CP%3Eso%20i%20did%20this%20dynamic%20charts%2C%20using%20sequence%20which%20uses%20ur%20first%20%26amp%3B%20last%20date%20in%20time%20frame%20%26amp%3B%20index%20%26amp%3B%20indirect%2C%20have%20a%20look%20at%20the%20sheet%20charts%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20summary%2C%20i%20used%20just%20index%2C%20%26amp%3B%20last%20date%20from%20your%20time%20frame%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am going bonkers trying to set excel-up as per my requirements.  I have attached my excel file here which is Office 365 version, Windows 10.  My problems are:

1.) in the summary sheet, under Technical Indicators, each indicator should auto-fill the last result from the Data sheet, which it is not.  The rows keep changing based on the period selected in the Time Frame in the summary sheet.  But it is not showing. 

2.) I am unable to Forecast as I get an error saying the date is inconsistent but the dates are those when the Stock Markets were on.

3.) I want the charts to be plotted based on the time frames entered in the summary sheet.

Thanks a million.

7 Replies
can you provide more details, exactly which columns you need to extract from data sheet to summary, & which columns you need for plot, can you make a small sample so we can help
Hi Chahine! Thanks for responding fast. I need to extract from these columns in the data sheet to summary sheet (their names are the same):
VWAP
ADX
OBV
RSI
MACD
These are also the columns that need to be plotted automatically based on the time frame.

@PowerLord your excel file is not working properly in formulas, so am not able to arrange it, maybe there are some formulas which are like loopsAnnotation 2021-03-29 055151.png

best response confirmed by PowerLord (Occasional Contributor)
Solution

There was some issue with links, so i turned it off

and it worked

so i did this dynamic charts, using sequence which uses ur first & last date in time frame & index & indirect, have a look at the sheet charts

 

in summary, i used just index, & last date from your time frame

Thank you very much Chahine! I will continue working and if I get stuck will post here again! Though I still am stuck on point no. 2, that is trying to get Excel to forecast, think you can help with that? THANK YOU!
Chahine,
Charts are still causing a problem. The chart plot data is different for each. For example, for OBV, the chart data is the Close Price, OBV and respective date. I entered this data many times but keeping getting a straight line for Close Price and weird pattern for OBV.
You need to modify the chart tables based on your need, i used the columns that you told me about, but many of these columns are NA maybe i dont have the correct connection to some external files
for charts i did them like it will change based on your selection