How to add PivotTable drop down selector to another tab?

[John1889]

Hi,
I have a pivot table on Sheet 1 and a report that references it on Sheet 2. I would like to be able to change the pivot table variables from Sheet 2 instead of having to go back to Sheet 1. Is there a way to duplicate the drop down selectors from the pivot table on Sheet 1 onto Sheet 2? Any suggestions would be deeply appreciated.
Thanks in advance.

[MattJM]

Hi,
I have a spreadsheet in which I am trying to Filter Out Contractors. Basically the Column can have several values. It can either be blank, PRE or have another value which can vary. I want to be able to filter rows which are neither Blank or Pre. The Values are in column H. I created a helper column in column I with the following formula =H2="". I then created another helper column in column J with the formula =H2<>"PRE". Both formulas work OK. My question is how do I combine the two formulas so that I end up with the rows which do not contain Blank or Pre. I tried =H4="" or H4="PRE" but I get a message saying that my formula contains an error.
Thanks

[BurgoEng]

Hi,
Try one of the below mentioned method.

1: Use code and the worksheet change event to take a value chosen from a Data Validation drop down and input that into the pivottable

2: put the pivottable on the same sheet as the report but hide all the rows of it except for the dropdown - NB - this will only work if the number of rows / columns in your pivottable is fairly static.

[BurgoEng]

Hi,
Try the following formula

=IF(H2="","",IF(H2="pre","",H2))

this checks H2 for a blank, if it is it populates the cell with nothing (""). If it isn't blank it then checks for "pre", if it is it populates the cell with nothing (""). If the contents passes both of these checks the formula will populate the cell with the contents of H2.
Cheers!

[MattJM]

Hi BurgoEng I was out of city on some personal reasons, thanks a lot for your response. I will try it out and will let you know.

[YukonOne]

Hi,
I need help in creating a simple macro that copies the cell that is to the left of my current cell, into my current cell.

When I record the macro it looks like this;
Range("C11").Select
Selection.Copy
Range("D11").Select
ActiveSheet.Paste

My problem is, the macro has hard coded reference to the cells that I was on when I recorded the macro (i.e. C11 & D11). How would I change this to say 'current cell' and 'cell to the left'. Any suggestions would be greatly appreciated.
Thanks in advance.

[BurgoEng]

Hi,
Try this CODE out:
selection.offset(0,-1).copy selection

Cheers!

[John1889]

Thank you BargoEng, I will try it.

[John1889]

Hi,
How to write VBA code to select a range of cells say..A1 to A15 (I can do that part) and then number the cells in sequential order i.e.-1,2,3,..etc.
Thanks for any help.

[BurgoEng]

Hi,
Use:

Sub NumberCells()
Dim i As Long
For i = 1 To Selection.Rows.Count
Selection.Cells(i, 1) = i
Next i
End Sub

Select the cells first, then run the macro. I assumed that you wanted to number only cells in the first column of the selection.
In addition to that and depending on exactly what you are trying to do:

Range("A1").Value = 1
Range("A1").AutoFill Range("A1:A15"), xlFillSeries

Cheers!