Moving cursor in inactive window?

[Beoin]

Hey, there. First of all, many thanks to all the crazy-smart people that post on this board. Not gonna lie, I have creeped on this site a lot, and I've found so much help on Mr. Excel. However, I finally have a question that I've been unable to resolve to my satisfaction (and my searches through the archives have been unsuccessful.)

Here's the setup: I have two open workbooks.
--The first (called Scansheet) is basically a couple of columns into which I scan barcodes. It's my active window, and after I scan in a number, the cursor automatically moves down a cell to prepare for the next scan. Beep beep beep beep beep, all the way down, one right after another.
--The second is my Master Spreadsheet, which has a list of the 300ish things I know I will be checking in/scanning, along with their associated barcode numbers. The first cell of every row contains a little beast of a formula (which I built after reading through these boards; thanks again!) that compares that row's barcode number against the columns in my Scansheet. If the number matches something that's been scanned in, the Master list cell shows "Yes" or "No" to indicate the item has been successfully checked in (or not).

Here's my issue:
--Because the Scansheet has to be active to accept the data from the scanner, the cells in my (inactive) Master list are just quietly changing to Yes/No. That's fine, but I would prefer to be able to see the row in my inactive Master list that is changing as I scan in, whether that means the cursor is moving there or .... whatever. But I need the Scansheet to stay active, since the scanner will input the barcode number into whichever cell is currently active, and I don't want to have to click into a different window every time I scan something.

Here's my question:
--Is this even possible? I feel like the answer may be, "No."

Thanks, in advance, and please let me know if there's any additional info that would be helpful.

 

[Beoin]

Also, I am also open to any alternative solutions, such as how to scan directly into my Master list without using a second spreadsheet. (I tried this initially, creating a single cell where the scanned info could be entered, but when that info changes, the Yes/No in the previously scanned cell disappears. I guess I'd need any scanned number to replace the formula in its matching cell with a "Yes", which maybe isn't even a thing.)

 

[Jaafar Tribak]

Have you tried arraging the workbooks windows side by side ? ... you could also use the synchronize scrolling feature to always keep things within view

 

[Beoin]

Have you tried arraging the workbooks windows side by side ? ... you could also use the synchronize scrolling feature to always keep things within view

Yes, I do have them side by side so I can easily access both, but the synchronize scrolling doesn't really help (unless I'm missing something about synch scroll!) The Scansheet on the left fills straight down as I scan, starting at A2. The Master sheet on the right stays alphabetized in case I need to do a quick manual search. If I start scanning into Row 2 of my Scansheet, it could match something on Row 289 of my Master, which I can't see unless I scroll all the way down my Master sheet window. So ideally, I'd like to be able to scan into Row 2 on my active Scansheet while the inactive Master "autoscrolls" down to the matching record (where the formula is changing from a blank cell to a "Yes"/"No".)

Apologies if I've made that even more confusing, but thanks for the idea!

 

[wideboydixon]

Here's what you could try:

In the Worksheet_Change() event on your Scansheet, use a Find method on the Master Sheet to try and find a cell with that text. If you find the cell than use something like:

Workbooks("Master Sheet.xlsx").Windows(1).ScrollIntoView foundCell.Left, foundCell.Top, foundCell.Width, foundCell.Height

Of course, if you do such a thing then you could probably dispense with the formula on the master sheet and simply put "Yes" to the right of the found cell!

WBD

 

[Beoin]

Thanks, WBD. I gave my husband (who has some coding background) your suggestion, and between that and a lot of Googling, he got it to do exactly that! I put a data validation on my Scansheet to warn me when I'm scanning a duplicate, and then it finds the code on my Master list and puts a "YES" or "NO" in the correct column. So no need for the formula!
Thank you so much!