specific cell reference with Structured Reference?

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
82
if I use the following code I end up with what ever row the formula is in from the correct table, i want to specify the row #

Code:
=tTable1[@Group]
That works fine if I do not want to manipulate, or populate that data in a different order.

that formula will use the row # the formula is in and pull from the same row number as the table + column.

If the formula resides on row 25 on a different worksheet, i can ONLY have the value from row 25 of table + column. That is not what I want. I might need the data from table + column in row 25 to display on row 10 on some other worksheet.

How can I do that? edit to add: Without using standard reference. My columns are known to change location (order if you will) so I do not want to use normal identifiers for the formulas/linking of data.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
Would something like this be what you are after?
It should return the 6th item in the 'Group' column no matter what row the formula is on

=INDEX(tTable1[Group],6)
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
82
Peter_SSs, that works perfectly. Thank you.

edit to add: just thought of this, would it be possible to replace the hard coded '6' with some kind of variable: ie numerical value 1 - 6 based on the calculation on 2 other columns?

GroupPosName
11Foo
12Tree
13Wood
14Smell

<tbody>
</tbody>

Code:
=INDEX(tTable[Name]i)
such that i will be the results of either a for or a do while loop on both Group and Pos column?
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
.. would it be possible to replace the hard coded '6' with some kind of variable: ie numerical value 1 - 6 based on the calculation on 2 other columns?
Most likely "yes" but I am not clear exactly what you have or what you want or how you want to achieve it.

1. Is the table shown in post 3 tTable or is it the results you want extracted fro tTable? (It would be good to see a small sample of both the tTable and the results you want from it)

2. You originally seemed to be asking for a formula, but your wording now of "a for or a do while loop" indicates a macro. Can you please clarify?
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
82
Most likely "yes" but I am not clear exactly what you have or what you want or how you want to achieve it.

1. Is the table shown in post 3 tTable or is it the results you want extracted fro tTable? (It would be good to see a small sample of both the tTable and the results you want from it)

2. You originally seemed to be asking for a formula, but your wording now of "a for or a do while loop" indicates a macro. Can you please clarify?
Peter_SSs,

1. tTable is a table that holds the data. The data is subject to change and the columns might move around (ex: Group might be in column 5 today, but in a few days/weeks/months it could be row 7) thus my interest in learning about calling cells within a table via table header. Ideally the table headers will not change in this spreadsheet, and from what I have read even if they do, Excel will update all code pointing to those columns.

2. You are correct on both points. Baby steps :D First I needed to learn about how to use formulas to pull data out of the tables, now it is time to start learning how to use either do while or for loops to access data from the table. I did end up with something that was new to me to use another cell for a data point to pass into the formula you provided.

Code:
=INDEX(INDIRECT("t"&A2&"[Group]"),6)
such that column A holds the list of names I need. With that I would still need to replace the 6 with a variable, but the combination of Index, and Indirect allowed me to place the formula anyplace on a different worksheet/workbook.worksheet and not have the value tied down to the row location of the formula.

I will reach out later to see about creating the macro using table column headers. The project I am on will, I am sure, require both formula and macro to populate the new workbook.worksheet
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
1. tTable is a table that holds the data. The data is subject to change and the columns might move around (ex: Group might be in column 5 today, but in a few days/weeks/months it could be row 7) thus my interest in learning about calling cells within a table via table header. Ideally the table headers will not change in this spreadsheet, and from what I have read even if they do, Excel will update all code pointing to those columns.
Unfortunately, that does not address my question 1 at all. It was:
1. Is the table shown in post 3 tTable or is it the results you want extracted from tTable? (It would be good to see a small sample of both the tTable and the results you want from it)
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
82
My bad, i absolutely misread your question. Like most entities I have for one of my projects a spreadsheet that contains data. This data is stored 1 row to everything about that location.

workbook1.worksheet(#) - tTable(s)
locationdata 1data 2data 3data 4
city1street1building1junk1meh1
city2street2building2junk2meh2

<tbody>
</tbody>

The data from this workbook is the master data for the project. Many cases the data from say row 2 in the above example will be needed in a new workbook that is column based

workbook2.worksheet(some_name_number) - currently not planned to go into a table, just cell data to be populated
data 3junk1junk2
data 1street1street2
locationcity1city2
data 4meh1meh2
data 2building1building2

<tbody>
</tbody>

Yes the bigger picture is far more complicated. The workbook that will be pulling data from the master will be getting data from tTable(s) from workbook1.worksheet(#) and using them in workbook2.worksheet(some_name_number)

The tTable(s) in workbook1 are all identical for the table headers, thus my desire to learn how to call cell data both with formulas and with VB.

Manual copy/paste of data from workbook1 into workbook2 is something I would like to avoid moving forward. I've done that enough times, it is not time to automate that process.

I am updating an automation process with the desired document I will use as my template for this project. That project requires copy/paste of basic known data (phase 1 of project) into a worksheet(raw.data), run script to copy X number of worksheet(output.data) and populate, link internal, and external data.

That worked great in phase 1. Reduced the time from 40+- man-hours by hand down to under 30min. Phase 1 is almost over so we now have that data. In phase 2 we can use the data from phase 1 to auto-populate even more field for workbook2, confirm and update as we move forward with phase 2.

I would like to avoid the current process of copy from workbook1 paste into workbook2 by hand, instead I would like to create the code in workbook1 that will involve user form, gather some basic data from the user, open workbook2, populate the data user specified, save as workbook2 new name.

I have code that I can use as a base for the user form.
I have code to open workbook2 from a known location.
I have code that can take the user selected data and populate into fields in workbook2
I DO NOT have the code to find and workbook1.tTable.value=workbook2.worksheet.cells().value {yes that is not proper code, just through flow}
I have code to save as

If i have made that muddy let me know. In short what I am attempting to learn now is howto access the data in a table using the combination of table column headers and a specified location (that yes will be coming from a loop of some type) to pull data from workbook1 to be used in workbook2.

At no point for this project do I plan on adding or editing the data in workbook1. Currently that is still a manual process as that is desirable for now.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
I'm struggling to keep up as things seem to be changing all the time.
First we had a table and then on another worksheet you wanted to get data from a particular row number - at least that is what I thought. Now we seem to have two workbooks and it looks like you want to extract all rows (with columns transposed to rows and in a different order).

See if you can adapt this concept - if I have interpreted the current requirement correctly. :eek:
Formula in H5 is copied across and down.

Excel Workbook
ABCDEFGHI
1locationdata 1data 2data 3data 4
2city1street1building1junk1meh1
3city2street2building2junk2meh2
4
5data 3 junk2
6data 1street1street2
7locationcity1city2
8data 4meh1meh2
9data 2building1building2
From Table
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
82
Thank you Peter_SSs

Their are no less than 2 workbooks. The data in, lets call it the Master Workbook has been populated over phase 1 of the projects. That data is semi static. As their are upgrades to the projects the data on the Master will update, otherwise it is static enough to be used as the repository for the data. (more info than our current SQL databases need/want)

Data from the Master is used to build multiple types of other workbooks built around the projects: support, updates, maintenance, alarming, etc...

HISTORY: during phase 1 much of the data kept in the Master was incorrect. As phase 1 progressed the data was corrected and updated. Other workbooks were used to populate data into the Master so those were largely built by hand. Towards the end of phase 1 we had 5 columns of data for each project that was known to be correct. Building an automated workbook from 5 columns was not to harsh. Code snippet to follow. We would manually copy those 5 columns worth of data from the Master into the other workbook and run some VBscripting to build out the new workbook. During the project data was collected and manually populated into the new workbook. At the end that data was cleaned up and moved into the Master.

Code:
' ================================================================
' Code to copy Group # from Overview sheet to new group Worksheet.
' ================================================================


        j = 2       ' Sets j to starting value.
                    ' Sets starting point for copy/paste of data to correct Cell.
            Set rFoundCell = Worksheets("overview").Range("A1")


               ' Starts for loop to search for xNumber vaule on the Overview Worksheet in the Group column.
               For lCount = 1 To Application.WorksheetFunction.CountIf(Worksheets("overview").Range("a1:a200"), xNumber)
                    ' Finds the xNumber value on the Overview Worksheet in the Group column.
                    Set rFoundCell = Worksheets("overview").Columns(1).Find(what:=xNumber, after:=rFoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
                
                    ' Start of the copy/paste data from Overview to new Group # Wroksheet in a transpose form.
                    With rFoundCell
                        Worksheets("overview").Range(rFoundCell.Address).Copy
                        Worksheets("group " & xNumber).Activate
                        '                             .Cells([row], [column]) - 06-12-2018 procedure was row 3
                        '                             18 July, 2019 procedure now row 8
                        Worksheets("group " & xNumber).Cells(8, j).PasteSpecial xlPasteValues
                        j = j + 1
                    End With


               Next lCount
xNumber is populated from the user via InputBox

That code is then slightly modified for each of the 5 columns worth of data. While that may not be the most efficient (yes screen updating and other updating is disabled during the process) it did its job and reduced a 40 hour process down to under 30 min.

CURRENT: The Master uses Table's containing thousands of points across many worksheets. Yes this is a good thing for us. I am trying to take the process from phase 1 and update the code to be able to pull hundreds of those data points from the Master to build out any of the other workbooks we will need in phase 2.

The Master is not locked so the column #'s will change. With the use of Table's that should not matter as long as the table column headers do not change. Instead of manually populating the "Overview" worksheet in workbook 2 with all of the columns from the Master and updating my existing code that is fixed and hard-coded not taking advantage of the power Table's provide.

The new code will reside within the Master workbook. It will open another workbook to be used as a template to populate data from Master into workbook2. I already know how to open the new workbook and perform a save as on that new workbook. What I am needing to learn now is how to pull data from the Master[tTable[Column](specific cell in that column)] to new Workbook.Worksheet.Cell

Thus when you asked for a fixed example, I cannot provide a fixed example. What I can say is this:

1. Master data is stored in Table's, currently 50+ tables
2. Master data is stored column base, currently 100+ columns
3. Other workbooks need to be able to pull fixed data from the Master for their needs; ie: troubleshooting, updating, repair, maintenance on project items.
4. Other workbooks are typically more than just a list of data. Can include items such as check-lists, punch-lists, confirmations, point of contact, dates, people involved, etc... none of that is stored in the Master and will be manually populated as the task is completed. These workbooks are more human friendly for reading and following processes.
5. Other workbooks will not need all 100+ columns worth of data, but the amount they CAN use is growing. To reduce manually copy/paste process I want to automate the population of data from the Master out to these other workbooks.

While the exact output will be different for each type of "Other workbooks" the process so far as coding is concerned should be similar enough to build on.

ie: Copy data from Master tTable1 Column header (Group) to Punch-list workbook on worksheet (Group #) into column A row 15

Column A and row 15 will not be fixed points. that is just a 1 cell from Master copied into Punch-list.

The process of moving a single point, than creating loops (for, do while, with, etc...) to populate sections of cells from the Master into Other workbooks is what I am after.

I will be researching the Me. today, if my google foo holds up to understand that process to pull data from tables.

Code:
Me.ComboBox.List = Range("tName[Name]").Value
worked great to make the drop down list for my user form.

enough ramble... I am looking to use VBscripting to pull data from tables in Master Workbook to populate non-table new workbook.worksheet.cell/range

Thank you.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
I think the requirement has become too great (for me at least) for a free public forum like this. If any particular issues can be distilled down to a simple form then you may well still get responses to them by posting new threads in the forum.
 

Forum statistics

Threads
1,078,440
Messages
5,340,293
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top