Group worksheets with different cell locations in Excel 2010

[Velidae]

Hello!

I am setting up a spreadsheet with three separate worksheets that have some overlapping columns, but in different locations.
e.g, "File #" starts in M3 on one sheet, but is in D2 for another sheet.

How can I set up a code that automatically groups the overlapping columns between the worksheets, while retaining these different column/cell locations?

Thank you for your help.

 

[Jeffrey Mahoney]

Are the column headers the same name? Can you add Named ranges in each to represent the same data? How do you want them grouped? Meaning, are you just trying to summarize the results, combine the data into a master, or lookup some values?

 

[Velidae]

Are the column headers the same name? Can you add Named ranges in each to represent the same data? How do you want them grouped? Meaning, are you just trying to summarize the results, combine the data into a master, or lookup some values?

I'm just trying to arrange the spreadsheet to ease data entry. Since multiple worksheets have the same data, it would be great to be able to just enter it into the first worksheet and have it automatically fill in the others.

The columns have the same headers, they're just in different orders in the worksheets because the same data is used for different purposes, so organized differently in that sheet.
Some of the headers are: File #, Researcher Name, Institution Name, Province.

 

[Jeffrey Mahoney]

I don't know why you would duplicate the data. I usually prefer one large dataset so I can lookup values easier.

"Automatically fill in the others" implies either using formulas to see the values from the other tables or using macros to do that.

If you are trying to enter data in all the tables independently, you wouldn't be able to use formulas because they could get overwritten and you would have circular reference back to each of the other tables.

A macro would have to find missing values on each of the tables, go look at the other tables to see if the data exists and then populate the found result. Not easy.

Again, I go back to a single table.

Jeff

 

[Velidae]

I don't know why you would duplicate the data. I usually prefer one large dataset so I can lookup values easier.

"Automatically fill in the others" implies either using formulas to see the values from the other tables or using macros to do that.

If you are trying to enter data in all the tables independently, you wouldn't be able to use formulas because they could get overwritten and you would have circular reference back to each of the other tables.

A macro would have to find missing values on each of the tables, go look at the other tables to see if the data exists and then populate the found result. Not easy.

Again, I go back to a single table.

Jeff

It's a large spreadsheet. Multiple people will be using it and not all the info is relevant for everyone, thus it is divided into worksheets.

I know if you ctrl + click the different worksheets it groups them manually and any info you enter into A1 will appear in all the grouped worksheets. Is this not possible to do automatically with VBA? If necessary I can reorganise the columns, but ideally I'd like to be able to input into the columns in their current, differing locations.

 

[Jeffrey Mahoney]

Temporarily combining sheets to enter data requires that the format is exactly the same.

If you reorder the columns, would that solve the problem? Do the rows of data match too? If someone enters data in row 10 on one sheet, does it match the same record on line 10 for the other sheets? If a user inserts a row or sorts the data would that not mess things up?

 

[Jeffrey Mahoney]

If the columns and rows don't match on all sheets, in your request, a macro would have to be created that matched the data for each record to the data from all other sheets. This macro would have to make sure not to overwrite any data already entered.

What would happen if the same record existed on at least 3 sheets, data was complete on two of the sheets but was different values; how would you populate the 3rd? Which of the two completed records would get chosen as the master?

Jeff

 

[Jeffrey Mahoney]

If I were tasked with allowing different users access to all the records, but only certain columns, I would make a system that would Hide or Unhide columns based on a password.

A hidden table could contain a list of passwords and columns to unhide. When a user puts a password in a specified cell, a macro would check the hidden table and choose to unhide the corresponding columns.

 

[Velidae]

If I were tasked with allowing different users access to all the records, but only certain columns, I would make a system that would Hide or Unhide columns based on a password.

A hidden table could contain a list of passwords and columns to unhide. When a user puts a password in a specified cell, a macro would check the hidden table and choose to unhide the corresponding columns.

I was able to solve my own problem. Instead of using AVB I just linked the first cell of Column A in worksheet A with Column A in worksheet B and dragged the formula down the column.

I'm now trying to link a cell within in an IF THEN formula but it's not working. This is my formula: =IF('Worksheet1'!P2="ABC","='Worksheet1'!A3","0") but the cell with this formula just says ='Worksheet1'!A3. Is it possible to link a cell to another cell within an IF THEN formula?

 

[Jeffrey Mahoney]

Try this.

=IF('Worksheet1'!P2="ABC",'Worksheet1'!A3",0)