RE: Formula help

[Bazola]

RE: Formula help

Hi All

I need some help with something if you dont mind..

Basically I have a cell that if it contains the text "Yes" it will enter todays date in the cell next to it. I also need it to copy data from one cell into another at the same time but take the values only, not the formula.

Thanks in advance.

Baz

 

[emukiss10]

Re: Formula help

Hello,

You need to specify which cells containing what and what you would like to be in them.

ex.


A1

YES

A2

=if(A1="YES",B1=TODAY(),"")

AND

A3
=if(A1="YES",A3=D2,"")

if A1 = YES cell A3 will have value of cell D2


EDIT*
I don't know if You can without useing VBA or reference to another cell freeze formulas "TODAY()"'s date. It will update itself every day.

best regards
w.

 

[Bazola]

Re: Formula help

Hi Emukiss10

I have tried the formula

=IF(H3="Yes",I3=TODAY(),"") in the first instance updating the cell references to mine, and it doesnt seem to work.

It just says, "There are one or more circular references where a formula refers to its own cell either directly or indirectly" and provides the wrong date - 01-Jan-00

 

[emukiss10]

Re: Formula help

My bad.

remove I3 - leave only TODAY() If You want todays date in cell I3

=IF(H3="Yes",TODAY(),"")

And dont forget to format cell as Date

 

[Bazola]

Re: Formula help

Awesome thanks.

How do I build the next step into it, so

=IF(H3="Yes",TODAY(),"") + I need it to Copy the content of cell G3 and put it in J3 - I want the copy to be values only though, not the formula.

 

[emukiss10]

Re: Formula help

try

In cell J3

=IF(H3="YES",G3,"")

 

[Bazola]

Re: Formula help

That does copy the content from the cell, but the original cell G3 contains a formula to work things out, hence why I need it to copy the value only at that point in time, otherwise it may change if someone amends the headcount for that site.

=IF(F3="Cloud < 10 users","Not Required",IF(F3="X-Small 15-25 users (or SfB Gateway or large data storage","Not Required",IF(F3="Small 25-50 users","1 UCS - 8 SAS HDD",IF(F3="Medium 50-100 users","2 UCS - 8 SAS HDD in each Host",IF(F3="Large > 100 users","2 UCS - 8 SAS HDD in Host 1, & 6 SSD HDD in Host 2",IF(F3="X-Large> 200 (plus big data)","2 UCS - 8 SAS HDD in Host 1, & 8 SSD HDD in Host 2"))
))))

 

[emukiss10]

Re: Formula help

I dont understand what do You need.

formula =IF(H3="YES",G3,"") will copy the contents of G3 (not formula in G3 but value of G3) to cell in which the formula is placed.

mayby You can give wxample of what do You need to have in G3/J3 based on the formula You have provide.

=IF(F3="Cloud < 10 users","Not Required",
IF(F3="X-Small 15-25 users (or SfB Gateway or large data storage","Not Required",
IF(F3="Small 25-50 users","1 UCS - 8 SAS HDD",
IF(F3="Medium 50-100 users","2 UCS - 8 SAS HDD in each Host",
IF(F3="Large > 100 users","2 UCS - 8 SAS HDD in Host 1, & 6 SSD HDD in Host 2",
IF(F3="X-Large> 200 (plus big data)","2 UCS - 8 SAS HDD in Host 1, & 8 SSD HDD in Host 2"))))))

 

[Bazola]

Re: Formula help

Hi Ok, so I will try and explain my issue.

I have a cell that says headcount, so you input the headcount for that site, from there there is another cell F3 which has the following formula in it: -

=IF(C3<10,"Cloud < 10 users",IF(C3<25,"X-Small 15-25 users (or SfB Gateway or large data storage",IF(C3<50,"Small 25-50 users",IF(C3<100,"Medium 50-100 users",IF(C3<200,"Large > 100 users","X-Large> 200 (plus big data)"))))
)

Cell G3 then has the following formula in it: -

=IF(F3="Cloud < 10 users","Not Required",IF(F3="X-Small 15-25 users (or SfB Gateway or large data storage","Not Required",IF(F3="Small 25-50 users","1 UCS - 8 SAS HDD",IF(F3="Medium 50-100 users","2 UCS - 8 SAS HDD in each Host",IF(F3="Large > 100 users","2 UCS - 8 SAS HDD in Host 1, & 6 SSD HDD in Host 2",IF(F3="X-Large> 200 (plus big data)","2 UCS - 8 SAS HDD in Host 1, & 8 SSD HDD in Host 2"))
))))

So the trouble I have with what you've provided is, if someone amends the headcount, it will automatically change whats in J3 to a different option. I dont want it to do that. I want it to copy the values based upon when we initiated the =IF(H3="Yes",TODAY(),"") and not dynamically update if the headcount is changed.

Sorry this is complicated, I hope you understand





So at the moment with what you have provided, it will copy whatever is in G3 to J2 which is fine. However because the formula provided is also linked to another cell which is how it get populated.

 

[emukiss10]

Re: Formula help

I think there is no way to copy values and freeze them with a formula. You need to have VBA macro for that to happen. Formulas will always change dynamicly. So either You will copy and paste values maualy or ask for macro that will do it for You. like this:

Sub cps()
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.cutcopymode=false
End Sub

(the code was copied from https://www.mrexcel.com/forum/excel-questions/628624-paste-values-formula-cell.html)