Use a Cell to check value of a different cell to populate another cell

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Use a Cell to check value of a different cell to populate another cell

Post by bradjedis »

Greetings.

I have a need to do the following:

In Cell F5, check Cell E5, if E5 is not blank, Enter a '1' in Cell G5.

I tried this, but to no avail.

=IF(E5=" ",G5 ="1",G5= "")

Thoughts? I do no want to have a formula in the G5, as I have conditional formatting looking for specific data in G5.

Thanks,
Brad

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use a Cell to check value of a different cell to populate another cell

Post by HansV »

A formula in one cell cannot set the value of another cell, you do need a formula in G5.
Why does that conflict with conditional formatting?
Best wishes,
Hans

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Use a Cell to check value of a different cell to populate another cell

Post by DocAElstein »

Hello
Out of interest**, one way to consider: Modify the coding here:
https://eileenslounge.com/viewtopic.php?f=30&t=36075
It’s using a UDF in a way it’s not supposed to work.

Example modification:

Code: Select all

 Option Explicit '  https://eileenslounge.com/viewtopic.php?f=27&t=39168    https://eileenslounge.com/viewtopic.php?f=30&t=36075
Function WotsThereWhere(ByVal Rng As Range) As String
' Evaluate "='" & ThisWorkbook.Path & "\TryThisPlease.xls'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
 Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
End Function
Sub YouNameIt(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 'Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
    If Rng.Offset(0, 0).Value <> "" Then Let Rng.Offset(0, 2).Value = 1
End Sub
In the modified coding version above,
_ if you empty cell E5, then the formula (UDF) in cell F5 will empty cell G5.
and
_If you put anything in cell E5 then the formula (UDF) in cell F5 will put a 1 in G5

If you do any further modifying to a file with that coding in, then you will always need to save and close and re open before anything will work as it is messing unconventionally with low level workings of defined dependence trees , re directing things as we are probably not supposed to, and other things I know nothing about.
**This is a bit of an unconventional way. A more conventional way using Event coding might be safer.

Alan
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 15 Jan 2023, 11:02, edited 4 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Use a Cell to check value of a different cell to populate another cell

Post by DocAElstein »

HansV wrote:
15 Jan 2023, 09:17
A formula in one cell cannot set the value of another cell, you do need a formula in G5.
I think that is officially true, - I have certainly heard smarter people than me often say it, but I think we can maybe, sort of, do it, or do something similar, if it’s a certain sort of UDF, https://eileenslounge.com/viewtopic.php ... 03#p280303

(We did something similar here as well, I think
https://eileenslounge.com/viewtopic.php?f=30&t=38798 )

( I am not necessarily recommending this solution, as it’s a bit unconventional. I think it's an interesting contribution, that's all
.......................... :flee: )
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Use a Cell to check value of a different cell to populate another cell

Post by bradjedis »

interesting replies. I was able to use the code in the https://eileenslounge.com/viewtopic.php ... 03#p280303

with a bit of massaging. directed the module2.YouNameit to point to the correct module. Original code had Modul1.xxx

Now IT works, but is working when the cell it checks is blank. Is there a way to check for blanks, and then NOT process till there is something>

I am using as the referenced post indicates.
This is located in the cell F5, data is in E5 and it places a 1 in the G5 cell.

=WotsThereWhere(E5)

Code: Select all

 Option Explicit
Function WotsThereWhere(ByVal Rng As Range) As String
 Evaluate "='" & ThisWorkbook.Path & "\bill-pay-checklist.xlsm'!Module2.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
End Function
Sub YouNameIt(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = "1"
' Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
End Sub


User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use a Cell to check value of a different cell to populate another cell

Post by HansV »

Change the line

Code: Select all

Let Rng.Offset(0, 2).Value = "1"
to

Code: Select all

    If Trim(Rng.Value) = "" Then
        Rng.Offset(0, 2).Value = 1
    Else
        Rng.Offset(0, 2).ClearContents
    End If
But I'm not convinced you need this...
Best wishes,
Hans

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Use a Cell to check value of a different cell to populate another cell

Post by DocAElstein »

bradjedis wrote:
17 Jan 2023, 01:54
with a bit of massaging. directed the module2.YouNameit to point to the correct module. Original code had Modul1.xxx
Hi
Correct. Actually if you look at the 'comments on that line you can see the shortened version just requires the macro name.
It’s just a personal preference of mine to do these things very explicitly, especially when doing these unconventional things, that’s all.



Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Use a Cell to check value of a different cell to populate another cell

Post by bradjedis »

Ok. so that did not do what I am looking for is, if there is data in cell E5, then populate G5 with a 1. If the cell E5 is empty, then do NOT populate with a 1.

naturally, this will be carried down, and in the similar col's for the rest of the year.

F5 is where the =WotsThereWhere(E5) is located.

There is conditional Formatting that inserts the Green check.

I replaced the section of the VBA as indicated.

Not working. Any thoughts?
You do not have the required permissions to view the files attached to this post.

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Use a Cell to check value of a different cell to populate another cell

Post by bradjedis »

ADDITIONAL Info:

If I start with the data in col E blank and col G blank, and enter the date in E5, it will populate the G5 accordingly. However Say I blank the date, it does not clear the G5 entry of a 1.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Use a Cell to check value of a different cell to populate another cell

Post by DocAElstein »

Hi
bradjedis wrote:
18 Jan 2023, 02:13
… enter the date in E5, it will populate the G5 accordingly.
However Say I blank the date, it does not clear the G5 nentry of a 1.
Ah, OK I misunderstood the logic. (Actually I thought it might be that a few times. I went back and forth a bit in my head with what you want, and finally did what was the minimum change to my very original coding from the old Thread, ( by the way, regarding the link to that old Thread - your link in your post is broken- if you want to copy a link from a forum post you often must right click on it and select something like “copy link” , otherwise you get what you see, which is often just part of the link, - and so end up often if you copy it directly with just that small bit in the clipboard , and then you will end up pasting the shortened bit seen in the post. That itself does not work as a link when pasted/ posted ).
_.____________________________________________________________________________

This is wot I gave you
_ if you empty cell E5, then the formula (UDF) in cell F5 will empty cell G5.
and
_If you put anything in cell E5 then the formula (UDF) in cell F5 will put a 1 in G5


But this is wot you want, at least that is wot I am now thinking......

_If you put anything in cell E5 then the formula (UDF) in cell F5 will put a 1 in G5

_.__________________________________________________________

OK, so..... back to my original code and macro for you in this Thread ( the one we are in now )
To stop the thing clearing anything, you just need to remove this line:
Let Rng.Offset(0, 2).Value = ""

Then all that will happen is that If you put anything in cell E5 then the formula (UDF) in cell F5 will put a 1 in G5. That’s it. Empty the cell E5 and nothing will happen anymore, because Rng.Offset(0, 2).Value = "" is wot was emptying it

The working logic is now just from this original line
If Rng.Offset(0, 0).Value <> "" Then Let Rng.Offset(0, 2).Value = 1
, where Rng is what you pass in the formula in cell F5
So far we have had E5 in the demo formulas, so that has been the Rng so far, so , so far Rng.Offset(0, 2) is 2 cells to the right, and 2 cells to the right of E5 is G5

Try this ‘un :

Code: Select all

 Option Explicit '  http://www.eileenslounge.com/viewtopic.php?p=303425#p303425
Function WotsThereWhere(ByVal Rng As Range) As String
' Evaluate "='" & ThisWorkbook.Path & "\TryThisUn.xls'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
' Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
' Evaluate "='" & ThisWorkbook.Path & "\TryThisUn.xls'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"
' Evaluate "='" & ThisWorkbook.Path & "\bill-pay-checklist.xlsm'!Module2.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"
 Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"
End Function
Sub YouNameIt(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
' Let Rng.Offset(0, 2).Value = ""
 'Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
    If Rng.Offset(0, 0).Value <> "" Then Let Rng.Offset(0, 2).Value = 1
End Sub
Choose the line you want to use in the first function. To demo I show this time the simple version. But I would recommend you using the full explicit version as you did before. That makes it a bit more bullet proof, could help you understand a bit better what is happening, ( and , most important - It looks more pretty and clever, Lol, ! – I think it was Shakespeare or someone who said – Wot’s the point of writing VBA coding if you don’t make it look beautiful like wot Alan does ) )

By the way, you may have noticed that for wot you want, you also don’t need
_all the Sht , ActiveSheet stuff.
or
_ the
Stop ' It wont

But I would leave it all there – makes it all a bit more pretty. Maybe add some more stuff in the 'comments hidden to the right, I do lots, useful info, links, - links to your favourite nature movies etc.


Alan
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 18 Jan 2023, 11:12, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Use a Cell to check value of a different cell to populate another cell

Post by DocAElstein »

bradjedis wrote:
18 Jan 2023, 02:01
...naturally, this will be carried down, and in the similar col's for the rest of the year....
I had not tried much at all with this thing, but I just dragged the formulas around a bit and it seems to do like you want.
I tried a few Excel versions and computers and versions. But have not investigated thoroughly. It seems to have blown up one old Excel 2003 version, but so far from 2007 it seems stable.
Remember though it’s best to save, close and re open after any changes – we are messing “illegally” with re directing things as its thought we can’t/shouldn’t…. and remember you are using it at your risk, I do these thing initially just out of academic interest, but/as in the long term some very useful novel things or new ideas come out of it… example I just got back from a major night shift when I wrote the last post, I was on a project that involves doing something involving re directing not thought to be possible: redirecting a slow moving Goods Train along a forgotten piece of unused track, track last used for bad things I think in WW2, and not shown on any plans or maps, and not thought to exist, ( or worse deliberately not talked about). It’s the same basic idea. “Illegal”, but morally I am happy I can justify it, (and I sometimes wonder if God lends me a hand - absolutely perfect timing. I finished at 3 o'clock this morning, and then it snowed heavily to hide all my tracks.......)
ReDirect,drag,(andHide).JPG
............ :flee:
Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Use a Cell to check value of a different cell to populate another cell

Post by bradjedis »

DocAElstein or Alan,

I downloaded the example file TryThisUn.xls. I enter data into E5, and G5 populates with a 1. Good. Now I remove the data in E5, and the data stays in G5. I am hoping that if I remove data in E5, that G5 clears. I did nothing to your test file other than enter and remove data in E5

Thoughts?

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Use a Cell to check value of a different cell to populate another cell

Post by DocAElstein »

bradjedis wrote:
18 Jan 2023, 02:01
if there is data in cell E5, then populate G5 with a 1. If the cell E5 is empty, then do NOT populate with a 1....
bradjedis wrote:
18 Jan 2023, 02:13
..., and enter the date in E5, it will populate the G5 accordingly. However Say I blank the date, it does not clear the G5 entry of a 1.
bradjedis wrote:
18 Jan 2023, 21:34
I enter data into E5, and G5 populates with a 1. Good. Now I remove the data in E5, and the data stays in G5. I am hoping that if I remove data in E5, that G5 clears.
??????? :scratch:
As Shakespeare said... To clear or not to clear, that is the question?
( Does blank the date not mean clear?
Last edited by DocAElstein on 18 Jan 2023, 22:03, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use a Cell to check value of a different cell to populate another cell

Post by HansV »

Let's take a step back. You wrote in he first post that you didn't want a formula in G5 because that would interfere with conditional formatting. Can you tell us why?
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Use a Cell to check value of a different cell to populate another cell

Post by bradjedis »

Hans, I do not think Conditional formatting is a problem now.

Using the file supplied by Alan,

First image is of the file before I enter data. second Image is with data. That part works. Once I blank out the data in E5, I would expect G5 to blank out.
Starting file.PNG
Second. PNG.PNG
Third.PNG
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use a Cell to check value of a different cell to populate another cell

Post by HansV »

Instead of a complicated function in F5, I'd use a simple formula in G5 itself:

=IF(E5="", "", 1)
Best wishes,
Hans

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Use a Cell to check value of a different cell to populate another cell

Post by rory »

HansV wrote:
18 Jan 2023, 23:32
Instead of a complicated function in F5, I'd use a simple formula in G5 itself:
Seconded. There's a very good reason this sort of behaviour is not allowed other than by hacks. Trying to audit/maintain workbooks with this kind of function in them can be an absolute nightmare.
Regards,
Rory

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Use a Cell to check value of a different cell to populate another cell

Post by bradjedis »

Ok, so following that logic, populating cell G5 Initially would fill a 1, thus removing the formula. Now when I blank the data in Cell F5, G5 does not blank out.

I tried a conditional format, but that will not work.. maybe I am just out of luck. on this one.

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Use a Cell to check value of a different cell to populate another cell

Post by bradjedis »

UPDATE: I added this to the VBA and it does what I need...

If Rng.Offset(0, 0).Value = "" Then Let Rng.Offset(0, 2).Value = ""


Will continue testing...

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use a Cell to check value of a different cell to populate another cell

Post by HansV »

Just to make sure: my recommendation is NOT to use WotsThereWhere in F5, just the formula =IF(E5="", "", 1) in G5.
Best wishes,
Hans