• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

returning an entire row of contents

S

shadedlight

Member
Hello,

I have two tables. One with Single Herbs and another with Formulas that are a combination of the single herbs.

I would like to type in the herb in the formula table; E21 (Bai He) and have that reference the single herb Bai He (A5) as well as it's entire row (B5 through M5) in the single herb table.

Bai He's row (B5 through M5) is then returned to (N21 through Y21)

This would then be copied down so it's dynamic and reference whatever herb i type in to the E column of the formula chart.

I apologize for being all over the place with explanation. Thanks for the help
 

Attachments

  • Return Row.xlsx
    52.4 KB · Views: 4
Hi, normally I would use a multi-cell array formula to return an entire row but, since you are using a Table for the returned formula, a relative reference to each attribute in turn would be preferable. I would recommend inserting a helper column to hold the row number, otherwise you will require a fresh search for the row number for each attribute and, also, the absolute referencing of the herb columns is not too pretty using Tables. I have applied a defined Name to the RowNumber column in addition to the structured reference for this reason. Finally, there are a few tests for blank cells and any 0s returned are removed by using a custom number format.
 

Attachments

  • Return RowPB.xlsx
    19 KB · Views: 4
Code: 
Option Explicit

Sub BaiHeFind()
    Dim lr As Long, i As Long
    Dim x As Long, j As Long
    x = 17    'change this to be the last row of your look up list in Column A
    lr = Range("E" & Rows.Count).End(xlUp).Row
    For i = 21 To lr
        For j = 2 To 17
            If Range("E" & i) = Range("A" & j) Then
                Range("B" & j & ":M" & j).Copy
                Range("N" & i).PasteSpecial xlPasteValues
            End If
        Next j
    Next i

End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button
 
@AlanSidman It would be interesting to see an equivalent version of the procedure coded using the list objects rather than the references to cells they currently occupy. I have the feeling that it would be more robust to inevitable changes in the Herbs table. For example
Code: 
    For Each oLR_Formula In oLO_Formulas
        For Each oLR_Herb In oLO_Herbs

        Next oLR_Herb        
    Next oLR_Formula
would cope with repositioning or resizing of the tables.
 
Peter,

Thank you and that totally works. I actually have to get this to work with Numbers for Mac as well and unfortunately isnumber is not a function that Apple supports. Can you think of a way to get this to work using something like OR(IFERROR(ISEVEN(N21),"false"),IFERROR(ISODD(N21),FALSE))

I really do appreciate any insight. Thanks
 
Narayan,

That absolutely works in excel but for whatever reason it doesn't seem to work in numbers. It returns the error:

Argument 2 of INDEX expects a number but found “.”

Thanks for your reply. I do appreciate it
 
@Narayan
I agree with your suggestions except that, for Excel, I would use a defined name to make the RowNumber column absolute whereas the other columns are used as relative references.
 
Narayn,

I apologize it says, "argument 3 of Index expects a number to be found." I looked through the table and didn't find any dot symbol. Again, I really do appreciate any help that you've given and realize my question is above and beyond what is appropriate in an MS excel forum.
 

Attachments

  • Screen Shot 2018-04-20 at 8.58.09 AM.png
    Screen Shot 2018-04-20 at 8.58.09 AM.png
    39.9 KB · Views: 6
Hi ,

The formula to be used is :

=IF(RowNumber <> "",INDEX(Table4[Element],RowNumber),"")

I am not too sure , but I think what is shown in the attached screenshot is :

=IF(RowNumber <> "",INDEX(Table4[Element],RowNumber,""))

where the highlighted parenthesis is in the wrong position.

Can you check ?

Narayan
 
Narayan,

You are AWESOME! That totally worked. I converted the formula to text rather than what Numbers defaulted to. I really appreciate it.

I'm putting together a spreadsheet of herbal ingredients in Chinese Medicine Prescriptions and will then run a bunch of Charts based off the information. This will then allow me to better understand the way the prescriptions work. You've not only helped me but have helped my patients. Thank you so much.
 
You must log in or register to reply here.
Back
Top