Cell Reference editing formula

[jrcrutc]

Hello all. I am trying to create a live editing list from tabulated data. I have a workbook with multiple worksheets that contain data in tabulated form. I have linked all the pertinent data from the different sheets to one single sheet for ease of visual checks. I used the normal method of (=Sheet1!A1) format. I then created the column headings that I wanted for my list on a new sheet and I am trying to get the information that I have in a row on my summary table to populate (with references) into the correct columns. I have pasted in a sample of the page in question below. Column 4 does not really need to live update. I have figured out a system for that. Let's take the last column in the sample below, I have roughly 40 columns of data in the table on sheet1 and I need to get that data into 1 column and 40 rows in an organized fashion that will live update when the other sheets that it references are changed. If I Highlight the whole column and drag down it takes the first item (=Sheet1!$B$4) and makes it (=Sheet1!$B$44) because it has 40 rows below it in the active sheet. Normally this would be the correct action, but I need it to go from (=Sheet1!$B$4) to (=Sheet1!$B$5) after I have the information from C4 through AV4 in the active column. I can't just do them one at a time (meaning put all of the items from B column into this new sheet and then move to C column) because I have an indeterminate number of items in B column from the original reference sheets. I have tried using several types of table to list macros, but I can't get the formatting to come out right, and I can't get them to work on a continual bases. Any suggestions you can provide would be greatly appreciated. If more clarification is required, please let me know. Thanks.


=Sheet1!A$1 =Sheet1!A$2 =Sheet1!A$4 =Sheet1!B$3 =Sheet1!$B$4
=Sheet1!A$1 =Sheet1!A$2 =Sheet1!A$4 =Sheet1!C$3 =Sheet1!$C$4
=Sheet1!A$1 =Sheet1!A$2 =Sheet1!A$4 =Sheet1!D$3 =Sheet1!$D$4
=Sheet1!A$1 =Sheet1!A$2 =Sheet1!A$4 =Sheet1!E$3 =Sheet1!$E$4
=Sheet1!A$1 =Sheet1!A$2 =Sheet1!A$4 =Sheet1!F$3 =Sheet1!$F$4
=Sheet1!A$1 =Sheet1!G$2 =Sheet1!G$4 =Sheet1!B$3 =Sheet1!$H$4
=Sheet1!A$1 =Sheet1!G$2 =Sheet1!G$4 =Sheet1!C$3 =Sheet1!$I$4
=Sheet1!A$1 =Sheet1!G$2 =Sheet1!G$4 =Sheet1!D$3 =Sheet1!$J$4
=Sheet1!A$1 =Sheet1!G$2 =Sheet1!G$4 =Sheet1!E$3 =Sheet1!$K$4
=Sheet1!A$1 =Sheet1!G$2 =Sheet1!G$4 =Sheet1!F$3 =Sheet1!$L$4
=Sheet1!A$1 =Sheet1!M$2 =Sheet1!M$4 =Sheet1!B$3 =Sheet1!$N$4
=Sheet1!A$1 =Sheet1!M$2 =Sheet1!M$4 =Sheet1!C$3 =Sheet1!$O$4
=Sheet1!A$1 =Sheet1!M$2 =Sheet1!M$4 =Sheet1!D$3 =Sheet1!$P$4
=Sheet1!A$1 =Sheet1!M$2 =Sheet1!M$4 =Sheet1!E$3 =Sheet1!$Q$4
=Sheet1!A$1 =Sheet1!M$2 =Sheet1!M$4 =Sheet1!F$3 =Sheet1!$R$4
=Sheet1!A$1 =Sheet1!S$2 =Sheet1!S$4 =Sheet1!B$3 =Sheet1!$T$4
=Sheet1!A$1 =Sheet1!S$2 =Sheet1!S$4 =Sheet1!C$3 =Sheet1!$U$4
=Sheet1!A$1 =Sheet1!S$2 =Sheet1!S$4 =Sheet1!D$3 =Sheet1!$V$4
=Sheet1!A$1 =Sheet1!S$2 =Sheet1!S$4 =Sheet1!E$3 =Sheet1!$W$4
=Sheet1!A$1 =Sheet1!S$2 =Sheet1!S$4 =Sheet1!F$3 =Sheet1!$X$4
=Sheet1!Y$1 =Sheet1!Y$2 =Sheet1!Y$4 =Sheet1!B$3 =Sheet1!$Z$4

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jrcrutc]

FDibbins, Thanks for the suggestion. I have now attached a dummy workbook that shows the issue. I have manually gone through one iteration, but as we all know humans make mistakes and I would like to use some type of formula or other method to automate what I did from rows 42 to 81 of sheet 2. Thanks for any help anyone can provide.

[FDibbins]

how representative of you're actual data is you're sample, with regards to the number of columns in sheet1?

[jrcrutc]

The number of columns is exactly the same in the dummy workbook as it will be in the real situation. This column number should be static, it is of course the number of rows that i can not predict on sheet 1.

[FDibbins]

see if this is what you want? I found mis-matches between what it seems you are asking for, and what you showed that you wanted?

[jrcrutc]

Thanks for the response. I can see where you can get confused. On sheet1 the JJJJ columns are "date" columns in the real situation, that actually get their own column on sheet2 (the Heading 3 column) and they have to be paired with the KKKK through OOOO columns on sheet one. My example is exactly what I want to reproduce. The obfuscation makes this difficult to explain. If further explanation is required please let me know. I will look at your code and see if I can adapt it to what I want to do. If I figure it out I will let you know. Thanks again for the response.

[jrcrutc]

Ok, I think I understand the offset code (to a small extent) but I can't figure out how to get it to skip the JJJJ columns. I also can't get the JJJJ column to fill out heading 3 like I want. Any help you can provide would be great! Thanks for your help so far.