Macro to change formula

[madsklavsen]

Hello everyone,

I am currently working on a project with the following formula:
=IFERROR(SUM(SUMIF ('DataArk + Noter'!$D$13:$D$1204;{"BSH";"MI";"OHJ";"RVM";"TZ"};'DataArk + Noter'!Y14:Y1204))/HVIS(SUM(COUNTIFS('DataArk + Noter'!$D$13:$D$1204;{"BSH";"MI";"OH";"OHJ";"RVM";"TZ"}))="";"";SUM(COUNTIFS('DataArk + Noter'!$D$13:$D$1204;{"BSH";"MI";"OH";"OHJ";"RVM";"TZ"})));"Ingen Data")

The formula returns the average leadtime on a project based on initials of people.


My question is:
How can i create a macro that inserts all the initials of the people from a list of names with the corresponding initials. I want to make the formula dynamic, so if changes/addings in the list of initials, the Macro will insert the new initials

Any help is appreciated.

Thanks!

[southward]

Hi,

You should be able to do this with a Dynamic Named Range.

Step 1: See this link for explanation on Dynamic Named Ranges.
https://support.microsoft.com/en-us/kb/830287

Step 2: Create Dynamic Named Ranges for your list of initials.
Step 3: Replace the group of initials with the "Name".

Hope this is helpful.

Cheers

[madsklavsen]

Hi Southward,

I am not sure i understand. The problem is, that in the formula stated above, i need the initials: {"BSH";"MI";"OH";"OHJ";"RVM";"TZ"}, to be referenced to a cell instead of me typing their initials. This is the case, because i want to be able to edit/add/remove initials from cell references.

E.g.
I have a column with the initials: {"BSH";"MI";"OH";"OHJ";"RVM";"TZ"}, and if i were to remove let's say: "RVM" i want the formula to change to:

=IFERROR(SUM(SUMIF ('DataArk + Noter'!$D$13:$D$1204;{"BSH";"MI";"OHJ";"TZ"};'DataArk + Noter'!Y14:Y1204))/HVIS(SUM(COUNTIFS('DataArk + Noter'!$D$13:$D$1204;{"BSH";"MI";"OH";"OHJ";"TZ"}))="";"";SUM(COUNTIFS('DataArk + Noter'!$D$13:$D$1204;{"BSH";"MI";"OH";"OHJ";"TZ"})));"Ingen Data")

Thanks, for your response. I hope you can help!

Best regards,

[Richard Buttrey]

Hi,

You'd be better off uploading the workbook so that we may see the request in context. There may well be a better way of getting what you want.

Are there some sets of initials that you DON'T want to sum which is sort of implied by your formula and note?

[madsklavsen]

Take a look at it, it's the table on the right!
Thanks!
http://www.filetolink.com/ec9bfcb958

[Richard Buttrey]

Hi,

You'll get more response if you upload to the forum. Many of us prefer not to visit file hosting sites of which we know little.

[madsklavsen]

Is this better?

[southward]

Hi,

Not sure of what results you are looking to get.

I have modified you spreadsheet.
Added helper columns (AJ through AQ) that compress your "Initialer" info from columns (W through AD). Highlighted in Yellow and Green.
Each of those columns have corresponding Dynamic Named Ranges: KTEAM1, KTEAM2, KTEAM3, KTEAM4, RTEAM1, RTEAM2, RTEAM3, RTEAM4

I modified the formulas in cells W5, X5, Y5, Z5. Replaced "{"BSH";"MI";"OHJ";"RVM";"TZ"}" portions with the corresponding Dynamic Named Range.

Columns AJ through AQ will automatically update to reflect the "Initialer" data in the corresponding columns.

Hope this is helpful.

Cheers,

[madsklavsen]

Hi Southward,

Thanks for all your efforts, it seems like a really good solution. However i can't get the dynamic named ranges to work. E.g. in cell H7 in the attached, the formula doesn't count the intials "CL" and returns 0.
Also if i were to use the dynamic named ranges in a datavalidation criteria, it doesn't work either.

Regards,

[southward]

So, in H7, you are looking to count the same initials that are in the array D13:D1204 as are in KTEAM2, which is the initials in array X14:X43.
Is that correct?

The most applicable formula that I can find for such a situation is =SUMPRODUCT(--(array1=array2))
The only problem is that they two array's have to be of equal length.

I am still researching to find a proper solution.

Maybe there is a GURU or two around here that can help find a solution.

Still looking.
Cheers

[GeneralDisarray]

Well, you can do that with an array formula if you change one of those columns to a row. See my example.

When you multiply a column by a row you get a rectangular array - you can multiply that by a constant (1 in this case) and then sum. See attached for an idea to start with.

{=SUM(1*((COLUMN_RANGE)=(ROW_RANGE)))} - this is an array formula (confirmed with cntrl+shift+enter) and the size of the arrays do not have to be the same.

--
Edit: southwards tip about dynamic ranges would work well to actually define the ranges :D I just did a simple example using some fixed ranges. This is a somewhat rare trick, step through the formula using the 'evaluate' feature to see what happens as the formula is evaluated.

[southward]

OK, give this array formula a try.

Please Login or Register  to view this content.

This is an array formula (confirmed with cntrl+shift+enter).

Hope it works for you.

Cheers

[madsklavsen]

Hi guys,

Thanks generaldisarray. I still can't quite get it to work. As i mentioned earlier, it needs to only count the cells with the given initials.
Simply: i want to replace the {"BSH";"MI";"OH";"OHJ";"RVM";"TZ"} with a specific range of cells (could be dynamic named ranges). If this is the case, it is important that the it doesn't count blank cells.

Thanks again guys, appreciate it.

[Richard Buttrey]

Could you state specifically what the Count in H7 should be. As well as telling us what the answer is also note the specific cell addresses that you have used to count that number.

Also clarify what you mean by replace those BSH,MI...ext values. Where are they (I only see BSH in column D)? And how does 'replacing them affect the count?

I can't help thinking that this is one of those requests that would be easier to help with if we knew what it is you're trying to do. By that I don't mean start with the formulae you have and tell us what it is trying to do.

I mean start with a high level description and tell us what the model data represents and what it is you're trying to achieve with it. There may well be a much better way if you're prepared to change the layout of your data.

[xlnitwit]

Hi,

There are two problems with your worksheet as it stands. First, the SUM(COUNTIF()) formula needs to be array entered using Ctrl + Shift + Enter. Second, you need to amend the dynamic ranges to exclude blank cells. For example, KTEAM1 could be redefined as:
=OFFSET(DataArk!$AJ$14,0,0,COUNTIF(DataArk!$AJ$14:$AJ$43,"?*"),1)

Example workbook herewith.

[madsklavsen]

Let me try to reformulate the problem:

Please refer to the file inclosed in this reply.

The sheet included is an underlying datasheet which i use in other sheets. I am using the initials and the team in the data "Konsulenter i Compass" to determine if the initials should be included in the formula. E.g. in cell F8 it counts all of the cells with the initials {"BSH";"MI";"OHJ";"RVM";"TZ"} and it returns 62.

So what am i seeking to do?
I want to replace the {"BSH";"MI";"OHJ";"RVM";"TZ"} with a dynamic range, so if changes appear (e.g. a new consultant is hired at the firm with a set of initials and a assigned team), the formula shouldn't be edited.

Also; i am using these initial-references multiple times and the cell F8 is just an example.

Thanks again guys, let me know if you want me to elaborate on anything else.

Cheers,

[Richard Buttrey]

Hi,

If the intention is to count the number of initials for a particular team that is defined by the table in U13:U31 then the answer for Team 1 is 42 not 62

In which case I'd address the problem from the other end. In a helper column - say N, in N13 enter

Please Login or Register  to view this content.

Now the single formula will be

Please Login or Register  to view this content.

assuming that D3 contains the Team number you're looking to total.

[madsklavsen]

But this doesn't make it anymore dynamic. What if i were to add another consultant? The formula wouldn't work.

[Richard Buttrey]

Why not?

Create a dynamic range name for the current fixed range V14:V31 and use that name in the formula.
i.e.

Define a range name 'Consultants' and define it as

Please Login or Register  to view this content.

then the helper column formula is

Please Login or Register  to view this content.

Or if you don't want a dynamic range name for some reason simply specify a sufficiently large range, say V14:V1000 to cover the maximum number of consultants you'll ever need

[madsklavsen]

But then it will count the blanks as well right?