=IF.. formula help 1

[DarkOne72]

Hello All,

I have an excel sheet with multiple sheets, I have a pivot table on one which shows a different number each day. What I want to do is on another sheet ,(Dates),is capture that number from the pivot table and put it into another cell for each day of the week (in dates sheet). The problem I run into is that with my formula I can capoture the number for that day but then it changes ALL the numbers in all the cells erasing the previous captured days and replacing it with the current number.
Here is my formula

=IF(Dates!$A$2=I$2,Pivot_Table!$R$2,"")

I hope I explained this well enough.

Thanks in advance

 

[SkipVought]

It's as clear as mud!

Please post a sample of the data on the DATES sheet.

What sheet is I$2 on? You say you're trying to find a date in the PT. Why not use the GetPivotData function?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DarkOne72]

I$2 is on the Dates sheet as well.

Wouldn't the GetPivotData change with each day as well?

I guess a more clear question would be:

Is there a way to copy a fields value (say 2100 in A1 on sheet1) and paste it on another sheet in say cell B5 sheet2 and not change when the main cell (A1 sheet1, that had the 2100 value before) changes and this being done by formula?

 

[SkipVought]

Yes.

Did you look at that function? What formula do you get when you reference the CELL you want to find, in the PT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DarkOne72]

I have put together a basic example of what I am talking about hopefully it makes more sense than I am trying to explain.
Thanks again

 

[SkipVought]

You cannot do what you want with a formula.

Change the data on sheet2 ...
[tt]
7/22/2008 7/23/2008
0 2600
[/tt]
paste this code in a MODULE and run. It will ADD the new date in the next column and the TOTAL from sheet1.

Sub AddNewData()
    With Sheet2.[A1].End(xlToRight)
        If .Value < Date Then
            .Offset(0, 1).Value = Date
            .Offset(1, 1).Value = Application.Max(Sheet1.Columns(1))
        End If
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DarkOne72]

Thank you ..works great!

your awesome!!!

 

[SkipVought]

the only way that you COULD use a formula is if you ALSO had the Day's Date associated with each column of data on sheet1. In fact, It could ALL be done on sheet1.

Like
[tt]
Sheet1
------------------------------
7/22/2008 [red]7/23/2008[/red]
=Sum(A3:A999) [red]=Sum(B3:B999)[/red]

500 367
600 497
568 316
35 3
897 423
[/tt]
Just [red]select 'n' drag[/red] to the next cell for the next day's DATE & TOTAL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}