Posts by Jas

Sample file:

http://jasandtina.com/pivot-test.xls

The purpose of the spreadsheet is to print one report for each employee who missed one of the three key metrics from the day before.

I don't know what the most logical way of accomplishing this task is, but this is what I came up with. Unfortunately, I only have a grasp on step one, and even then I'm not real sure of myself.

1) Set the Date field in PivotTable1, PivotTable2, and PivotTable3 to the date displayed cell I2.

2) For every data point in PivotTable1 that is greater than the value entered in C6, copy the employee's name into the list starting in cell K9.

3) For every data point in PivotTable2 that is less than the value entered in cell F6, copy the employee's name into the list starting in cell K9.

4) For every data point in PivotTable3 that is greater than the value entered in I6, copy the employee's name into the list starting in cell K9.

5) For every unique name in the list starting in cell K9, change the "Name" field in PivotTable4 and print the worksheet. This process should repeat until all names have had an individual report printed.

Again, if there's a better way of accomplishing my goal, I'm open to suggestions! Thanks in advance for any time you spend looking at my problem.

Jas.

That's perfect! Thanks!

I know I've seen this in here before, but can't find it. :redface:

I need to have a count of how many times conditions in two columns are met.

If A2=2 and B2="x"
If A3=2 and B3="x"
If A4=2 and B4="x"...

... A20=2 and B20="x"

I need to know how many times it happens that column A = 2 and B = "x"

Formula help? Thx in advance.

:biggrin:

Thanks, don't worry, I WILL be back frequently! :wink2:

In the mean time, I've also ordered one of the books reccomended here, and can't wait for it to show up...

Derk, you're awesome. It works perfectly! Thanks much.

As part of a macro I'm writing, I want to autofill a column.

It should go to the last filled line in the column, then use auto fill to continue to the bottom of the list. In the attached example, the column in question is column F.

Here's what I have so far...

Range("F1").SelectSelection.End(xlDown).Select

When I tried to record the macro, it returned references to my current list...

Selection.AutoFill Destination:=Range("F4:F10")

That works great for this one example, but as soon as I update the list, the range will no longer be F4:F10.

Suggestions, anyone? Thanks in advance! :coolrsvd:

That looks pretty slick... My VBA isn't strong enough for me to understand everything that's going on, so I'll spend some time with my nose in a book to figure it out! :wink2:

I'm particularly confused as to how this macro would know what files to open.

I also don't know what this line does: On Error GoTo 0

Other than that, I have at least partial clues...

I have 7 employees who keep similar lists in Excel. Each list has the same four items in it (columns A through D). Column C is the date. There are anywhere from 0 to 100 line items entered into the list for each date.

I regularly open all seven worksheets, copy the NEW data, and paste it into another worksheet that has a combined list from all 7 people. I use the combined list to feed a pivot table.

I would like to automate this task... I'm open to suggestions on how to solve this...

A macro to open each file in turn, copy the new data, paste it at the bottom of the list is the only thing I've been able to think of. I'm not sure the best way to do it as I would want to specify for the macro to ONLY grab the data with a specific date in each of the 7 "child" worksheets to copy them into the "parent" worksheet.

If there is another way to accomplish this without using a macro, that's good too. Any and all suggestions are welcome.

Thanks in advance!

Maybe give everyone their own sheet in the same workbook, then share the workbook? That way anyone who needs to can have it open at the same time?

Menu: Tools... > Share Workbook...

You can reference data from other spreadsheets, though, using formulae, like:

='[Book1.xls]Sheet1'!A1

"I'm trying to get the last column number of the pivot table"

Start in K6.

=IF(A7="Grand Total,"J6,"")

Copied down, will eventually give you

=IF(A240="Grand Total",J239,"")

The only time that formula will return a value will be when the next row is the total... thus the last column number of the pivot table.

I have no idea if that's anywhere near being helpful... I didn't quite understand what you were trying to do.

Copy, then

Edit
Paste Special...
Values

Another way to skin this cat:

=IF(ISERROR(A2/B2),0,A2/B2)

I previously used invisible boxes (no text, no fill, no line) to make "hot spots" and assigned the macro to the auto-shape. It's working well for me, but I was looking for something else for alternate methodology. Ralph's code looks like a good alternative, too. Thanks!

What if you want one macro to run if you click any cell in the range B2:C15

and another macro to run if you click in range D5:F7?

Thanks a ton, all!

I'm pretty good at recording macros, taking them apart, and learning what I can from what is recorded... but I want a conditional action based on the value of a specific cell. So now I need help.

Sub dbox5and6

While S22 = "Presentation"
DialogSheets("US5").Show
Else
DialogSheets("US6").Show

End Sub

I know my code there isn't correct, but you get the idea of what I want to do, I think... My macro should show the user a hint when they click a button. The hint it gives is based on the value of cell S22.

I can't download any attachments, so I don't know if you'll be able to get this or not... but it has an example of generating random #s on one sheet, copying them, and pasting them onto another.

I used a button rather than an automatic macro... with an automatic macro, you won't be able to tell what your student was looking at when he or she did the problem. This way, it doesn't change unless you tell it too.

If you can't see the attachment, shoot me a PM and I'll throw you an e-mail with it attached.

EDIT: I see pennsaver did something a little more advanced while I was typing this post...

I can't download your sample, but a macro can be created to copy the randomly generated data and then paste just the values (perhaps in a different location so you still have the random formulaes for the next time you use the spreadsheet). Would that suit your needs?

Nevermind... got it... Here's what I'm using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myrng As Range
Set myrng = Range("s22")

If Intersect(Target, myrng) Is Nothing Then Exit Sub

If Target.Cells.Value = "Presented Galleria" Then

Range("O27").Value = " "

End If

If Target.Cells.Value <> "Presented Galleria" Then

Range("O27").Value = "Additional Feature"

End If


End Sub