PDA

View Full Version : [SOLVED] Editing the contents of a Match Index Cell through VBA



heroofgoodwi
12-18-2017, 03:34 AM
Hey all,

I have been looking for a method to utilise VBA to allow a user to select a new title from a list of sheets from a drop down box and then click a button which would run a piece of VBA which would change a series of Match Index cells to represent the new selection.

Currently the cells contain something like this.



=IFERROR(INDEX('Full £ Template 10_2017'!E6:BA6,MATCH(Report!$C$4,'Full £ Template 10_2017'!$E$3:$BA$3,0)),0)

What I would like the end result to be is for the user to be able to change the date in the formula to match the one on the new template. For example moving from:


'Full £ Template 10_2017'

to


'Full £ Template 11_2017'

If anyone has any suggestions it would be greatly appreciated.

p45cal
12-18-2017, 04:54 AM
You can probably use INDIRECT in the formula.
Does the drop down contain the exact names of the sheets?
VBA probably won't be necessary.

Something like:
=INDEX(INDIRECT("'" & C4 & "'!E6:BA6"),MATCH(Report!$C$4,INDIRECT("'" & C4 & "'!$E$3:$BA$3"),0))
where C4 is the dropdown cell (on the same sheet as the formula in this instance) containing exactly the same text as the sheet names.

heroofgoodwi
12-18-2017, 07:50 AM
That is a rather elegant solution p45cal.

Thank you