formula to find the first cell up from the current cell with a checkbox ticked for Google Sheets

[fitnessman]

hi guys

is there a formula (for example in cell Q10) to seach from upwards in column A (where i have checkboxes in each cell) to find the row that has the first checkbox ticked UP from Q10 and then calculate the foluma Q10-Q(the row number that the first checkbox is ticked up from Q10)

hope that makes sense am stuck as i cant seem to find anything that will specifically seach upwards from Q10 in column A until it finds the first checkbox ticked. can it be done?

 

[Skyybot]

I don't think that is possible. It seems like it might create a circular reference. Excel does not allow circular references.

 

[fitnessman]

I don't think that is possible. It seems like it might create a circular reference. Excel does not allow circular references.

no worries i have found a workaround for now with xlookup and the -1 search mode but amstruggleing with creating a dynamic range from my cell row upto A2, will keep pluggig away

 

[Skyybot]

=XLOOKUP("YourValue",ADDRESS(ROW(),COLUMN(),,,),,,,-1)

 

[Skyybot]

=XLOOKUP("YourValue",ADDRESS(ROW(),COLUMN(),,,),,,,-1)

Sorry

=XLOOKUP("YourValue",ADDRESS(ROW(),COLUMN():A2,,,),,,,-1)

 

[fitnessman]

thanks but i get error argument must be a range?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[fitnessman]

well on this computer its actually google documents but on my work computer i have the latest excel but i have found they seem to have very similar functionality but maybe thats why im struggleing on this computer at home at the moment.

 

[Fluff]

Questions about sheets should be posted in the General Discussion & Other Applications section of the board & you should make it clear what you are using.
I have done this for you this time.

they seem to have very similar functionality

They do, but there are also a lot of differences.

 

[fitnessman]

Sorry

=XLOOKUP("YourValue",ADDRESS(ROW(),COLUMN():A2,,,),,,,-1)

i can get =address(2,1,,,)&":"&address(row(),Column(),,,) to produce an array A2:A10 however i cannot get xlookup to recognise this formula as a range i just get a blank cell no error. how can i get the address to be recognised as a range?