Re: List Validation: displaying only those values from col A that have B set to value
Is this what you wanted? No array and no helpers. Will only work if the bands are grouped together (all beatles together, all rolling stones together, etc). See attached.
In Data Validation put: =OFFSET(Blad1!$A$1,MATCH(Blad2!$B$1,Blad1!$B:$B,0)-1,,COUNTIF(Blad1!$B:$B,Blad2!$B$1))
Re: How to ignore columns using offset formula? (Moving reference cell)
So is your offset going to the right or the left? You said it counts back, which to me reads as in going left. But then you mention reaching columns AC which would be going right from R5.
Does C21 hold negative or positive value?
If you are trying to go right and skip columns AC:AH then you can use this formula...
=AVERAGE(OFFSET(R5,1,IF($C$21 > 9,$C$21+7,$C$21+1),1,3))
If your offset needs to offset to the left, then you will never reach AC.
Re: Adding additional fomulas to existing links from multiple sources
If everything is going to be multiplied by cell H5 then you can...
Go to Home > Find and Select > Replace > ReplaceTab > Expand options
Find What: ='G:\
Replace with: =$H5*'G:\
Replace All
Re: Dynamic Named Ranges with Intermittent Blanks
Try this. It will work.
=OFFSET(Measurements!$C$10,0,0,LOOKUP(2,1/(Measurements!$C$10:$C$784<>""),ROW(C:C)))
