How to auto populate certain cells on one sheet from another
On sheet 1, I have a column called "Type". Next to that I have a column called "Part Number". Next to that I have a column called "Description".
On sheet 2 I have the same set up.
I filled in all the information I need on sheet 2 for each Type of Product as a reference sheet. How do I auto populate sheet 1 information to match those 3 columns when I type in the Type in sheet 1?
Note: Sheet 1 has multiple qty's on different rows of the same product. Since I cannot copy and paste from one sheet to another, I was hoping to have a reference sheet (Sheet 2) that I could use to auto populate Sheet 1 whenever I typed in a certain Type. I do plan on using the dropdown menu in the Type column for Sheet 1 and have that match the Types in Sheet 2.
Please help with any info you have. Kevin
Best Answer
-
It looks like you might be missing the INDEX function - or it's embedded in your range name - I can't tell which.
Try this first to make sure Index/Match piece is working. Once that is set, place the IFERROR( ahead of the entire formula. Then we'll have to close the IFERROR formula on the tailend.
=INDEX({Sellery Hall Fixture SUBMITTAL INDEX Range 3}, MATCH([Type/ Designation (On Print)]@row, {{Sellery Hall Fixture SUBMITTAL INDEX Range 1},0))
Answers
-
-
Jeremiah offered excellent solutions.
One more possibility is using Index Match. You would add this formula in each of the separate columns on Sheet 1, changing the INDEX term to the respective field you're looking for. The MATCH portion would remain the same since you are always using TYPE on Sheet2 as the reference point. Remember each {range} is a single column from Sheet2, created using a cross reference sheet link.
This info might be useful https://help.smartsheet.com/function/index
=IFERROR(INDEX({Sheet 2 column Part Number}, MATCH(Type@row, {Sheet 2 column Type},0)),"")
I added the IFERROR so if there are any non-matching cells the sheet1 cell will remain blank rather than showing #NO MATCH.
-
I've tried the =IF ERROR code a few times and can't seem to get it to work. I get #UNPARSEABLE.
This is the code I have in there:
=IFERROR({Sellery Hall Fixture SUBMITTAL INDEX Range 3}, MATCH([Type/ Designation (On Print)]@row, {{Sellery Hall Fixture SUBMITTAL INDEX Range 1},0)),"")
-
It looks like you might be missing the INDEX function - or it's embedded in your range name - I can't tell which.
Try this first to make sure Index/Match piece is working. Once that is set, place the IFERROR( ahead of the entire formula. Then we'll have to close the IFERROR formula on the tailend.
=INDEX({Sellery Hall Fixture SUBMITTAL INDEX Range 3}, MATCH([Type/ Designation (On Print)]@row, {{Sellery Hall Fixture SUBMITTAL INDEX Range 1},0))
-
This worked!!! I did forget the INDEX Thanks KDM
=IFERROR(INDEX({Sellery Hall Fixture SUBMITTAL INDEX Range 1}, MATCH([Type/ Designation (On Print)]@row, {Sellery Hall Fixture SUBMITTAL INDEX Range 2}, 0)), "")
