• 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.

Formula to Auto populate the fields in the table

Hi,
I am working on the attached sheet and looking for a formula or solution which would auto populate the values in the table once I select the Carrier Global description.


I have attached the sheet for reference.

Appreciate you help on this.
 

Attachments

  • Screenshot.PNG
    Screenshot.PNG
    20 KB · Views: 1
  • Book3 (1).xlsx
    16.2 KB · Views: 7
Thank you for the attached sheet.

However, please can you onfirm what does (3 & 7) stands for in the address formula that you have used. Which rows do the exactly refer to.

=MATCH(C$27,INDIRECT(ADDRESS(3,MATCH($B30,$1:$1,0)-1) & ":" & ADDRESS(7,MATCH($B30,$1:$1,0)-1)),0)
 

Attachments

  • Screenshot.PNG
    Screenshot.PNG
    33.7 KB · Views: 1

Rameez Sarang

You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers.
For example, ADDRESS(2,3) returns $C$2. As another example, ADDRESS(77,300) returns $KN$77.

Syntax

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
  • row_num Required. A numeric value that specifies the row number to use in the cell reference.
  • column_num Required. A numeric value that specifies the column number to use in the cell reference.
 

Rameez Sarang

Instead of ADDRESS-function, You could also use something like in this sample.
Or
You could combine those two tables to one and use one lookup-value (number or text).
... You can do that Yourself.
 

Attachments

  • Book3 (1).xlsx
    16.6 KB · Views: 8
many thanks for your help on this. I am able to populate the fields with the solution you suggested.

Appreciate all your help on this :)
 
Back
Top