Excel Grand Master Required

danza · 22 Nov 2017 at 22:13

Right, I've been tasked with making it difficult to balls up data entry in a spreadsheet at work. Some of us are epic at Excel, I am not quite so (yet). The idea with the task is that there are separate columns for each snippet of information that makes up the designation of a product, which will make it a lot harder to make duplicate entries, balls-up formatting or make mistakes. Hopefully.

I have a front page, sheet 1, and I need the highlighted column to auto-populate based on inputs on sheets 2 & 3 where columns A to E are concatenated into column F. What I want to happen, is when a number is typed into column A on sheets 2 & 3, some magical function on sheet 1 in the cells in column B checks whether that number exists in column A on either sheets 2 or 3, and if so, it fetches the full description from the same row in column F, and dumps it next to the corresponding No. in column A, sheet 1.

I've messed about a bit with 'IF' & 'OR' etc., but I can't make it jive. Anyone got any ideas?

danza · 22 Nov 2017 at 22:38

Cheers, but unfortunately the sheets need to stay separate.

danza · 22 Nov 2017 at 22:42

Awesome, I will give it a go!

danza · 22 Nov 2017 at 23:02

Ace, cheers! The added IFERROR is probably what I'd have been asking about tomorrow when I couldn't get rid of a load of #N/As.

Thanks for the detailed descriptions, most helpful. Macros could be the next step, but I'll learn to walk first. I've got tomorrow to sort out a really messy spreadsheet, tidy it up and make it closer to foolproof than it currently is.

danza · 23 Nov 2017 at 12:33

Right, Tingle's function works great for searching in 2 sheets (sheets 2&3), but what do I do to it in order to add extra sheets to search through (I have many extra sheets)? Everything I do seems to end up with the error message of too few arguments.