I have an Excel file which contains multiple worksheets.
- Worksheet 1 has data that is imported from an external source. The data is imported in rows. For example row 1 is the data name (header). Row 2 is data set 1, row 3 is data set 2, row 4 is data set 3.etc.
- Worksheet 2 just has the data transposed into columns. So the header is column A, column B is data set 1, colum C, is data set 3......etc. etc.
- Worksheets 3, 4 ,5....and so on are the presentation sheets (printed reports will be generated from these sheets.
- Worksheet 3 only requires data from column B of Worksheet 2. However the presentation of Worksheet 3 requires the data to be in certain cells (not in a single column format)
What I have done is set up Worksheet 3 to look exactly as I need it by referencing the appropritate cells in Worksheet 2, column B. So Worksheet 3 is basically a sheet of cells referenced back to Worksheet 2, column B in a user friendly format that can be printed.Here is where I need help. Worksheet 4 is an exact copy of Worksheet 3 but needs the data in column C of worksheet 2 instead of column B with the rows being consistent. For example cell D35 in Worksheet 3 needs the data in cell B15 of Worksheet 2 and cell D35 of Worksheet 4 needs the data in cell C15 of Worksheet 2.Since worksheet 3 is set up I made a copy of it to become Worksheet 4. My question is how do I change all of the cell references to column C without manually doing it. Is there a function I can use. Any help would be appreciated.