Display table location in cell - Dynamic Solution with VBA Needed

Hi all,

I'm trying to create a spreadsheet to make work easier (locations of certain things in a system)

I want to put an x in a table, and be able to pull that location out and into a cell.

I have attached a screenshot of what I want to achieve (locations have been entered manually at this stage)

Is someone able to help with the best way to achieve this please? I'd also like to add some checking in, so that if something is located in the same place as something else, an error comes up (say two things were in red 2 for example)

Many Thanks

EDIT: Ideally at some point I'd like to make this system so that it can't be edited (IE the formulas aren't displayed in the cell, but not sure how easy this is to do? My excel skills aren't too advanced!)

Welcome to the forum.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Thank you for the reply - apologies the first post wasn't very helpful!

I have attached a sample of what I want to achieve, with a column containing desired outcome. I hope this helps?

Thanks

Why is example 5 and error? I cannot see why you think the location is already in use ...

The headings above the matrix - will they be there in the real workbook, or will it just be three grids with the number headings side-by-side?

I'm not clear what the ultimate aim is here, and if I provide a solution based on this sample, I need to be sure that everything in this sample is replicated in the real workbook, otherwise my solution will fail. Make sense?

With this in mind, is there anything else I need to know?

Yes, thanks - helpful. Will be back!

OK. Get rid of the merged headers for now and fill the first 10 cells with Red, the next 10 with Green and the final 10 with Blue. You can hide this row later and put your nice merged headings above it.

In E6 copied down:

=IF(COUNTIF($H6:$AQ6,"x")>1,"!Error! - Double Patched",IF(COUNTIF(E$5:E5,INDEX($H$2:$AQ$2&" "&$H$3:$AQ$3,,MATCH("x",$H6:$AQ6,0)))>0,"!Error! - Duplicate Entry",INDEX($H$2:$AQ$2&" "&$H$3:$AQ$3,,MATCH("x",$H6:$AQ6,0))))

In C6 copied down:

=IFERROR(LOOKUP(LEFT(E6,FIND(" ",E6)-1),{"Blue","Green","Red"},{"Right","Centre","Left"}),"")

This is just the start of this, eventually I want to make this quite modular (macro buttons to change the size of the matrix etc.)

Uh-oh! This is worrying ...

OK, well my solution is not going to work when you start doing that, sadly.

I won't be helping any further with this, as I think the whiole thing will need to be VBA driven. I think we need to move this to that section. Do you agree?

Thank you for both replies and your help! - The above solution is great for a start whilst the spreadsheet isn't too complex and works exactly desired so thank you!

Yes - that probably is the best idea. My general programming skills are ok, but I don't have any direct experience with VBA.

Thanks

OK - I've moved the thread. Good luck with your project.