Populate new rows based on values from other cells

[ma2169]

hello and thank you in advance for taking a look at this question. I have used this site as a great reference in the past but this is my first time asking a question. i have searched over and over but i cannot seem to find an answer specific to my need. if more information is needed please let me know

i have a set of cells in which my company enters information. in my sheet these cells are A26:J56. I need to take information from cells A26:BB6 to create a new table. in the picture below i need to populate preferably with a formula (not insert a new row with a macro if possible since i have other formulas already preloaded into cells H62:H156 that i don't want to move as yet other cells reference those formulas and there current location) cells A62:A150 with information from cells A26:A56 based on the value in cells b26:B56.

So in cells A62 & A63 it should populate with "site 1" since "site 1" is the value in cell A26 there is a value of 2 in cell B26 and cell A64 should be populated with "Site 2" since is is the value in cell A27 there is a value of 1 in cell B27 and so on.

Thank you in advance!

 

[OaklandJim]

preferably with a formula (not insert a new row with a macro if possible since i have other formulas already preloaded into cells H62:H156 that i don't want to move as yet other cells reference those formulas and there current location)

Consider that when adding or deleting rows or columns normally the formulas update automatically to account for such changes.

If you are looking for a non VBA solution I'm not sure that what you want is possible. But I can't tell.

For me to try to assist I'll need to have more. Is it possible to post a link to the workbook so someone willing to assist does not have to gin one up to work on? Use the link icon at the top of the message area.

Also, ideally you show more of the data. Words are ok but at least for me but content is not completely obvious. Dealing with it as an abstraction is challenging for me. (Often others on the list do not have that problem, so maybe someone else will grab this request.)

Have a look at Mr. Excel's add-in called XL2BB which enables you to post portions of a worksheet. It shows values and formulas. See HERE for details.

 

[ma2169]

Consider that when adding or deleting rows or columns normally the formulas update automatically to account for such changes.

If you are looking for a non VBA solution I'm not sure that what you want is possible. But I can't tell.

For me to try to assist I'll need to have more. Is it possible to post a link to the workbook so someone willing to assist does not have to gin one up to work on? Use the link icon at the top of the message area.

Also, ideally you show more of the data. Words are ok but at least for me but content is not completely obvious. Dealing with it as an abstraction is challenging for me. (Often others on the list do not have that problem, so maybe someone else will grab this request.)

Have a look at Mr. Excel's add-in called XL2BB which enables you to post portions of a worksheet. It shows values and formulas. See HERE for details.

thank you for your response. i am sorry for the limited information but this is a work spreadsheet so i have to be careful with what i share.

unfortunately i cannot add the rows because other formulas rely on the rows that currently exist. meaning if i insert a row say above line 64 then the other equations will still reference the same cell whcih is now on row 65. but i need it to reference the info on 64. and i cannot insert an entire row as it will ruin other information.

i am ok with a macro version i should have mentioned this previously, as long as it does not require inserting rows. a macro that copies and pastes or creates and array (if this is the right word) the table as shown in the OP would work too. but again i cannot have it insert a row it must populate the cells that currently exist.

i cannot post a link to the spreadsheet at is it for work and there is to much other information i cannot share which is why i only showed the limited amount i did.

this is my template sheet so there is no data in there but the information "sites" would be our vendors from where we ship.

again since this a work spreadsheet i do not have the ability to install add-ins on my work computer.

hoping you can help. thank you

 

[OaklandJim]

Can you post a worksheet with fake data? That would help me understand the need better. I have to see stuff before I can come up with a solution. Others on the list are better at understanding in the abstract.

 

[ma2169]

Can you post a worksheet with fake data? That would help me understand the need better. I have to see stuff before I can come up with a solution. Others on the list are better at understanding in the abstract

forgive me but i created a blank file which is ready to upload but i don't see a place i can actually upload it here?

 

[OaklandJim]

Use the link icon above the message box.

 

[OaklandJim]

You have to put (upload) the file somewhere where it can be linked to such as Box, Dropbox, 1drive, etc. After that then you create a link to it using the link icon.

 

[OaklandJim]

Ok. I believe that this code does what you asked for. Let me know if it works or if you have questions.

Sub PopulateTable()

'   ----------------------
'        Declarations
'   ----------------------

'   "Anchor" for source data is one cell above the upperleftmost (first) source data cell.
    Dim rAnchorCellSourceData As Range
    
'   "Anchor" for target data is one cell above the upperleftmost (first) target data cell.
    Dim rAnchorCellTargetData As Range
    
'   Count of existing source data rows to process.
    Dim iSourceDataRowsToProcess As Long
    
'   Used for itetrating through source data
    Dim iRow As Long
    
'   Used for itetrating through source data
    Dim iTargetRow As Long
    
'   Used to keep track of how many target data rows were found.
    Dim iTargetRowsFound As Long
    
'   Contains the site name for a source data row
    Dim sSite As String

'   Contains the quantity of trucks for a source data row
    Dim iQtyTrucks As Long
    
'   -------------------------
'        Initializations
'   -------------------------
                                                    
    With ThisWorkbook.Worksheets("Sheet1")    '<= Name of worksheet that is being processed.
                                              '   Change this to whatever your worksheet is named.

'       Set data anchor cells for 1. source data, 2. target data.
        Set rAnchorCellSourceData = .Range("A25") '<= "anchor cell" for source data is in the hidden row 25
        
        Set rAnchorCellTargetData = .Range("A61") '<= "anchor cell" for target data is in the hidden row 61
    
    End With
    
'   --------------------------------------------------
'       Count then Clear Existing Target Data Rows
'   --------------------------------------------------

    iTargetRowsFound = 0

    With rAnchorCellTargetData

'       Iterate target data until an empty cell is encountered.
        Do
'           Keep track of which target data row is being processed.
            iTargetRow = iTargetRow + 1
            
'           Set count of target data rows found.
            If .Offset(iTargetRow).Value = "" Then iTargetRowsFound = iTargetRow - 1

        Loop Until iTargetRowsFound <> 0

    End With

'   Clear existing data if there is any.
    If iTargetRowsFound <> 0 _
      Then rAnchorCellTargetData.Offset(1).Resize(iTargetRowsFound).ClearContents

'   ----------------------------------------
'       Count Existing Source Data Rows
'   ----------------------------------------
    
    iRow = 1

'   Iterate source data until an empty cell is encountered.
    Do Until rAnchorCellSourceData.Offset(iRow) = ""

        iSourceDataRowsToProcess = iSourceDataRowsToProcess + 1

        iRow = iRow + 1

    Loop

'   ------------------------------------------
'        Put Values Into Target Data Area
'   ------------------------------------------
    
    iTargetRowsFound = 0

'   Process each source data row.
    For iRow = 1 To iSourceDataRowsToProcess

'       Get the site name and qty of trucks for the source data row.
        With rAnchorCellSourceData
            sSite = rAnchorCellSourceData.Offset(iRow).Value
            iQtyTrucks = rAnchorCellSourceData.Offset(iRow, 1).Value
        End With

'       Put the site values for the source data row being processed into the target data row(s).
        For iTargetRow = 1 To iQtyTrucks

'           Kee4p track of how many target data rows have been processed.
            iTargetRowsFound = iTargetRowsFound + 1

'           Put the site name into the respective target data row.
            rAnchorCellTargetData.Offset(iTargetRowsFound).Value = sSite

        Next iTargetRow

    Next iRow
        
End Sub

 

[ma2169]

Ok. I believe that this code does what you asked for. Let me know if it works or if you have questions.

Sub PopulateTable()

'   ----------------------
'        Declarations
'   ----------------------

'   "Anchor" for source data is one cell above the upperleftmost (first) source data cell.
    Dim rAnchorCellSourceData As Range
   
'   "Anchor" for target data is one cell above the upperleftmost (first) target data cell.
    Dim rAnchorCellTargetData As Range
   
'   Count of existing source data rows to process.
    Dim iSourceDataRowsToProcess As Long
   
'   Used for itetrating through source data
    Dim iRow As Long
   
'   Used for itetrating through source data
    Dim iTargetRow As Long
   
'   Used to keep track of how many target data rows were found.
    Dim iTargetRowsFound As Long
   
'   Contains the site name for a source data row
    Dim sSite As String

'   Contains the quantity of trucks for a source data row
    Dim iQtyTrucks As Long
   
'   -------------------------
'        Initializations
'   -------------------------
                                                   
    With ThisWorkbook.Worksheets("Sheet1")    '<= Name of worksheet that is being processed.
                                              '   Change this to whatever your worksheet is named.

'       Set data anchor cells for 1. source data, 2. target data.
        Set rAnchorCellSourceData = .Range("A25") '<= "anchor cell" for source data is in the hidden row 25
       
        Set rAnchorCellTargetData = .Range("A61") '<= "anchor cell" for target data is in the hidden row 61
   
    End With
   
'   --------------------------------------------------
'       Count then Clear Existing Target Data Rows
'   --------------------------------------------------

    iTargetRowsFound = 0

    With rAnchorCellTargetData

'       Iterate target data until an empty cell is encountered.
        Do
'           Keep track of which target data row is being processed.
            iTargetRow = iTargetRow + 1
           
'           Set count of target data rows found.
            If .Offset(iTargetRow).Value = "" Then iTargetRowsFound = iTargetRow - 1

        Loop Until iTargetRowsFound <> 0

    End With

'   Clear existing data if there is any.
    If iTargetRowsFound <> 0 _
      Then rAnchorCellTargetData.Offset(1).Resize(iTargetRowsFound).ClearContents

'   ----------------------------------------
'       Count Existing Source Data Rows
'   ----------------------------------------
   
    iRow = 1

'   Iterate source data until an empty cell is encountered.
    Do Until rAnchorCellSourceData.Offset(iRow) = ""

        iSourceDataRowsToProcess = iSourceDataRowsToProcess + 1

        iRow = iRow + 1

    Loop

'   ------------------------------------------
'        Put Values Into Target Data Area
'   ------------------------------------------
   
    iTargetRowsFound = 0

'   Process each source data row.
    For iRow = 1 To iSourceDataRowsToProcess

'       Get the site name and qty of trucks for the source data row.
        With rAnchorCellSourceData
            sSite = rAnchorCellSourceData.Offset(iRow).Value
            iQtyTrucks = rAnchorCellSourceData.Offset(iRow, 1).Value
        End With

'       Put the site values for the source data row being processed into the target data row(s).
        For iTargetRow = 1 To iQtyTrucks

'           Kee4p track of how many target data rows have been processed.
            iTargetRowsFound = iTargetRowsFound + 1

'           Put the site name into the respective target data row.
            rAnchorCellTargetData.Offset(iTargetRowsFound).Value = sSite

        Next iTargetRow

    Next iRow
       
End Sub

this worked perfectly. thank you so much.