Replies

Thanks Rory, it was actually the definition of the custom view just as you say.
Once custom view is selected the workbook goes into long drawn out process of switching sheets and calculations run over and over again.
I was hoping that the settings as to which worksheet and which columns/rows/views would be set in order to try and check the steps one at a time.
Thanks for reply.
alan

That’s great Steve.
I should have realised creating the text file in code was the solution.
Many thanks for that.

Just re-read that post and, the text/cells in Excel window have just the single quotation marks around them but then have 3 sets when opened in notepad.

Thanks Guys.

Yes, the greater than portion on it’s own works fine, which is why I was getting confused.

The splitting of the fields criteria as per John’s suggestion works great and now I get all the rows when needed.

Many thanks
Alan

Thanks Andrew, form version works fine.

Thanks Steve, works well as an array formula.

Thanks Guys.
Mark, the conditional formatting works great, but I really need a value returning to be able to use that value elsewhere.
GFamily (Runcorn – just round the corner from me..almost) – that solution prompted me to try an array formula. The solution you show works great, but I unfortunately don’t have luxury of being able to add columns to the worksheet.

so far, I’ve found this to work pretty well:
given any column of numbers I can find the last entered number using the array formula:
{=OFFSET(Q1,MAX(IF(ABS(Q4:Q55)>0,ROW(Q4:Q55),0))-1,0)}

As I had negative numbers in one of the lsits I had to take an absoute view of them, then return the row number of each one in the array where greater than zero. From there I could see the maximum row number and then just use that in an offset.
My list spanned from row 4 to row 55 but I still used row 1 as the offset reference and subtracted 1 from my MAX value to get the actual row which contains the last number.

thanks for the input guys.
Alan
Warrington

Thanks Guys. Yes it’s a follow up on the December post which works brilliantly, but unfortunately the timing has been somewhat delayed owing to the UK VAT changes and incumbent work on that.
Thanks for the pointers and I’ll be throwing a bit of time at it this weekend but today is upgrade day and need to give time over to that.
Best laid plans of mice and men and all that.

Hmmm, not sure about that format though John:

as it is the intention to use the link same as a shortcut on the desktop and it’s not a file per se.

Just tried that in HTML formattted mail and the link appears but no go when selected.
Cheers
Alan

Thanks Guys. Yes it’s a follow up on the December post which works brilliantly, but unfortunately the timing has been somewhat delayed owing to the UK VAT changes and incumbent work on that.
Thanks for the pointers and I’ll be throwing a bit of time at it this weekend but today is upgrade day and need to give time over to that.
Best laid plans of mice and men and all that.

Hmmm, not sure about that format though John:

as it is the intention to use the link same as a shortcut on the desktop and it’s not a file per se.

Just tried that in HTML formattted mail and the link appears but no go when selected.
Cheers
Alan

Thanks Guys. Yes it’s a follow up on the December post which works brilliantly, but unfortunately the timing has been somewhat delayed owing to the UK VAT changes and incumbent work on that.
Thanks for the pointers and I’ll be throwing a bit of time at it this weekend but today is upgrade day and need to give time over to that.
Best laid plans of mice and men and all that.

Hmmm, not sure about that format though John:

as it is the intention to use the link same as a shortcut on the desktop and it’s not a file per se.

Just tried that in HTML formattted mail and the link appears but no go when selected.
Cheers
Alan

Thanks Guys. Yes it’s a follow up on the December post which works brilliantly, but unfortunately the timing has been somewhat delayed owing to the UK VAT changes and incumbent work on that.
Thanks for the pointers and I’ll be throwing a bit of time at it this weekend but today is upgrade day and need to give time over to that.
Best laid plans of mice and men and all that.

Hmmm, not sure about that format though John:

as it is the intention to use the link same as a shortcut on the desktop and it’s not a file per se.

Just tried that in HTML formattted mail and the link appears but no go when selected.
Cheers
Alan

Thanks Guys. Yes it’s a follow up on the December post which works brilliantly, but unfortunately the timing has been somewhat delayed owing to the UK VAT changes and incumbent work on that.
Thanks for the pointers and I’ll be throwing a bit of time at it this weekend but today is upgrade day and need to give time over to that.
Best laid plans of mice and men and all that.

Hmmm, not sure about that format though John:

as it is the intention to use the link same as a shortcut on the desktop and it’s not a file per se.

Just tried that in HTML formattted mail and the link appears but no go when selected.
Cheers
Alan

Thanks Guys. Yes it’s a follow up on the December post which works brilliantly, but unfortunately the timing has been somewhat delayed owing to the UK VAT changes and incumbent work on that.
Thanks for the pointers and I’ll be throwing a bit of time at it this weekend but today is upgrade day and need to give time over to that.
Best laid plans of mice and men and all that.

Hmmm, not sure about that format though John:

as it is the intention to use the link same as a shortcut on the desktop and it’s not a file per se.

Just tried that in HTML formattted mail and the link appears but no go when selected.
Cheers
Alan

Thanks Guys. Yes it’s a follow up on the December post which works brilliantly, but unfortunately the timing has been somewhat delayed owing to the UK VAT changes and incumbent work on that.
Thanks for the pointers and I’ll be throwing a bit of time at it this weekend but today is upgrade day and need to give time over to that.
Best laid plans of mice and men and all that.

Hmmm, not sure about that format though John:

as it is the intention to use the link same as a shortcut on the desktop and it’s not a file per se.

Just tried that in HTML formattted mail and the link appears but no go when selected.
Cheers
Alan

Thanks Guys. Yes it’s a follow up on the December post which works brilliantly, but unfortunately the timing has been somewhat delayed owing to the UK VAT changes and incumbent work on that.
Thanks for the pointers and I’ll be throwing a bit of time at it this weekend but today is upgrade day and need to give time over to that.
Best laid plans of mice and men and all that.

Hmmm, not sure about that format though John:

as it is the intention to use the link same as a shortcut on the desktop and it’s not a file per se.

Just tried that in HTML formattted mail and the link appears but no go when selected.
Cheers
Alan