Dynamic Chart without using Macros

[BHunter]

I am trying to make a chart that will change based on the active cell's row.

This workbook will be used exclusively on iPads for field work. As far as I know the ios version of excel does not support macros so I will have to make this chart without using macros.

I've figured out how to do it using a macro
Dynamic chart based on cell selection
This method uses a macro to determine determine the selected cells row and column which then populates a 'helper' / hidden column with the data from the active cells column. The helper column is then used as the data for the chart.

Does anyone know how to accomplish this without using VBA? (see ios comment above)

 

[teylyn]

Hello,

I wrote the blog post you are referring to. The method described uses the Worksheet_SelectionChange event. This is a VBA event that monitors if the cell selection changes and can be used to kick of actions in such a case.

Currently, Excel has no other means than VBA to determine the position (row/column) of the currently selected cell. If you need an approach that does not use VBA and will work on Excel for iPad, then you may need to look into formula solutions, but there will need to be an element of human interaction, for example a cell where the user specifies a value. This could be the row number or a product code or a date or some such. Based on that user input, formulas can be built to populate the range of cells that feed the chart.

If you need input on how to do that, please pipe up.

cheers,

 

[BHunter]

Thanks for the reply and original blog post teylyn.

Am I safe to assume there's no way of extracting anything from the active cell without using VBA? If I could get the active cell's value that would work alright for me.

If I have to use the user input to populate the range, I'll give it my college best before bugging you for help. The best way to learn is to do, right. I'm assuming I can make something work with a vlookup or hlookup.

Thanks again