Copying a block and incrementing a row by 1.

[ken800]

I can't install the tool to upload a sheet on my work PC so an image will have to suffice. Here's the jist: I have data coming from multiple sheets that I vlookup and format into a summary sheet. It is always the same number of rows like the below example. I want to be able to copy A3:C7 and paste it in A8. A4 is simply ='sheet2'!E4 and then copied down. I want to change the formula in A8 to when I copy the block I don't get ='Sheet 2'!E8 but instead increment it just one so the second block of picks up the NEXT number in the list -- i.e. 'Sheet 2'!E5. Copy the block again and pick up E6 and so on... I've spent quite a bit of time using offset, indirect, and other methods but haven't figured it out.

 

[awoohaw]

so you want the top 4 rows from sheet to sheet, is that right?
Where do you get your sheet name from? Do you have a table of sheet names somewhere? or do you use default sheet names and will NEVER change them?
If you do change them is there a convention that keeps it consistent like 202212 202301 202302 (that is consistent but hard to build formulas on, but can be worked with).
(So maybe a table of your sheet names may be a start including ones in the future).

 

[ken800]

so you want the top 4 rows from sheet to sheet, is that right?
Where do you get your sheet name from? Do you have a table of sheet names somewhere? or do you use default sheet names and will NEVER change them?
If you do change them is there a convention that keeps it consistent like 202212 202301 202302 (that is consistent but hard to build formulas on, but can be worked with).
(So maybe a table of your sheet names may be a start including ones in the future).

Let me add more color. Essentially I create a report for part numbers. Data comes from other worksheets that are generated and I paste them into a tab. They are the same format and same location every time. In the example above, that is sheet 3 which is a raw output of a BUNCH of data -- 90% of it isn't needed. So let's say my boss says give me an analysis on Part 6836-2. In my example above, I'm just pulling inventory from one sheet. In my actual job, I pull multiple data sets from several spreadsheets and fill in a rather comprehensive grid that is 13 rows high and 30 columns wide.

What I'm trying to do is two parts. The first part is to be able to post a block of part numbers in my "part numbers" tab. Then in my "report tab", The first block of data will always look at the same first cell in my "part numbers" tab. Let's call it A1. Then I will copy down however many blocks I need to copy to create an information block for each part number. The information blocks contain dynamic xlookups, vlookups, and if/thens which copy down just fine. So let's say today it's a dozen parts. I'd copy the main block in my reports eleven times down (12 including the "starter") and I want the first upper left cell in each block to read the NEXT part number in the list rather than row 1, 14, 27, etc.

The second part would be some kind of macro or formula to copy the block down automatically based on how many part numbers I paste in my list of parts to analyze. I suspect a hidden helper column to get a count would be required but I haven't even begun to investigate that part yet and don't even know if it is posible.

Since I always know the starting point of the FIRST part for both the report sheet and the part numbers sheet, i could go get that cell location then modify from that formula going forward. Or then could all be the same formula with the first looking at row "1", second looking at row "1"+1, third looking at row "1"+2 where that last number increments just one with each copy/paste of the block.

 

[awoohaw]

So you aren't doing this in batch type setting. You copy data into a new worksheet, name the worksheet, then go to your master to input the formula?
I' don't understand why you have to start a new block then if the one worksheet has only one part number? (Or do I have that wrong?).

If I do have it wrong, are the number of identical part number records consistent? Can you use helper cells/columns? Have you considered VBA? (I am not proficient in VBA so I hope someone else chimes in on this thread if you want to go that way).

 

[Skyybot]

Not a lot of info but give this a shot. Paste it into ThisWorkbook.

Sub skip4()
x = 2
y = Sheets(2).UsedRange.Rows.Count
For i = 2 To y
    For x = x To x + 3
        Sheets(1).Cells(x, 1).Value = Sheets(2).Cells(i, 1).Value
    Next x

Next i

End Sub

 

[Alex Blakenburg]

You might want to update your profile to show what version of Excel you are using since solutions can vary based on the version.
Another options would be to use Power Query.
You would need to convert both Sheet2 & Sheet3 into a Excel Tables.
I have called them
• tblPartNos (Sheet2)
• tblPartQty (Sheet3)

I can talk you through it if it is of interest.

Loading the tblPartQty is only PQ step

Loading the tblPartNos and merging it with the above and unpivoting it is simply clicking a few more buttons.

Code generated for those steps is:

let
    Source = Excel.CurrentWorkbook(){[Name="tblPartNos"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"Part Numbers"}, tblPartQty, {"Material #"}, "tblPartQty", JoinKind.LeftOuter),
    #"Expanded tblPartQty" = Table.ExpandTableColumn(#"Merged Queries", "tblPartQty", {"US", "ASIA", "SA", "Europe"}, {"US", "ASIA", "SA", "Europe"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded tblPartQty", {"Part Numbers"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Part Numbers", "Part Number"}, {"Attribute", "Location"}, {"Value", "Stock Quantity"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Part Number", type text}, {"Location", type text}, {"Stock Quantity", Int64.Type}})
in
    #"Changed Type"

 

[ken800]

So you aren't doing this in batch type setting. You copy data into a new worksheet, name the worksheet, then go to your master to input the formula?
I' don't understand why you have to start a new block then if the one worksheet has only one part number? (Or do I have that wrong?).

If I do have it wrong, are the number of identical part number records consistent? Can you use helper cells/columns? Have you considered VBA? (I am not proficient in VBA so I hope someone else chimes in on this thread if you want to go that way).

Call my "report" the master file. It will be 1 to N blocks of data, each repeating exactly as the previous but with different data. The N blocks of data is determined by how many part numbers I am going to process. It's usually 5 to about 25 but rarely the same numbers and almost never the same quantity. Once a week the file can grow into the thousands but I'm not doing that until I can automate this a bit more. The formatting of the report sheet is always the same including conditional formatting. The format of the part numbers sheet is always the same. Part numbers are listed down the left side starting in A6 and down from there. The data is a dump from three different systems into excel format. I paste these each into a tab with the same name every time. The process goes like this: Clear all but the first report block on the report tab. Move to the part numbers tab and paste in the list of parts that I'm going to report on. Clear the next three tabs but leave the headers and copy/paste the raw data output from the other systems into each of their own always-the-same-name tab so I can do lookups against the data.

Now I go back to the report tab. I copy the block down N number of times for however many parts I have in my parts tab. It copies the first part number down the left side for 13 rows then v or x lookups data from the three different tabs with some concats and ifs. The lookups copy down just fine as they are looking for specific data that matches and I have various ifs in case the data isn't there. The only thing that doesn't copy down right is the base part number. When I copy the first (call it the template block) down, it skips thirteen rows and instead of looking in A2 for the NEXT part, it looks in A13. Right now I just go in and edit the formula in the upper left of each block to fix it as it's not that big a deal for a handful but when it gets to be 15-20 it's a hassle.

I was looking for something very simple using offset, indirect, index, row(), etc. but maybe it doesn't exist.

All that said, I'll look at the power query as suggested above as that looks promising.

 

[awoohaw]

Power query has a function where if you save all your source data in the same folder (even with other kinds of files) with consistent naming convention the data can automatically import with a refresh click. Search youtube for Excel Magic Trick 1357, by ExcelIsFun.

 

[ken800]

Thanks, Awoohaw. Long-term that's a better solution. I've recently been forced into becoming a "power" user of excel as my company relies on them in a very big way. I've been trying to keep my reports and such created with "basic" excel functions so that others can see and repeat my work but my sheets have rapidly become something the vast majority just use and don't ask how so going the next step and using power query among other things is probably where I need to go.

For the time being, I had an epiphany late last night and came up with this though it probably is not the most elegant:

=INDIRECT("'P#'!A"&SUM(ROUNDDOWN((ROW()/13),0)+6))

Essentially it goes and gets the known column in my part numbers tab then takes the current row divided but the number of rows in each block of data (13 and is always the same) then adds 6 to that number as my part numbers start in A6 on that tab. The rounddown is because I have a block in the top 2 rows on that parts tab that is merged all the way across the top explaining my results to the the reader of the data and any special notes so instead of starting on line 1, I start on line 3. Otherwise, the division would be whole numbers. For anyone that might like this for their own use, it goes like this:

1st block above formula: 3/13= .23 rounded down = 0+6=6 so I get A6
2nd block above formula: 16/13= 1.23 rounded down = 1+6=7 so I get A7
3rd block above formula: 29/13= 2.23 rounded down = 2+6=8 so I get A8

and so on...

 

[awoohaw]

If you have lots of calculations in the workbook using the INDIRECT function will slow the workbook down.
As far as users of the workbook. the Power Query functionality is nearly invisible, unless they play with the data and connections group in the ribbon.
It will take you an hour or two to learn it.

But, you have a solution that works. which is good. best wishes.