Posts by Cobstillaware

Hi. Thanks for your post. Sorry, but I’m a little confused, or perhaps a haven’t been clear. I don’t know what to do with any of these worksheet formulas, yours or mine. (Please see attachment.) They all work perfectly and do roughly the same thing, which I don’t need, i.e., identify the values of the odd cells in BaseRng. None of them do the thing I do need, i.e., identify the cell locations so the formula will work in a range name definition. I test this by putting the formula in the “Refers to:” field in the “New Name” window, then give it a name. I then type that name in the Name Box of the worksheet and press Enter. If the cells I have shaded blue are not selected, it’s not working. As the macro (a change procedure attached to “Input”) indicates, I want to put calculation results in cells offset to the right of the odd cells in BaseRng.

Regarding the sample workbook I posted, you said there were many comments to be made. Please share; I know I could use all the help I can get.

Thank you for all your help. Excel forum question sample NIC 2.xlsm

Rory – I just opened up Ozgrid and saw your post as I was copying and pasting the following which I set up in a Word document. I agree; it’s a good idea.

Hello, again. I think I have a solution, inspired by your macro. After looking at it again, it occurred to me to put something like it in my own macro. Here is the original action part of my macro:

Dim r As Range

indx = 1

For Each r In Worksheets("Nutrients Consumed").Range("odd-cells named range").Offset(0, j)

r.Value = r.Value + (arstrt(1, indx) / 100) * NewValue

indx = indx + 1

Next r

r and k are the only variables pertinent to our concern; the others have to do with where the data comes from. Here is the modification:

Dim r As Integer

Dim k As Range

Set k = Worksheets("Nutrients Consumed").Range("BaseRng")

For r = 1 To k.Rows.Count Step 2

k.Cells(r, 1).Offset(0, j).Value = _

k.Cells(r, 1).Offset(0, j).Value + (arstrt(1, indx) / 100) * NewValue

indx = indx + 1

Next r

In other words, instead of looping through every cell in the new odd-cells named range, I simply loop through every odd cell in BaseRng and skip the named range altogether. Any suggestions for improvement are welcome. Also, for future reference I’d still like to know if all array formulas will not operate for a Named Range definition, or just the one I was trying to use and why.

Sorry, everyone. I forgot to push "reply" for the above. The following I just wrote. Thanks for your patience.

Hi, Carim. Attached is a small imitation model of my project that you asked for. I would like to keep the real information under wraps for now, so this is a pretend tool for calculating genetic content of one drop of blood from various animals. (This is the phoniest science you’ll encounter this week.) Numbers in “Input” are multiplied by numbers in “Data”, and the results are displayed in the appropriate cells in “Genetic content”. The message boxes are for testing and not part of the final workbook. Let me know if anyone sees where I could make improvement.

I tried copying your formula (from post #9) to a cell, and it returns only the first value. The formula from post #12 returns all the correct values (horizontally), but I can’t get it to work in a range name.

Thanks, Pecoflyer, for your post. Sorry, but I just don’t understand arrays yet. I’ve read several times that CSE is no longer necessary. But I read just as often that it is. In any case, I want others to be able to to use this on their own machines. And what is AFAIK? Thanks for you help.

And thank you, Rory. The idea is working beautifully. But I’m not sure what you mean in #18. It’s been my experience that many formulas, when used on a worksheet, return values. When those same formulas are used as range name definitions, they identify the ranges those values occupy.

Excel forum question sample NIC.xlsm

Rory – I just opened up Ozgrid and saw your post as I was copying and pasting the following which I set up in a Word document. I agree; it’s a good idea.

Hello, again. I think I have a solution, inspired by your macro. After looking at it again, it occurred to me to put something like it in my own macro. Here is the original action part of my macro:

Dim r As Range
indx = 1
For Each r In Worksheets("Nutrients Consumed").Range("odd-cells named range").Offset(0, j)
r.Value = r.Value + (arstrt(1, indx) / 100) * NewValue
indx = indx + 1
Next r

r and k are the only variables pertinent to our concern; the others have to do with where the data comes from. Here is the modification:

Dim r As Integer
Dim k As Range
Set k = Worksheets("Nutrients Consumed").Range("BaseRng")
For r = 1 To k.Rows.Count Step 2
k.Cells(r, 1).Offset(0, j).Value = _
k.Cells(r, 1).Offset(0, j).Value + (arstrt(1, indx) / 100) * NewValue
indx = indx + 1
Next r

In other words, instead of looping through every cell in the new odd-cells named range, I simply loop through every odd cell in BaseRng and skip the named range altogether. Any suggestions for improvement are welcome. Also, for future reference I’d still like to know if all array formulas will not operate for a Named Range definition, or just the one I was trying to use and why.

PS - Carim

Carim, I was afraid of that. To be sure, do you mean that all array formulas will not operate for a Named Range definition, or just the one I’m trying to use?

The formula you sent is a little beyond my skill level, so I’ll have to spend some time studying it. But for now, thank you for your valiant effort. I’ll try to make contact again in a day or so.

Thanks again for your fast response.

Sorry if I wasn’t clear. BaseRng is already dynamic. I am using this definition;

='Nutrients Consumed'!$BE$21:INDEX('Nutrients Consumed'!$BE$21:$BE$400,MAX(IF('Nutrients Consumed'!$BE$21:$BE$400<>"", ROW('Nutrients Consumed'!$A$1:$A$380))))

I’m trying to avoid OFFSET because I’ve read that it can slow down a workbook. Does this volatility occur with range names and macros, or is it an issue with worksheet formulas only? I can post this question in a new thread if you think I should.

Regarding your last paragraph, I need only one range extracted from BaseRng, the odd cells. An array formula doing that is exactly what I’ve been trying to find. I don’t need this new, odd-cells formula to be dynamic (updating automatically when new data is added) because it is based on BaseRng, which is already dynamic. I’ve tried quite a few. After entering the formula into the “Refers to:” field in the New Name window, I enter CSE. It doesn’t add the curly brackets. And when I try to add them with the keyboard, Excel changes it to ="{=INDEX(BaseRng,SEQUENCE(5,,,2))}", with the quotation marks and the additional equals sign. Neither of these correctly select all the odd cells when I enter the name in the name box. I’m beginning to think that Excel Names will not accept or recognize array formulas.

The structure of the workbook is this; numeric data is entered into cells on the first sheet. An event procedure causes a calculation with the numbers and a small database on the second sheet. Excel then enters the results of those calculations into the appropriate cells to the right of the odd cells in BaseRng on the third sheet. If it will help, I can create a small sample workbook that may illustrate what I’m trying to explain.

So, to summarize, if you can help me do what you said in your last sentence, have an array formula (used as a range name definition) that will select out the odd cells in BaseRng (remembering that BaseRng is dynamic, and I only need one new formula), that would be a major step toward finishing this project. Thank you so much for your time and effort on this.

Bravo, Carim! Thank you so much for your quick response. I didn’t know a macro could be used in that way. However, I see in Name Manager that the resulting definition is a series of individual cell references. I already have that. After Excel adds a sheet name to all fifty references, I have a definition 1,410 characters long. (I wish I had your macro when I created that range name. It would have saved me time.) And it’s not fully dynamic. If I resize or move BaseRng, I have to remember to run the macro to update the new range name. That’s no great hardship, of course, but I was hoping to have all my range names dynamic. In any case, I’m still baffled as to why the concise formulas I mentioned, when entered into a cell, can find the correct cells and return their values but cannot find those same cells when used as a name definition. Other formulas that I’ve used in the past do both perfectly. I’d like to know for future reference so I can be more self-reliant using Excel. If it’s not possible, so be it; I can live with it. But I don’t want to miss out because I gave up too soon.

Many thanks for your expertise and time. I will add your macro to my library of valuable procedures.

My situation: I have a column of 100 cells listing 50 nutrients (vitamins, etc.), two cells per nutrient. I want to use the first of each pair in a macro. The 100 cells I have named “BaseRng”, and it is dynamic because the list may change. I’ve been struggling to find a formula that will select out the odd-numbered cells in BaseRng, and give it a name that will work in a macro. I’ve found several that work beautifully as worksheet formulas (I stick it in a cell and it returns the values in the 1^st, 3^rd, 5^th etc. cells). But when I put the formula in the “Refers to:” field in the New Name window (with absolute referencing), and then type the name in the name box on the worksheet, sometimes nothing happens. The best I get is the 1^st cell in BaseRng is selected. Why is this happening, and how can I name all the odd-numbered cells in BaseRng? I’m trying to avoid OFFSET because of the volatility.

Following are some formulas I’ve tried on a smaller, experimental range of ten cells. They all work as worksheet formulas. As definitions for a range name, the first one selects only the first cell; the other two do nothing.

=INDEX(BaseRng,SEQUENCE(5,,,2)) I like this one. It’s compact and independent of row numbers.

=IF(ISODD(ROW(BaseRng)), BaseRng,"")

=IF(MOD(ROW(BaseRng),2)=0, BaseRng,"")

I’ve spent hours searching the internet, to no avail. Please, I would be very grateful for any help. Thank you.

Can I assign two macros to the samebutton/shape so that when I click it one action occurs and when Iclick again the other happens, like a simple push-button light switchto alternately hide and unhide a row? Thank you.

Re: Achieve morphing visual changes

Thanks for your response, Grimes0332. Attached is a scaled-down sample of what I'm trying to do.

I'm building a production report. Ithas a pair of macros that hide and unhide a row containing a linechart. Is there a way to make the change visually more of a smooth,stretching, organic, morphing kind of growing rather than the abrupt,sudden, jerky displacement that I see now? I'm building this at homewith Excel 2016. It must function in Excel 2007 on the workmachines. Thank you.

Re: Find multiple averages simultaneously-fixed rows, variable columns

Thanks for your response, Pike. I'm still having trouble attaching. I've followed precisely the first four steps that you have outlined, and the file appears in the upper box. But it never appears in the bottom box, as in Step 5. When I click "Done" anyway I get the message "Please drag & drop an existing attachment on to 'Drag Files Here' or add a new one by pressing 'Add Files' ". But I can't drag anything. I wrote the following before I tried attaching.

The attached file is an abbreviated version. I cannot really finish building the spreadsheet without solving this problem. You'll see five horizontaldata fields. Eventually there will be five more. The first four –the bigger ones – have color-coding for explanation purposes. The four colors are light purple, green, blue, and darker green. Assume that the cells in the rows that have the vertical fill pattern (rowsJ – N) are the current cells being averaged. The average for each data field would be displayed in the cell having the same color (D:5,D:20, D:38, D:56) as its rows. In cell D:20 I have entered the actual formula that I would probably use if I had to do this only once.

I need to be able to do something like click on the blue shape labeled “Reset All Ave”, then select a new range of columns using the letter headings (I've tried to imitate the gray highlighting you would see with the diagonal fill pattern in columns T – X.), and then click “Execute”. The new averages (of the cells belonging to both the color-coded rows and columns T –X) would appear in the average cells.

So far I've investigated the AVERAGEIF function, filtering, and a macro chain.

Thanks.

I'm building a production report inExcel. It has eight horizontal fields of data containing dailyproduction numbers reaching from column E or so to the very right endof the spreadsheet (XDF). I need to find a way to find the averageof particular rows in each field within a particular range ofcolumns. Easy enough. But I must be able to change the range ofcolumns from time to time, and I must be able to find the eightaverages simultaneously. And it must be easy to do. (It's for myboss.) These averages will be displayed in a fixed set of cells,each corresponding to its data field.
I'm new to forums, and am unfamiliarwith protocol. So I have been trying for 15 minutes to drag a screenshot file into the "Drag previously uploaded files into this area"showing a couple of the fields, edited for clarification. If youlook at the screenshot, I'm trying to find a way of finding theaverage, in each field, of the cells belonging to both a preselectedand fixed set of rows (highlighted in blue)and a newlyselectedand variable set of columns (highlighted ingrey to mimic the selecting using the letter headings); I needto change the range of columns from time to time. These eight averages will be displayed in a fixed set of cells eachcorresponding to its data field. I imagine that the user couldclick on "Reset Average" (lower left), select a range ofcolumns based on the dates in row 3, and then click "Excecute". In the attached demo the new averages would be displayed in cells C48and C64.
Regarding the reset buttons,obviously I have to find a way to type text ("Reset Average")into the cell without losing the formula imbedded in it. Itwould be a nice touch if the buttons had a 3D look and respond insome visual way (like sinking back into the sheet) when clicked.
I've been struggling with this forweeks, looking at AVERAGEIF, macros, even trying to teach myself VBAcode. Can you help, please. Thanks.