YouTubersLoveExcel#51: VLOOKUP & Variable Labels

I am using Excel 2000 (yes i know it's ancient but the business won't pay to upgrade!) and have put together a line chart. The line chart shows, for each customer order, how many days it took to despatch the stock.

What i would like to do is set something up, whereby a label appears next to any point on the graph which is greater than 5 days (but not if it is below 5 days).

Is this possible?

I have a Laminated label I need to print for 200 items.

The label is set up in A2 (Item) Data comes from B2 etc
A3(Size) Data comes from C2 etc
A4(Color) Data comes from D2 etc

I need Macro to Get Data from B2, C2, D2,
Put in A2, A3, A4,
print,
Move to next data line B3,c3,d3
Get Data, put in A2, A3, A4, (always fixed)
Print,

Loop to end of data in column b.

I can use the formula =Sum('11Jan2011:01Jan2011'!A1) to add up all the cell values in A1 on all the sheets. But need to set the dates as a variable from a date set in another cell and make this a Text so to match the (sheet tags) names.
=SUM(TEXT('B3,"DDMMMYYYY")TEXT(F3,"DDMMMYYYY"'),!A1)) does not work.
Any help would be great.
Thanks

OK excel guys... I hope this is an easy one! Here we go...

I have a simple bar chart. The x-axis labels give me a range of values from 1 to 10 (ex: a ten year cash flow). I want to make this variable so if I choose to look at the chart using only a 5 year cash flow, I'm not looking at a chart that has data charted and truncating in year 5 with half the chart taking up empty space. Likewise, if I wanted to look at a 20 year cash flow, it would expand out to 20 years instead of being limited to 10 years, as defined by the range initially selected.

I'm using the "Source Data | Category (x) axis labels" field to redefine the range at present. Is there a way to make this dynamic through a cell reference (the referenced cell builds the range reference), or through a macro? I think a macro would work, but I'm trying to keep this "so easy a caveman could do it" if you know what I mean.

Thanks.

I am trying to print some labels for shipping containers. The query produces the total quantity of the order, the quantity per container and a calculated field figurers the number of containers. Labels are 4-up on a sheet. I could leave some blanks in between orders that didn't come up even, but would rather not. Right now I have a command button on a form, but I can't figure how to get it to multiple copies, let alone vary the number of copies with each order? HELP?

Any suggestions?

Rocky.... :D

OK it took some time, but I did find a simple solution. If you add a field called Count to the table and the form and set it to the number of copies of each label. (the text box on the form can be invisble, but must be there.) Then in the section detail print event add the following code.

Sub Detail_Print(Cancle As Integer, PrintCount As Integer)
If Me!txtLabelCount = 0 Then
Me.NextRecord = True
Me.MoveLayout = False
Me.PrintSection = False
Else
If PrintCount < Me!txtLabelCount Then
Me.NextRecord = False
End If
End If
End Sub

This comes from an old Sybex book by Ken Getz, Paul Litwin & Greg Reddick titled "Microsoft Access 2 Developer's Handbook" ISBN: 0-7821-1327-3 good stuff still today!

Hi I am doing a lost and found worksheet for work and I am trying to have the labels sheet where if I entere the item #, it will fill in the details from the Found sheet. Can you please help me out with the vlookup code.

I tried looking the code up, but just can't get it right. Thanks for the help.

Hello all. I have found the message boards to be a great resource for many projects. This new one has me a bit stumped. I have a data set that labels rows using the year and quarter - 20071, 20072, 20073, 20074 for example (calendar year 2007 quarters one through four). In one instance, I need to identify data from the last four quarters. I am using vlookup but am unsure how to structure the formula when I start at quarter two and have to work back to the prior year.

Hi
I am a very, very new Excel user, so pardon if this is a stupid question. I have created a pie chart and for each wedge there is a legend with coloured labels. I wanted to add the percentage as well, so I go to Data Labels, click Percentages and Best Fit, and voila, there are my percentages, but they are outside the Pie Chart and I want them to be NEXT to the legend.

1% small red block - Manufacturing
5% small blue block - Medicine, etc.

Can anyone help? I could send you the chart.

PS: When I got the chart, it was like that, except the percentages were skew, so I fiddled and now nothing works properly. I have tried to make the chart area smaller and bigger.

Thank you very much in advance.

Hi

I'm not sure how to go about using the Labels control in Excel. I embed several of them in an excel worksheet and want to step through all of them but for some reason excel doesn't seem to recognise a Labels Collection. Is there a Labels Collection?

Here's what I tried to do.

First I added 5 Labels to an Excel Worksheet. Label1, Label2...to Label5
Now I tried to step through them.


Below I get Runtime error 13 'Type Mismatch'
Code:

For each Label in ActiveSheet.Labels
Label.caption = "Test"
next Label

I also tried to make an Array of Labels

For the below I get Runtime error 91 'Object Variable or With Block Variable not set'
Code:

Dim Lbls(1 to 5) as Labels
'eg
Lbls(1) = Activesheet.Label1

If I place Set before the Lbls(1) I get Runtime error 13 again 'Type Mismatch'

I beleive this comes down to my fundamental understanding of Labels and how they are reference.. etc.

Any help on this would be greatly appreciated.

Thanks so much.
Danny2000