Posts by mknov

I solved this myself, but with a different workaround. Although I have to admit I don;t understand why it works.

The cells I was talking about were changing as a result of a lookup that was referencing values that changed when a refresh was run (from a web query). In case anyone's interested, this is what I used...

At the end of the refresh sub, I call this one:

'Inserts the date the cells were last updated (the date the refresh ran)

Sub dateconsol()

Range("h2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("h2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub

Hi Iridium,

Thanks. This is cool, but only works if I apply the change directly to the cell. If the value of the cell has changed as a result of me changing another cell which is referenced by that cell's formula, it ignores it.

Does that make sense or am I rambling?!

Argh!! I know I used to know this...

What is the funciton / code to insert into one cell the date and time another cell was last modified.

It needs to work even if the change wasn't applied directly to the cell (i.e. the cell's value changed as a result of a lookup)

Thanks

MKNov

Hi,

I have a spreadsheet that needs to be updated daily with exchange rates (gbp/usd and gbp/eur). I once heard there is a way to link to the web so that the cells update with the daily rates every time you open them. Being lazy, this appeals to me. Does anyone know if it is possible?

Thanks

MKNov

Fantastic. Thanks v much Derk!

Hi,

I have an excel worksheet formatted as an order form template that is completed by the salespeople at my company when they make a sale.

To make sure they give the order team all the information they need, I would like to prevent the salesperson from printing the form unless they have filled in all compulsory fields.

e.g. if A1, B3, C6 and G8 are not blank,
allow print <this is where I'm stuck&gt;

else msgbox 'you haven't filled in all relevant fields'

Have been away from Excel for too long! Can someone point me in the right direction?

Thanks

MKNov

I mean what if I want both the EX and the DB formula in the same IF?

Sorry, feel like I'm being a nuisance now.

Cool!!

But when I try to add in the bit for the DB codes (IF LEFT(B21,2)= "DB", LEFT(B21,4), it tells me I've got too many arguments?

But I would need the LEFT etc formula to be the same all the time, as I would have to hide this on the order form.

It would need to not matter what the saleperson types in. e.g. whether they type a code that begins with SP or one of the EX codes, the lookup would still need to work.

Which is why I didn't think I'd get around it with formulae?

Ah, nearly!

Have made some amendment and comments...

Quote

Originally posted by royUK
I think we need to see these rules.

Here they are...

Well how is the category code to be determined? Is there some rule that can be used? Without a rule or a lookup table, I don't see how Excel could get the category. [/quote]

Yes there is a set of rules to determine which category a product falls into. It is usually dependant on the AB section of the code. In some instances it is dependant on both the AB section on the code and the final digit.

Quote

Originally posted by royUK
It's still not clear to me where you are getting your various codes from. What determines the individual code the you enter manually?

Each code relates to a product. When a new product is created, a code is assigned to it in an Access database. However there are exceptions - not all products are stored in this database and therefore some codes are created manually.

The code is made up of company dividion (AB), division department (CD) and then a sequential four digit number 1234.

Quote

Originally posted by royUK
I'm not really sure what you mean.Have you got ABCD in one cell and the number in the next cell?If so are you needing to use FIND and OFFSET?

Hi Roy,

No I have got ABCD1234 (or whatever) all in one cell. The category that the product falls into (and therefore the corresponding category code) is dependant (with a couple of exceptions) on the AB section of the product code. am I making sense?!

MKNov

Quote

Originally posted by Derk
The easiest way woyuld be to have a table on a (hidden) sheet that has all of the possible ABCD combinations in the first column and the corresponding category codes in the second column. Then extract the ABCD part of the user input with Left(userinput,4) and look up the category with the Vlookup function. You could do this right on a spreadsheet or in your macro if other work was being done.

Hi Derk,

The problem with that solution is that every day there are more and more ABCD codes, which I can have all on a spreadsheet by importing them from their source using Query, but the category code does not exist in that, or any other, table.

Thanks anyway

Hello All,

I seem to have had a memory blackout.

I have a wide range of item numbers e.g. ABCD1234. Each has a corresponding item category, e.g. 3710.

The category is dependant on what the ABCD part of the item code starts with.

How do I write a macro in VBA that will automatically add to a spreadsheet the category code based on the manually entered item code.

I was thinking along the lines of If, Else if, but there are thousands of item codes.

What is the best way to go about this?

How do I say "begins with" in VBA?

Any help gratefull received

MKNov

Quote

Originally posted by XL Dave
Got any jobs?

Joe's £5K per month sounds good to me.

Go on giz a job! :biggrin:

Hi XL Dave,

Yes, Joe is a jammy sod isn't he? I often think I am in the wrong part of the company myself.

If I ever get hire (and fire) power you'll be first on my list! :spin:

Quote

Originally posted by Imbuzi
Mknov,

Just a few questions regarding your sales tracker system.

Can you get a downloadable file e.g. a txt file?

on the tracker system do each of the sales people have an ID?

How big is the list of products are we talking about?

I may have a few ideas if you can get the data in a downloadable format.

Graeme

Display More

Hi Imbuzi,

The sales tracker is just an Excel workbook with one sheet set up to act like a form (where we enter the sales) and another sheet with all the sales on. And a few other sheets running various macros to manipulate the data.

No they don't have an ID, but they could have - I haven't built them in but they do exist elsewhere.

By the list of products do you mean how many lines per person? (1-40 ish) Or how many sales in a month? (few hundred lines)

Any help gratefully received!

Bother. I was starting to get that impression.

OK well thanks for your help. back to the drawing board I guess.

Cheers

MKNov

Ah. I see your line of thinking now.

But this would be very manual.

I was thinking more along the lines of something that would take my list of sales, split it by person and then depending on the team the person belonged to, their target and the types of product they had sold, apply a set of rules and come up with a commission figure.

I think I may be asking far too much on this one.