A Tricky One......

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nevi - 17 Jun 2006 00:24 GMT

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!!!

Signature

nevi

Reply to this Message

Bernard Liengme - 17 Jun 2006 00:39 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hello All,
>
[quoted text clipped - 23 lines]
>
> Hope someone can help!!!

Reply to this Message

nevi - 17 Jun 2006 00:57 GMT

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........

Signature

nevi

Reply to this Message

Bernard Liengme - 18 Jun 2006 14:04 GMT

Have you though of recording a macro that will do an Copy | Paste Special ->
Values ?
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> I had a feeling that might be the case.........
>
[quoted text clipped - 18 lines]
> I am open to suggestions if anyone has any ideas of a way to go at
> this........

Reply to this Message