This should be easy but haven't done much VB for a while.

I've got a userform that I'm using for data input. When I enter a line I want the cursor to go back to the first input box.

Assuming it's something like taborder but can't work it out.

Ta

Something like

MyTextBox.SetFocus

Cheers. Will have a look at it.

Often i have to select many individual row lines out of a population. I can't always use vlookup because the descriptions aren't exact

Can i
A: vlookup with a contains text, or
B: lock the control key so i can conditionally format and select highlights for copy paste with out holding it down?

why do you need to do A?

Can you add a column to your data that has the find() formula in it? so isnumber(find(text,a1,1))*1 will give you 1s where your text is present and 0s where not. "text" can be a cell reference too. Then you just look for 1s or filter on 1s or whatever.

Obvious but brilliant, thank you much

You're welcome!

i have a question from a while back, from memory this was it but maybe the details are slightly wrong.

i obviously know how to do a vlookup.

but can i do a vlookup that returns the sum to that point?



like this?

heh no doubt this is embarrassingly simple now i think about it. after a quick google probably will involve the 'address' function yea?

Are your dates always ordered? Can you just use SUMIFS conditioned on the date being <= the date you input?

Could you just add a column that does the sum-to-that-point and lookup that column?

yeah the dates are always ordered.

adding the column isn't practical really. it's 30 external spreadsheets and i don't need this enough to get into that hassle.

also i don't want to sum the entire column, it will basically be 'sum between these two dates' (obv having that sum column would help me do one minus the other, but yea adding the column can't happen really)

sumifs hm i could i guess. i'll try that. these spreadsheets are all big and just doing the vlookups is pretty bad...this will probably not be feasible.

Can you redesign the process to not involve 30 external spreadsheets :-p

this works:



(which is of course what you suggested, will try on the big files soon)

Why are you not using the SUMIFS function?
I think Sumproduct is a lot slower.

also isn't index-match superior to vlookup in pretty much every way?

Every way except complexity. Need to think about who might need to use your tool

.

Is there a way to stop a pivot table changing the cell background colour? Currently when the table refreshes and is a different size the cells that it no longer covers revert to blank.

And is there a way to filter a pivot table to a specific time? e.g. between 15:34 and 15:38?

I'm writing a vba driven spreadsheet for work. It basically helps generate quotes. Some of the info in there is commercially sensitive.

So my question is what can I do to stop prying eyes getting to that info?

The important sheet is xlveryhidden and when I'm finished with everything else I'll lock down as much as I can through excel and password protect the macros (think I can do that, been a while).

I think I've got some code to stop the user from breaking while macros are running (code 18 error handling).

What else can anyone think of?

End users are likely to be non excel savvy but obviously I can't control where it goes from there. How safe can I make my data?

Thanks all.

Can't think of anything at the moment but send me the unlocked spreadsheet and I'll play around with it and see what I can come up with. Also let me know who would be commercially interested in the data and how much it would be worth to them so I know what I'm dealing with. Thanks.

Has anyone ever noticed how now() doesn't work correctly? What is the logic of this? The interesting part is that the same feature / bug exists in Google Docs.

Say you wanted to timestamp an even automatically. The logical person would just do something like =if(cell != '', now()) then copy down.

You enter on A2 and B2 shows the current time.

You then enter on A3 five minutes later. It is working as expected in that B3 shows the current time except there is one problem: B2 shows the same value as B3, so you lost your timestamp.

Highly counter-intuitive. My coworker ended up searching online for a solution to this issue and ended up having to copy / paste a macro to use.

seems like it's working correctly to me.. are you expecting the B2 cell to never recalculate? what's wrong with just using the macro you found? or if you need to avoid macros google is showing that you can use a circular formula, but that seems like a ****ty method.

Macros tend to slow things down quickly, which can cause system crashes and lost data. Then again our sheets become very large very fast.

The point is that now() doesn't work like one would expect it to. There is no reason for the time to inherit all the way down.

I'm with Exitonly. Formulas recalculate. It may not work how you want it to work, but it's working how you should expect it to work.