Show values of selected table row in other cell

**kammend** · 12-28-2017, 07:31 AM

Hi all,

Attached a sample file of the table.

when the user selects a certain cell within the table (table name: "Table1") then I want to have the values of the cells in column D and E (of the selected row) shown in D1 and E1.

So the value of D1 and E1 constantly changes depending on the row that is selected. It should not matter which cell is selected. E.g. when A10 is selected, then D10 and E10 should be used.

I am thinking of using something like the "intersect" function. However, I do not really know how this works in vba and IF it will work.

Next step is that I use that value to run a Filter macro f

rom the Ribbon. But first I need to get the correct values ;-)
This should run on Excel 2010, 2013 and 2016.

Thanks,
Daniel

**xlnitwit** · 12-28-2017, 08:20 AM

Hi,

You can add this code to the worksheet code module

Please Login or Register  to view this content.

**sintek** · 12-28-2017, 08:24 AM

Try this...Put code in sheet module

Please Login or Register  to view this content.

**Stormin'** · 12-28-2017, 08:47 AM

Here's my take on this. A more general approach using your named ranges and easier to change names etc. if they change in the future. You only want the top yellow cells to populate if you have only one row selected in the table; else blank (or change to whatever you want).

In a normal module (I don't like having big chunks of code behind worksheets as I forget about them):

Please Login or Register  to view this content.

In the worksheet code behind Sheet1:

Please Login or Register  to view this content.

**kev_** · 12-28-2017, 09:22 AM

very similar to xlnitwit

sheet module:

Please Login or Register  to view this content.

**Stormin'** · 12-28-2017, 09:40 AM

Everyone else's code will break if any new columns are inserted on the left hand side or if the yellowed cells are moved or not next to each other. Mine doesn't... just saying

**kev_** · 12-28-2017, 09:51 AM

@stormin' - yours will break if the named range is deleted or the table is renamed

**Stormin'** · 12-28-2017, 10:06 AM

In balance of probability... it is more likely for an end user that is not involved in the design to insert a column, move some cells, or even add a new table (so cannot rely on ListObjects(1)) than to rename the main table or delete/rename named ranges.
Constants are up the top for easy changing for the developer (and no counting of columns required)

**kev_** · 12-28-2017, 10:31 AM

@stormin'
What's probability (or logic) got to do with likely user behaviour?

And it does not matter how easy it is for the developer to fix the code (especially if he's at home for Christmas)
- if the named range is deleted then your code is still just as broken as everyone else's!

**Stormin'** · 12-28-2017, 10:43 AM

Likewise, if the workbook is deleted everyone's code is broken

**AliGW** · 12-28-2017, 10:46 AM

Can't this all be dealt with using some sheet protection???

**kev_** · 12-28-2017, 11:03 AM

Merry Xmas @AliGW

@stormin' - I still have no idea where you are going with your "asides". What's the "punchline"?

**Stormin'** · 12-28-2017, 11:11 AM

There is no punchline, I was just expanding on your logic to expose the fragility of it.
I have a feeling that we are overthinking the issue way beyond the OP's original question

**AliGW** · 12-28-2017, 11:15 AM

OK, chaps - no more bickering, please. Let's not let this get out of hand. If you have nothing to add that does not go some way towards solving the weaknesses in each of your solutions, please don't post. Thanks!

**kammend** · 12-28-2017, 05:42 PM

hi all, thank you for the many responses

For your info; I have protected the worksheets. Users can add lines, but not add/delete columns. And also, users cannot delete or create tables.

I want to use this logic on mulitple worksheets with tables with different names ( the 2 essential columns have the same name in all tables).

**Stormin'** · 12-29-2017, 05:21 AM

- Same code to work across multiple worksheets in the same workbook
- Only one table per worksheet
- Each table has a different table name
- Target columns have the same heading in each table
- Yellowed cells are in the same position in each workshet and will not be moved

Some minor modifications (in red) to the code above for those criteria. I've forgone the use of the defined names you set on your yellowed cells to allow it to work on more than one sheet.

Please Login or Register  to view this content.

Then for each worksheet that you require this function, just put the following into the code behind the worksheet:

Please Login or Register  to view this content.

**kev_** · 12-29-2017, 08:44 AM

@stormin' - how ironic!

**Stormin'** · 12-29-2017, 10:22 AM

@kev_ I know, right! I thought of you when writing that post haha

**AliGW** · 12-29-2017, 10:24 AM

Nice to see we now have a bromance - much nicer.

**kammend** · 01-03-2018, 12:12 PM

Hi Stormin',
I tried your code and it works beautifully

Now I want to make it a little bit more complex. I have a workbook with multiple worksheets and multiple lists.
Eventually I want to call a "filter macro" from the ribbon that uses the result of the intersect code you provided.

How can I make this code work on all the lists I have in the workbook. It is fair to assume that each list will have the same 2 columns (budget task and worksheet).

So basically the name of the worksheet and list is different each worksheet. To make it a bit easier for me with the filter macro, I put the intersect outcome on a separate worksheet. So the intersect result of each list should go to the same cells in the "config" worksheet.

I attached an example file to demonstrate.

**Stormin'** · 01-04-2018, 05:12 AM

Sorry Dan, I'm not sure I understand exactly what you mean.
Will your filter macro filter all the tables with the same two criteria in the config sheet?
Which table's intersect goes into the cells in the config sheet?

**kammend** · 01-04-2018, 06:44 AM

The filter macro in the Ribbon will only run on the active sheet.
So I want to run one single macro from the ribbon that works on all worksheets (with a list that has meets the criteria of these 2 columns). So each time you determine the intersect in a list it will put the result in that Config worksheet. That way the macro only needs to check that worksheet for the filter criteria.
I probably also need to have the name of the active list put on that Config worksheet..?

I hope this clarifies it a bit

**Stormin'** · 01-04-2018, 12:32 PM

Ah, okay, so your macro will use only the activesheet's intersect to filter only the activesheet's table. Then of course you should be able to select any similar sheet and run the same macro to filter just that sheet's table with just that sheet's intersect.

Is that correct? You won't need a config sheet for this, we can just read directly from the activesheet each time.

**kammend** · 01-30-2018, 07:05 AM

Hi, sorry for the late response. I haven't had time to work on this for while. And now I do again :-)

Yes, that is correct. I have multiple worksheets (approx +10) with a table in it . The TableName is different for each sheet of course.
If I can store the output values on the filter (Budget Task and WorkStream) in a variable I can use in a macro that is run from the Ribbon, then that is good for me too. (maybe even better)

So I would need to determine the activesheet and the activetable to make it dynamic.

With the current logic it stops in debug mode and the line with "Set oTbl = .ListObjects(sTblName)"
This makes sense as a few lines above we have defined "Const sTblName As String = "Table1" and this should be made dynamic. Is there a function for ActiveTable? Or do we need more to make this work?

**Stormin'** · 01-30-2018, 09:07 AM

Yep change that to

Please Login or Register  to view this content.

to grab the 'first' listobject on that sheet, which should be that table.

Let me know if it works as you want it to.

**kammend** · 01-30-2018, 11:37 AM

Yes! Briliant

This solved it for me.

Thanks a lot!

**Stormin'** · 01-30-2018, 12:36 PM

Just make sure that that section looks like this:

Please Login or Register  to view this content.

Glad it's working, happy to help

**kammend** · 01-31-2018, 08:52 AM

Hi, sorry to reopen this thread. I now see that the "Work Stream" column does exist in every table, however not always in the same place.

Is there a way to first determine the location of both columns before applying he filter?

Daniel

**Stormin'** · 01-31-2018, 10:14 AM

As long as the columns are named exactly the same, it will find them regardless of the position in the table.

**kammend** · 01-31-2018, 10:29 AM

They are named exactly the same. However I do get empty filtered table when column is at different position...

Please Login or Register  to view this content.

When the Budget Task is not the 4th column, but the 6th (or any other number), the filter still filters on the value found for "Budget Task" and applies that filter on Field4 (which holds other data)

**Stormin'** · 01-31-2018, 11:20 AM

Sorry I thought you were talking about my code. That's something different.

One method to find the table field number by field header is using the MATCH worksheet function:

Please Login or Register  to view this content.

Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Re: Show values of selected table row in other cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] show a certain value in the selected cell based on the selected item no. and month

[SOLVED] Is there a formula to show which cell is currently selected

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions