Posts by simonwar

Re: Data Summary From Key Field in Column to New Sheet

OK, much appreciated I will manipulate my source data first - this is great though I am very happy with the result.

Re: Data Summary From Key Field in Column to New Sheet

Just a quick check I have to change this line because my date is in Column M and Column O and is format 17/10/2011 18:28:23

.Columns("b:c").NumberFormat = "hh:mm"

to

.Columns("m", "o").NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"

But I get a script error - "subscipt out of range" - any guidance appreciated.

Re: Data Summary From Key Field in Column to New Sheet

Wow, that is simply perfect, and will save me hours.

Re: Data Summary From Key Field in Column to New Sheet

Any guidance would be appreciated?

Hi Ozgrid,

I have data like this:

A B C
1 INC0123 00:00 00:12
2 INC0123 00:13 00:27
3 INC0123 00:28 00:42
4 INC0127 00:00 00:20
5 INC0127 00:21 00:48
6 INC0128 00:00 00:22
etc

and what I would like is a summary sheet that looks like this

A B C
1 INC0123 00:00 00:42
2 INC0127 00:00 00:48
3 INC0128 00:00 00:22

etc

Much appreciated, Simon.

Re: Autofill Jumping Cell Sets

Not sure how to use this function can you explain?

I have:

=SUM(Sheet1!A1:G1)

on sheet 2 Cell A1, and when I Autofill across columns I get

=SUM(Sheet1!B1:H1), on sheet 2 Cell B1,
=SUM(Sheet1!C1:I1), on sheet 2 Cell C1,
=SUM(Sheet1!D1:J1), on sheet 2 Cell D1

when I actually want

=SUM(Sheet1!H1:N1), on sheet 2 Cell B1,
=SUM(Sheet1!O1:U1), on sheet 2 Cell C1,
=SUM(Sheet1!V1:AB1), on sheet 2 Cell D1

and so on to sum 365 columns of daily data on worksheet 1 into weeks summaries on worksheet2.

Thanks, S.

How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example:

I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2.

Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.

Thanks, Simon.

Re: Efficient Entry Of Data & Automatically Have Their Price Show In Adjacent Cell

Quote

If you have already typed in product in the same column

Long product names of similar title make this ineffective. Auto-complete only kicks-in on the first character entry it can make a one-to-one relationship with.

Quote

Auto-Complete so users only need to enter abbriviations

Do you mean I create Abbreviations of the products and use that list for auto complete, or does EXCEL have an intelligent abbreviate option?

Hi,

I own a Hair Salon which collates its daily takings using a spreadsheet.

A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.

Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.

All of this is subject to human error.

What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.

Appreciate any help, thanks, Simon.

Re: Drop-Down List With Auto Complete As You Type

Dependant Lists seems OK, still a bit clunky for 2009.

Couldn't get "Decreasing Data Validation Lists" to work, but sounds interesting?

See my attached attempt at following the tutorial, can you straighten me out, as this may be what I 'm looking for.

I created a list of products A1:A10 on worksheet 1,
I moved to worksheet 2 and selected a range of cells I want the validation on,
I pasted the code, some of which came up RED,

and that was it - kept getting an error and simply couldn't see what was suppossed to happen ???

Thanks, Simon.

Re: Drop-Down List With Auto Complete As You Type

Already thought of that and while its on the right track is still unusable, as follows:

Take the following products:

Oatmeal & Honey Conditioner 250ml
Oatmeal & Honey Conditioner Tween

I will not see any Autofill until I have completely typed in

"Oatmeal & Honey Conditioner 2" , or
"Oatmeal & Honey Conditioner T"

So does not really give me a usuable solution, because I am still typing in most of the text and also any "typos" throw Autofill off straight away:

No, the real "dogs" of a solution would be to get results straight away, or at least say after 3 or 4 characters.

I know this sounds crazy but you haven't met my staff

Is it possible to get results for:

Oatmeal & Honey Conditioner 250ml
Oatmeal & Honey Conditioner Tween

After typing in "Oat", or Oatm" ?

Thanks, S.

Hi,

I have a large list of data records, 600+, and would like to use List Validation but find EXCEL's default Validation not appropriate for such long lists.

What would be perfect is similar to what you get in Access, when you type in part of the record string required, a drop-down list appears and shows you the record entries that have that string within them and not necessarily in character order, quickly wittling down to the few records that apply and then use your arrow keys to move and select the correct record, etc.

Example,
I own a salon, and many shampoo ranges have many similar products:

Superstar
Superstar Blow Dry Lotion
Superstar Leave In Conditioner
Superstar Shampoo
Superstar Conditioner

So if I typed in "Super", my drop down list would show these 5 records and then I could use the arrow key to quickly select the correct one, saving time without having to type the whole entry and/or get the description wrong, making later MATCH and INDEX functions fail.

A sticky problem is that in some ranges staff enter in slightly different names for the same thing, however somewhere in their description "Super" will appear.

Example: Superstar is actually a Bedhead range of TIGI, but thats being a bit precise but unfortunately some staff do look for products in my lists by using full details.

bedhead Superstar
bedhead Superstar Blow Dry Lotion
bedhead Superstar Leave In Conditioner
bedhead Superstar Shampoo
bedhead Superstar Conditioner

But if I type "Super", I still want these options to come up

I have attached, a work sheet showing where I am trying to go with this idea?

Sheet 1 is my "Idea" of sorts
Sheet 2 is some "sample data" of approx 200+ records
Sheet 3 is the worksheet my staff enter into each day to manage the daily takings of cash, which is the bit I need to improve as they hate typing in long descriptions for "Products"

Any guidance appreciated.
Simon.

Re: Create Alphanumeric Passwords

OK, it was an Add-In issue, all good now the RANDBETWEEN method works fine.

Apologies, Dave, but I am using your solution verbatim, still getting '#VALUE!'. Checked again just now?

Thanks, Simon.

Re: Create Alphanumeric Passwords

Trying the RANDBETWEEN method, get the jist of the method, but the equation is throwing back the '#NAME?' error.

I tried simplifying the equation elements and see whats happening but couldn't break into the equation, always giving me an error?

Can you check the equation for me.

*** JUST NOTICED DO NOT HAVE RANDBETWEEN *** IS IT AN ADD-ON?
------------------------------------------

Here what your saying on the 'INT=' method. Is there a way of controlling results to stop getting 'text' returns ?

Appreciated, Simon.

Re: Create Alphanumeric Passwords

Using the '=INT' and the Chars and uChars method, when the i refresh and therefore the volatile RAND changes, some entries change to '#VALUE!', and then chage back to a valid password if I refresh again?

I am refreshing by entering the relevant cell and dropping my cursor into the cell content window and pressing enter.

Any ideas why '#VALUE!' pops out of the equation at random?

Thanks, Simon.

Hi,

I would like to use an excel spreadsheet to generate a series of passwords. The password must include alphanumeric content, and be at least 10 characters long, and case sensitive.

For example: 14sH2dA456a

Thought about concatenting each variable, but do not know how to control the random variable creation in a spreadsheet?

I have looked at RAND but can't figure out how to use it to any avail?

Any help, much appreciated.

Thanks, Simon.

Re: Graphically Representing Activity Data

I think its pretty much spot on.
I need to play with my data and I will get back to you.

I like the Duty Cycle, thanks for the prompt.

Q. Is there a metric for the extent of the 'flip-flopping' between Run and Setup?

Example Duty Cycle gives me a cumulative effiiciency, but it would be nice to know that 'X' flip-flops were taken to achieve, and being able to create a metric to compare machine runs?

This could be a great metric for improving throughput efficiency and understand disruption.

Any ideas, Simon.

I have Activity data from my production line that details when a job is running "RUN" and when its on stop "SETUP".

It looks like this:

Run 0.45 hrs (decimal hours)
Setup 0.1 hrs
Run 1.2 hrs
Setup 0.2 hrs
etc.

I want to be able to graphically present this against a fixed timeline, I have the timeline in a column, (not a row).

I have cumulatively summed my times so I have this...
Run 0.45 hrs
Setup 0.55 hrs
Run 1.75 hrs
Setup 1.95 hrs
etc

However I want to be able to establish whether the job was ON RUN or SETUP at each interval of my timeline.

For example: if my time line was in 0.01 decimal minute intervals, my chart would look like this:

0.01 Run
0.02 Run
0.03 Run
0.04 Run
etc, all the way until
0.46 Setup
0.47 Setup
etc, all the way until
0.56 Run
etc etc

I did a search and found some details on INDEX and MATCH, but not sure quite how to implement this?

Thanks, Simon.

Re: Worksheet creation by key field from a data table

Here is more detail, while I look at Pivot Tables.

I have attached a fragment on my 16000 record worksheet including several manually created sub worksheets.

The KEY Field is in RED on work sheet "All Parts", called Technical Class, and basically is a title for similar product types.

I want to lift the records by "Technical Class" and create new sub work sheets with these classes only, naming the worksheet by the Technical Class title, e.g. 20010, 20020, 20030, and so on.

Any more details, just ask no worries.

Thanks, SImon.

Hi All,

Can you help ?

I have a worksheet with 16000 records of data. Each record has a KEY field, and I would like to print extracts from this worksheet by the KEY field.

There are 90 KEY filed variants.

To date, I have manually lifted the subgrouped records and dumped them into their own worksheet ready for printing.

This takes a huge amount of time and felt that a more automated method was available, given the knowledge.

Any ideas ?

Thanks, Simon.