A Tricky One......

Hello All,

Hopefully someone here knows something I dont!

Basically, I need a formula or function that will allow one cell to determine what value is assigned to another cell. What I've been playing with is:

=IF(H1=1,(SUBSTITUTE(Details!D6,"0",D2)))

I've never used SUBSTITUTE before and I think I am applying it wrong...... I'm not sure if it will even do what I am looking for.......

So, what is supposed to happen is that if H1=1 then cell D6 on the Details page will have the same number as cell D2. Eventually it will be an imbedded IF structure that will say that if H1=2 then Details!D7 will be the same number as D2.

The other important part is that I need it to be firm. As in, if I enter the number 1 into H1 then D2 needs to become the same number as Details!D6, but if I then enter the number 2 into H1 then I need the number that was just put into Details!D6 to stay there.

I hope I explained that well; I know it's a little confusing.

Hope someone can help!!!

You will need to rethink this.

1) You should read Help on Substitute. If A1 has the text 123A456 then
=SUBSTITUTE(A1,"A","B") in a cell such as B1 will make B1 display 123B456

2) If D6 has formula =IF(H1=1,D1,"*") then D6 will display D1's value when
H1 has value 1. But when H1 is changed to another value D6 will display *

To make D6 hold the value even after H1 loses its 1 value you will need some
fancy VBA code.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"nevi" <[email protected]> wrote in message
news:[email protected]...
>
> Hello All,
>
> Hopefully someone here knows something I dont!
>
> Basically, I need a formula or function that will allow one cell to
> determine what value is assigned to another cell. What I've been
> playing with is:
>
> =IF(H1=1,(SUBSTITUTE(Details!D6,"0",D2)))
>
> I've never used SUBSTITUTE before and I think I am applying it
> wrong...... I'm not sure if it will even do what I am looking
> for.......
>
> So, what is supposed to happen is that if H1=1 then cell D6 on the
> Details page will have the same number as cell D2. Eventually it will
> be an imbedded IF structure that will say that if H1=2 then Details!D7
> will be the same number as D2.
>
> The other important part is that I need it to be firm. As in, if I
> enter the number 1 into H1 then D2 needs to become the same number as
> Details!D6, but if I then enter the number 2 into H1 then I need the
> number that was just put into Details!D6 to stay there.
>
> I hope I explained that well; I know it's a little confusing.
>
> Hope someone can help!!!
>
>
> --
> nevi
> ------------------------------------------------------------------------
> nevi's Profile:
> http://www.excelforum.com/member.php...o&userid=33238
> View this thread: http://www.excelforum.com/showthread...hreadid=552854
>

I had a feeling that might be the case.........

The idea here is that a ton of information gets pasted into cells F2 - F1500 on a daily basis. Now, there is a list of names in cells C2-C50 and cells D2-D50 count up how many of the entries in F2 - F1500 apply to each of the names in C2-C50. On the Details sheet I have the daily results of this count.

At the moment I am manually moving the count in D2 - D50 to the daily tracker which is time consuming. I was hoping that I could put the day of the month (as 1, or 10, what have you) in H1 and have it automaticlly move it to the corosponding day for the matching name on the Details sheet.

So, let me throw in an example. C2 contains "John Doe" . D2 contains John's count of 150. On the details sheet D6 would be John's Day 1 and D7 would be Johns Day 2. I'm looking for a function that will place the contents of D2 into Details!D6 if H1=1 and Details!D7 if H1=2, but will not blank out Details!D6 when H1 is changed from 1 to 2.

I am open to suggestions if anyone has any ideas of a way to go at this........

Have you though of recording a macro that will do an Copy | Paste Special ->
Values ?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"nevi" <[email protected]> wrote in message
news:[email protected]...
>
> I had a feeling that might be the case.........
>
> The idea here is that a ton of information gets pasted into cells F2 -
> F1500 on a daily basis. Now, there is a list of names in cells C2-C50
> and cells D2-D50 count up how many of the entries in F2 - F1500 apply
> to each of the names in C2-C50. On the Details sheet I have the daily
> results of this count.
>
> At the moment I am manually moving the count in D2 - D50 to the daily
> tracker which is time consuming. I was hoping that I could put the day
> of the month (as 1, or 10, what have you) in H1 and have it
> automaticlly move it to the corosponding day for the matching name on
> the Details sheet.
>
> So, let me throw in an example. C2 contains "John Doe" . D2 contains
> John's count of 150. On the details sheet D6 would be John's Day 1 and
> D7 would be Johns Day 2. I'm looking for a function that will place the
> contents of D2 into Details!D6 if H1=1 and Details!D7 if H1=2, but will
> not blank out Details!D6 when H1 is changed from 1 to 2.
>
> I am open to suggestions if anyone has any ideas of a way to go at
> this........
>
>
> --
> nevi
> ------------------------------------------------------------------------
> nevi's Profile:
> http://www.excelforum.com/member.php...o&userid=33238
> View this thread: http://www.excelforum.com/showthread...hreadid=552854
>