How to make a cell value permanent with a Function

Question

Hey There

I need help with a spreadsheet, I need to make a cell value permanent.

or if there is a different solution to my problem, I will be more then happy to get help.

this is what i need:

if you look at the attachment on this massage, you will see a part of a spreadsheet.

now, every time that column L or M or N will show a value, I need a cell next to it on the same row to show, ether or:

1. a permanent value as "1", even if cell K4 will not be highlighted and cell L4 will not show a value(cell L4 is a result of K4).

or

2. if cell K4 is highlighted or cell L4 shows a value(cell L4 is a result of K4), I need a cell next to them that counts how many time ether of them comes up.

the values on this spreadsheet are conditional and they change on a daily base, and the resin that I need a "Function" is that this workbook can contain from 37 spreadsheets up to 70 spreadsheets, that most of them almost have the same or some what different Tables of Numbers but with different increments in them.

and please don't tell me, copy and paste the Values so they will become as a permanent text, That is to much work with a workbook that could contain from 37 spreadsheets up to 70 spreadsheets, I need a function for that, or I will accept a VBA solution as well.

please help me, and thank you

David V M

Answer 1

The final solution to this is provided below.  The basic issue was to identify specific cells that changed on a series of worksheets in the workbook in response to changes in cells on one other worksheet.  The number of the "other" sheets is variable and can change over time.  The specific layout of those can change in terms of number of columns/rows used, but each follows the same general layout.  The code was all placed in the code module for the one sheet where the changes are initiated (by changing a value in row 8 on that sheet)

Option Explicit

'these constant values 'define' the different sheets so that

'the code can determine where the values on Start are that

'need to be monitored and

'how things are laid out on the "t_#" type sheets,

'and how to determine that it is a "t_#" type sheet

'in case other sheets besides the 'Start' and "t_#" type sheets

'are in the workbook

' this is the row on the Start sheet where the values we

' need to monitor are located.  These MUST be the only

' entries on that row of that sheet!

Const startDataRow = 8

'these refer to the "t_#" type sheets

' where to find the "POD ix of X" type entries

Const sheetKeyPhraseCell = "B3"

' enough of the "POD ix of X" type entries to

' ensure accurate identification of cell B3

' You must start at the left side of whatever phrase is

' being used and include enough characters to uniquely

' identify it.

Const sheetKeyPhraseText = "POD " ' 1st 4 characters are always "POD "

  'this will be the number of columns between the

  'columns with 'B4', C6, D11, E7, etc type entries in them

  'Example: 1st is C, next is F, so 6-3 = 3

Const columnGap = 3

'these variables are set up in the

' Worksheet_Change() event process as needed to

' define any given sheet that must be processed

'

  'this will be the sheet to be processed

Dim anyPODSheet As Worksheet

  'this will be the first row to examine for

  'entries like B4, C6, D11, E7, etc.

  'the last row will be determined at run time

  'by examining the entries in column A of

  'the sheet itself, allowing for any given

  'sheet to have a different number of entries (used rows)

  'or to all be the same, but user doesn't have to track it.

Dim firstPODRow As Long

  'This will be the letter ID of the first column on the

  'sheet with a 'B4', C6, D11, E7, etc type entry in it

Dim firstColID As String

  'this will be the letter ID of the last column on the

  'sheet with a 'B4', C6, D11, E7, etc type entry in it

Dim lastColID As String

  'this holds the value of 'Target' from the Start Worksheet_Change() event

Dim theTargetValue

Private Sub Worksheet_Change(ByVal Target As Range)

  'calls Sub ProcessOneSheet() below

  'once for each sheet that needs to be processed

  'prior to the call the variables declared above

  'are set up for the specific sheet.

  '

  'did did (all of) the change(s) take place in the proper row?

  If Target.Row <> startDataRow Then

    Exit Sub

  End If

  'and did just one of them change?

  If Target.Cells.Count > 1 Then

    Exit Sub

  End If

  'was the value simply deleted with [Del] key?

  If IsEmpty(Target) Then

    Exit Sub

  End If

  'save the value of Target for comparison at the sheet

  'level so we only update rows where it matches properly

  theTargetValue = Target.Value

  'work through all worksheets in the workbook, identifying those

  'that have the key phrase text ("POD ") in the key phrase cell (B3)

  For Each anyPODSheet In ThisWorkbook.Worksheets

    'this phrase must start at the left-most part of the entry

    If InStr(UCase(Trim(anyPODSheet.Range(sheetKeyPhraseCell))), UCase(Trim(sheetKeyPhraseText))) = 1 Then

      'it appears to be a "t_#" type of sheet

      firstPODRow = Range(sheetKeyPhraseCell).Row + 1

      'assumes that the key phrase cell is directly above the first

      'entry in the 3-column groups of data

      'so add 1 to that column number to get the first column # for the "B4", "D16" type entries

      firstColID = Left(Range(sheetKeyPhraseCell).Offset(0, 1).Address(True, True), _

       InStr(2, Range(sheetKeyPhraseCell).Offset(0, 1).Address(True, True), "$") - 1)

      lastColID = anyPODSheet.Cells(firstPODRow, Columns.Count).End(xlToLeft).Address(True, True)

      lastColID = Left(lastColID, InStr(2, lastColID, "$") - 1)

      Call ProcessOneSheet

    End If

  Next ' end anyPODSheet loop

End Sub

Private Sub ProcessOneSheet()

  'this will process a sheet to update counts

  'based on values of the Public constants defined

  'above as set up by the calling routine

  'the sheets must be unprotected for this to

  'function properly, each could be unprotected/protected

  'within this module if required.

  '

  'Define local working variables

  Dim colPtr As Integer

  Dim lastPODRow As Long

  Dim rowPtr As Long

  'make sure that at least the worksheet has been defined

  If anyPODSheet Is Nothing Then

    Exit Sub ' do nothing, don't know what sheet to work with

  End If

  With anyPODSheet

    lastPODRow = .Range("A" & Rows.Count).End(xlUp).Row

    For colPtr = Range(firstColID & 1).Column _

     To Range(lastColID & 1).Column Step columnGap

      For rowPtr = firstPODRow To lastPODRow

      'modified to also test if the numeric value for this 3-cell

      'group matches the value that was entered on the Start sheet

      'only updates counts if it does

        If Len(Trim(.Cells(rowPtr, colPtr))) > 0 _

         And .Cells(rowPtr, colPtr).Offset(0, -1) = theTargetValue Then

          .Cells(rowPtr, colPtr).Offset(0, 1) = _

           .Cells(rowPtr, colPtr).Offset(0, 1) + 1

        End If

      Next ' end rowPtr loop

    Next ' end colPtr loop

  End With ' end of anyPODSheet processing

  'release resource back to the system

  Set anyPODSheet = Nothing

End Sub

Answer 2

Hi JLLatham 

this is the file:Test-l-test-POD4(M).xlsm

Answer 3

It's always a big help to be able to actually examine a workbook.  You can't upload directly into a posting here, but you can upload to one of the free file-sharing sites and then post the link to the file that they provide.  If you use something like OneDrive, make sure the file is shared:

MediaFire: http://www.mediafire.com

Windows OneDrive: http://onedrive.com

FileFactory: http://www.filefactory.com

FileSavr: http://www.filesavr.com

FileDropper: http://www.filedropper.com

RapidShare: http://www.rapidshare.com

Box.Net: http://www.box.net/files

Answer 4

Hi JLLatham

and thank you for your reply.

is there any way that I can upload a sample Excel so you can see how it work?

the whole workbook is conditional, and the highlighted cells are a conditional as well, there condition is that if the cell(B4:B23, E4:E23, H4:E23, K4:K23) value is equal to the value in cells(E8:H8) in sheet 1(the picture that I put for example is from sheet 2) then they going to be highlighted.

and the cell L4 is conditioned that if the cell before it K4 is value equal to the cells(E8:H8) in sheet 1, then show the position of the cell(it was on column E, but I moved it) on the current sheet, as it shows that the position is E4.

cell M4 is conditioned that if cell before it L4 shows a value then show "1"(the x before the 1 is created by cell formatting to show x before the Number that will appear).

so you see if I could upload or send a sample Excel so that will be more easy to understand the way the workbook works

and yes I need it to happen for all rows(cells on the same column starting 4 to 23).

Thank you

Answer 5

I'm a bit confused at this point but we can discuss this.  And it most definitely would be a VBA solution.

Now, VB cannot detect when a worksheet formula comes up with a new answer.  So in the case of L4 we cannot detect when it changes, BUT you said that L4 is dependent on K4.  If the entry in K4 is made by a person typing from the keyboard or from some VB/macro then we can detect that change.

We need you to 'fine tune' your description a little bit.  I don't understand, for instance, what you mean here "if cell K4 is highlighted or cell L4 shows a value(cell L4 is a result of K4), I need a cell next to them that counts how many time ether of them comes up." 

First, L4 is next to K4 and you've said you want a cell next to them.  Exactly WHAT cell do you want to use?

Second, what do you mean by "of them comes up".  Does that mean when K4 changes to a different non-zero value?

Also, you mentioned shaded cells - in the real world are they going to be shaded, or was that just for showing us where the cells were while asking the question here?

So you need to describe what happens and what needs to happen kind of like this:

When the value in cell B4 changes to a non-zero value, Cell C4 should increment by 1, displayed as "x #" where # is the count of changes.

Repeat this for each cell on the row: tell which one to 'watch' for a change, and which one should show the count of changes for it (and whether or not changing to an empty cell as when someone might hit the [del] key would be included in the count).

Finally, do you need this to happen for all rows starting at row 4 on down the sheet, or just for row 4?