H. G.
asked on
Coding help VBA/Excel
We have a macro-enabled .xlsx that:
Creates a new tab in the workbook
pulls data from a website and
pulls specific values from the newly created tab to populate cells in an exisitng tab
Spoken again. The Update Data from Server button is supposed to:
Create the FinalData tab in the .xls
Pull data from the MDA server/website
and populate it with the data pulled from MDA site then
Populate the necessary columns/rows/cells of the Testing Spreadsheet tab with the data from the FinalData tab.
But not all have the same information in the same columns. The “Testing Spreadsheet” tab within the .xls itself should have the same information in all of the same cells across the projects as long as the spreadsheet was not modified by whoever created it. However, the “FinalData” tab that has the results will oftentimes not have the same information in the same columns for each project since the requirements for final testing may vary by project. As a result, the function that auto-populates the cells in the “Testing Spreadsheet” tab needs to call the testing numbers by column name instead of by column location within the “Final Data” tab to make sure we get the correct results.
Can anyone help?
Creates a new tab in the workbook
pulls data from a website and
pulls specific values from the newly created tab to populate cells in an exisitng tab
Spoken again. The Update Data from Server button is supposed to:
Create the FinalData tab in the .xls
Pull data from the MDA server/website
and populate it with the data pulled from MDA site then
Populate the necessary columns/rows/cells of the Testing Spreadsheet tab with the data from the FinalData tab.
But not all have the same information in the same columns. The “Testing Spreadsheet” tab within the .xls itself should have the same information in all of the same cells across the projects as long as the spreadsheet was not modified by whoever created it. However, the “FinalData” tab that has the results will oftentimes not have the same information in the same columns for each project since the requirements for final testing may vary by project. As a result, the function that auto-populates the cells in the “Testing Spreadsheet” tab needs to call the testing numbers by column name instead of by column location within the “Final Data” tab to make sure we get the correct results.
Can anyone help?
ASKER
It appears that the column headings aren't consistent
So basically you are saying, your input is inconsistent, your output is undefined (because content driven), but we need to write a macro to organize it.
I am afraid that will not work... ;-)
Step 1: make sure your input tables have a fix template (they should at a minimum have fixed columns for the data you want to retrieve)
Step 2 : make sure the input tables are 'Tables' as defined in Excel
Step 3: retrieve your data using table reference instead of cell reference, e.g. if you have a column named 'ID', you'd reference the data in that column with Tablex[ID] (the advantage is that the column does not need to be in a fix position, so your users can add columns and reorganize the tables, but they need to keep the header the same)
I am afraid that will not work... ;-)
Step 1: make sure your input tables have a fix template (they should at a minimum have fixed columns for the data you want to retrieve)
Step 2 : make sure the input tables are 'Tables' as defined in Excel
Step 3: retrieve your data using table reference instead of cell reference, e.g. if you have a column named 'ID', you'd reference the data in that column with Tablex[ID] (the advantage is that the column does not need to be in a fix position, so your users can add columns and reorganize the tables, but they need to keep the header the same)
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
You can use the column headers to retrieve data, independent of the location