Need macro to display information based on given data

[mikester]

Hi

I am sure there is a way to do this, just need help in figuring it out.

I am given the info in yellow in my example. So I have to enter information into a system but need the starting points (these are in a group of 24) to enter them.

First, I am given Start and End numbers of DLs. In the attached example I am building members 1 - 312. Based on that, I need 13 entries in this example (312/24 = 13). So I need my DL list (in green) to be 1, 25, 49, 73, etc, and stop and 289 (since 313 would be next, and I only need 312).

Here might be a tricky part. I also have to include PMs (which are also given). But if I am told to Skip members I have to skip numbers x97 - x100. If not then I can include them in my list.

Please see my attached example, the expected results are in Green.

Please let me know if this makes sense or if you have any questions.

[mikester]

Am unable to attach file. Maybe because I just signed up? How long once I activate can I post?

[bebo021999]

Are you able to attach a screenshot at least?

[mikester]

Still can't attach a screenshot or my file. Didn't know it would be an issue!!

Need something that will generate the DL and PM lists based on the info given

Appreciate the help.


GIVEN: Example 1 (Skipping PMs)

DL Mbr Start
1

DL Mbr End
312

PM Mbr Start
2001

PM Mbr End
2295

Skip PMs
Yes
Skips x097-x100

Expected Results

DLs PMs
1 2001
25 2025
49 2049
73 2073
97 2101
121 2125
145 2149
169 2173
193 2197
217 2200
241 2224
265 2248
289 2272



GIVEN: Example 2 (No Skips in PM)


DL Mbr Start
1

DL Mbr End
312

PM Mbr Start
2001

PM Mbr End
2312

Skip PMs
No


Expected Results
DLs PMs
1 2001
25 2025
49 2049
73 2073
97 2097
121 2121
145 2145
169 2169
193 2193
217 2217
241 2241
265 2265
289 2289

[mikester]

Attaching file.

[bebo021999]

In 1st expected results, I could see DLs and PMs has grown with step 24, except D10 to D11, step 3.
Is it from PMs cell A15 = "skip x097-x100 " condition?
How 2197 - 2200 belongs to this criteria? because that conditon is 1097 or 2097 or 3097? not 2197?

[protonLeah]

Named ranges:
DL_Mbr_End =Sheet1!$A$5
DL_Mbr_Start =Sheet1!$A$2
PM_Mbr_End =Sheet1!$A$11
PM_Mbr_Start =Sheet1!$A$8
Skip_PMs =Sheet1!$A$14
NumEntries =Sheet1!$F$1

SkipStart =Sheet1!$A$15
SkipEnd =Sheet1!$A$16

** Data validation in A14 ----> Yes, No **

calculate DLs (C2):

Please Login or Register  to view this content.

For PMs,
D2:

Please Login or Register  to view this content.

D3:D_n:

Please Login or Register  to view this content.

[mikester]

Bebo --- I apologize. I should have looked at the numbers more closely in my first example. I want to skip 97-100, 197-200, ....., 997 - 1000, 1097 - 1100, ----- 2097 - 2100, etc.
Correct values should be:
1 2001
25 2025
49 2049
73 2073
97 2101
121 2125
145 2149
169 2173
193 2201
217 2225
241 2249
265 2273
289 2301



Leah - thanks, I'll check it out.

[mikester]

Leah --- thanks very much, I need a couple of adjustments the more I think about this. Won't always be 13 entries per say, could vary depending on the order.

I see what you are trying to do with the Name Ranges, but I was hoping that I could just enter the numbers in the yellow parts and it would adjust accordingly. So if I only need 96 members in DL, my DL list would just stop at entry 73 (since the next entry would be 97 and I don't need that one).

Also, sorry I didn't make this clear but if I skip PMs it just won't be 2097 - 2100. I meant like 97 - 100, 197-200, 297-300, ------ 1097 - 1100, 1197 - 1200, ----- 2097 - 2100, 2197 - 2200, etc.

Thanks again for the help. Sorry if I didn't make myself clear at the begining.

[protonLeah]

v.2 Modified to determine number of entries based on start/end calculations.

C2:

Please Login or Register  to view this content.

D2:

Please Login or Register  to view this content.

Counts are reported in F2:F3

I'm not sure about the "skip" option...

[bebo021999]

It requires:
A14 = Yes or No
A15 contains limit range, end with 000-x000 (with "000" is 3-digit number)

Please Login or Register  to view this content.

[mikester]

Leah and Bebo--- thanks, it is so very close! Thanks for all the help and info!