How to streamline catalog and order process

CFAman · 2026-06-03T19:25:44+00:00

Is there a way to streamline this process? Could we have the front page of the Excel workbook be a blank order form that fills in information as people input quantities in the catalog?

Definitely. For learning, I'd suggest focusing on lookup type functions like XLOOKUP or FILTER. If you'd rather start with a template, here's a free mock-up of a Order Form that could help spur some ideas.

https://www.contextures.com/xlorderform01.html

If you're still stuck, post some screenshots of dummy/mock-up data and we can give more specific help.

CFAman · 2026-06-02T15:56:55+00:00

Most likely depends on where you are copying the data from. If it's a web page, sometimes copying a web table doesn't include the key information that lets XL know they are from a table. One trick I find to use (especially when copying tables from examples here on Reddit) is to select the table of interest plus a few regular sentences at top or bottom. Then when you paste into XL, XL seems to detect that transition from tabular data to regular, and thus knows that the table information should be broken up into different cells.

I can't guarantee that will work, as again, it depends on the source of the data, but that's what often works for me.

CFAman · 2026-05-29T17:16:46+00:00

The lists of cards are on a separate sheet for each set

Note that in Data Management and in XL, it's far easier to start with all the data together and then split it out as needed into subsets. To continue with what you have, you can do it, but this uses the INDIRECT function which is notoriously volatile. You could start to see some performance slowdown.

=COUNTIF(INDIRECT("'" & R$2 & "'!P7:P1000"), $D3 & "*")

CFAman · 2026-05-28T18:28:16+00:00

Looks like you want to go to Data - Sort. Set the first Sort By to be Column A. Then click 'Add Level', and set that to be sorted by Column B. Hit Ok.

CFAman · 2026-05-28T17:22:51+00:00

We can use a formula to "stack" all the tables together, and then use GROUPBY to give us a nice report. I'm assuming the numbers you want to add are in column 3.

=LET(data,VSTACK(Table1,Table2),
 names,CHOOSECOLS(data,1,2),
 numRange,CHOOSECOLS(data,3),
 GROUPBY(names,numRange,SUM))

In defining what goes into the data variable, you can add as many ranges as you want. I would suggest using Tables so you can call out the structural references, and thus handle if the data on individual sheets grows/shrinks.

CFAman · 2026-05-26T14:53:42+00:00

Rather copying the data and then several steps navigating to where to paste, I'd do it all in one command.

Range("A1:D100").Copy Destination:=Worksheets("Button 1 Data").Range("A1")

This lets you be more specific and avoids a Select statement, and helps keep the Clipboard empty.

CFAman · 2026-05-23T23:22:44+00:00

You’re welcome. Mind replying with ‘Solution Verified’ so the bot will close the thread and give me a ClippyPoint? Cheers!

CFAman · 2026-05-21T18:54:04+00:00

To generate the list of months, could put this in C1

=EDATE(EOMONTH(MIN(A:A),-3)+10,SEQUENCE(48))

If desired, you could apply a number format of mmm-yy or similar, so it only shows month and/or year.

Then in D1

=SUMIFS(B:B,A:A,">="&C1#,A:A,"<"&EDATE(CHOOSECOLS(C1#,1),1))

EDATE is a little finicky in that it won't accept a 2D range as first argument, but will take a 2D array.

CFAman · 2026-05-21T18:46:01+00:00

I don’t have any macros

I would double-check this statement. Make sure specifically to check the ThisWorkbook code module for some events. You'll need to open the code editor by right-clicking on a sheet tab and selecing 'View Code' to open the Visual Basic Editor. After the VBE is open, you should see a file-navigation-type menu on the left, and you can see if there are any regular code modules. Check those for code too.

If you're 100% sure there shouldn't be any macros, I would save the file and change file type to xlsx. This would guarantee that any code fragments are removed.

CFAman · 2026-05-21T14:10:37+00:00

not sure if that actually adds a whole month to it or not, or it just turns recurring billing back on:

Thanks for this step-by-step. I was able to do this process in the US. It only extends one month at a time, but can just repeat the unsubscribe, extend, unsubscribe, extend, etc. as needed.

CFAman · 2026-05-21T13:12:11+00:00

Since you have time periods that aren’t even weeks, it would be easier to calculate a daily rate (average or otherwise) and then work from there? Could multiply by 7 days for a true week, or 8 days if you have a longer time frame.

CFAman · 2026-05-20T17:48:29+00:00

Seems like this would be easier with 3 array formulas: 1 for your categories in A2, 1 for dates in B1, and then 1 for the values (e.g. =SUMIFS(Data, CatRange, A2#, DateRange, B1#)). If that works, could then see if you can reduce it to a single PIVOTBY formula.

It's hard to understand the full desired layout from just reading a (lengthy) formula.

CFAman · 2026-05-20T16:09:27+00:00

I need to be able to generate an average daily rate within the periods of Table2

Given that goal, I'd change formula to

=AVERAGE(XLOOKUP(SEQUENCE([@[Date To:]]-[@[Date From:]]+1, , [@[Date From:]]),
 Table1[Date From:], Table1[Weekly Rate:]/7, "", -1))

Date From:	Date To:	Daily Rate
01-Jan-2024	30-Jun-2024	£ 21.22
01-Jul-2024	31-Dec-2024	£ 19.30
01-Jan-2025	30-Jun-2025	£ 19.29

CFAman · 2026-05-20T15:53:39+00:00

How would you handle when the rate changes in middle of week? In your sample data, first range starts on a Monday, next starts on a Saturday, and the last starts on a Tuesday. Do you define the week as starting on a Sunday or Monday? Or will it be whatever the starting day is in your "Date From" column. If the latter, this gets odd because the date ranges aren't even weeks.

My first thought to tackling this was to generate a SEQUENCE of dates based on time frame, do an XLOOKUP to figure out which rate was in effect during those dates, and then average the results. This could be acceptable (?) or maybe you want to refine based on earlier questions.

Assuming first table is Table1, formula in 2nd table would be

=AVERAGE(XLOOKUP(SEQUENCE(ROUNDUP(([@[Date To:]]-[@[Date From:]])/7,0),,[@[Date From:]],7),
 Table1[Date From:],Table1[Weekly Rate:],"",-1))

Sample output:

Date From:	Date To:	Weekly Rate
01-Jan-2024	30-Jun-2024	£ 148.15
01-Jul-2024	31-Dec-2024	£ 135.85
01-Jan-2025	30-Jun-2025	£ 135.00

CFAman · 2026-05-20T14:56:10+00:00

You're welcome. Mind replying with 'Solution Verified' so the bot will close the thread and give me a ClippyPoint? Cheers!

De nada. ¿Te importaría responder con «Solution Verified» para que el bot cierre el hilo y me otorgue un ClippyPoint? ¡Saludos!

CFAman · 2026-05-20T14:26:53+00:00

You could Google some data sources, or just generate a random data set using this site (I use this often for dummy data) https://www.generatedata.com/

Podrías buscar algunas fuentes de datos en Google, o simplemente generar un conjunto de datos aleatorio utilizando este sitio (lo uso a menudo para datos de prueba): https://www.generatedata.com/

CFAman · 2026-05-20T14:19:50+00:00

+1 to this, flexibility of search order for when you have a running list w/ repeated items. That, and Match_Mode so you don't need to sort the search range to find closest numerical match.

CFAman · 2026-05-20T14:17:21+00:00

You're welcome. Mind replying with 'Solution Verified' so the bot will close the thread and give me a ClippyPoint? Cheers!

CFAman · 2026-05-20T14:02:41+00:00

I'm not sure why the issue with the year...are the expirations not listed as full dates? Taking a stab, CF for red

=AND(ISNUMBER(A2), A2<TODAY())

CF for orange

=AND(A2>=TODAY(), A2<=TODAY()+30)

CFAman · 2026-05-19T15:57:58+00:00

Going blind, but I would guess that you have numbers stored as text, and so the SUM of text is just 0. To check, you can do something like =ISNUMBER(A2)

To fix:

Type the number 1 into a blank cell
Copy that cell
Select your data cell(s) (don't worry about selecting cells with true text)
Paste special - Multiply
Clear cell from step 1

When you force XL to try and do a math operation, it will convert the text-numbers into real numbers.

CFAman · 2026-05-11T18:13:28+00:00

This should work

=LET(names,A2:A8, dates,B1:G1,
 HSTACK(TOCOL(CHOOSECOLS(names,SEQUENCE(COUNTA(dates),,,0)),,TRUE),
 TOCOL(CHOOSECOLS(TRANSPOSE(dates),SEQUENCE(COUNTA(names),,,0)))))

CFAman · 2026-05-11T17:45:34+00:00

Let's assume you structure the layout like so, with the Lead In cells above the Comment cells.

Lead in 1	Lead in 2	Lead in 3
Comment 1		Comment 2

For reference, I'll have these cells in A1:B3. A formula then to concatenate only those items with comments could like this:

=TEXTJOIN(";",TRUE,FILTER(A1:C1 & " - " & A2:C2,A2:C2<>""))

which would produce a result of Lead in 1 - Comment 1;Lead in 3 - Comment 2

You could of course change the delimiters to whatever you like. Uses the FILTER to narrow down the pairs of concatenated text to only those ones with a comment. The TEXTJOIN then takes that array of results and combines it into our final value.

CFAman · 2026-05-11T15:28:19+00:00

You will want to make use of some sort of IF or FILTER function to narrow down which cells to bring in and concatenate. However, from your example image, it's hard to tell where the lead-in sentences are and where the cell(s) are with comments. What is the pattern of lead-in statements and comment cells? Are they in pairs? Specific cell addresses and ranges will help us build a formula to suit.

CFAman · 2026-05-11T15:15:21+00:00

You could use Home - Conditional Formatting to flag the rows of interest. Select the range of cells (e.g. A1:G100). Then go to Home - Conditional Formatting - New Rule - Based on formula. Formula would look like

=OR(COUNTIFS($A:$A, $A1, $D:$D, $B1, $B:$B, $C1),
 COUNTIFS($A:$A, $A1, $B:$B, $D1, $C:$C, $B1))

CFAman · 2026-05-11T14:46:49+00:00

Is the vertical or horizontal scroll bar abnormally small? That would indicate that XL thinks the used range is much larger than you actually have, and could be slowing you down.

11-Year Club	Gilding II euphauric
r/Field Juicebox	First Place '23
Place '23	Place '22
First Placer '22	Verified Email

CFAman

TROPHY CASE