Is there a way to Link Horizontal Cells in Book1 to Vertical Cells in Book2

[itsaysmoops]

I am wondering if there is a way to link a group of horizontal cells from one book to a group of vertical cells in another book. For instance if I wanted to link cells C10, D10, E10, F10, G10 from Book 1 to Cells A20,A21,A22,A23,A24 in Book 2?

I have been copying and Special Paste - Linking each cell one by one and while I am facing a daunting task of linking hundreds of cells, I am hoping to find an easier way.

Is this possible?

I would appreciate any help on this!

[GeneralDisarray]

what identifies each table? can you put up the workbook(s) and not just a screen shot of them (in another workbook)

[itsaysmoops]

sure give me a few mins

[itsaysmoops]

Book3.xlsx

basically on the tab with the charts it is going to keep track of a task completion percentage broken down into periods. i have a data sheet for each period that I am linking to each period's corresponding chart. if that makes sense? i dont know if i am explaining it that great but I am not an excel wiz..

thanks for your help

[GeneralDisarray]

don't understand the design with what is attached. looks like you have sheets labled by period and on each of those period sheets you would like to present the information from the totals table??? do the period sheets look like the DataList sheet in what you've attached?


If that is the case...you could redesign the workbook to provide a dynamic table range (using a named formula in through the name manager) to point to the totals table for each sheet, then use the INDEX/MATCH design to pull the numbers out.

That's alot to redo (possibly) and if you don't know how to make a named range (or named formula if you prefer) and use INDEX/MATCH combonations to reference that table...you are going to have to post more book

[itsaysmoops]

What I was trying to accomplish is to have the cells on the chart automatically populate from the data collected on each period's assignment tracker. I have created 13 Period trackers that will feed the charts. If you will see on the p6pmassignments.xslx file, under the tab with just a person's Name there is a column that says "done". once a certain number is placed in that column to indicate the task complete, it gets tallied on the data list sheet. from there those sums feed the PM Charts book. So each time another task is completed and tallied, the chart is automatically updated. I have never really had any formal training with excel, everything has just been trial and error. Up to this point I have been linking each cell manually for each period. Here is one that I already have linked to the charts. I can continue on the way I have been going linking each cell one by one, but I was hoping to save some time with an easier route.

You mentioned a dynamic table, I am not sure what that is really. If it would make what I am trying to do a little easier, i would not have a problem redoing the books.

again, thanks

[GeneralDisarray]

not completely sure what to tell you, quite a lot of sheets to look at i think you would maybe benefit from a redesign, but how are you using the sheets? Are individual people filling out there tab? Like, Primo opens the book fills in primo's tab -- or are you doing it for them?

--

Also, about dynamic ranges....

Just to give you an example of a dynamic table (or named formula) check the drop down boxes on "Juan" cells H3 & G3. Notice you no longer have a gigantic area of white space to deal with if you check the data validation on each cell you will see a named formula (=done_list_choices for H3, and =est_hours_choices for G3). To see how those formulas are defined go to the name manager (Press ctrl+F3 to get there).

='Data List'!$P$2:INDEX('Data List'!$P$2:$P$11,MAX(COUNTA('Data List'!$P$2:$P$11),1)) -- >Defines the "done" column drop down choices...this will automatically include any new entries in the Data List tab's "done list" you. (so, if you add stuff you don't have to re-validate the cells, and you avoid passing in an entire column).

[itsaysmoops]

Under the "Juan Assignment" sheet I would create a task to be completed form by using the drop downs, and then print it out. he would take the sheet with him and document his completion, when he returns the form to me, I would document the tasks he completed in the "Juan" sheet, the tallies taken from the Juan sheet done column would feet the data list which would feed the chart

[GeneralDisarray]

bingo...so when he returns his completed assignment sheet you could dump the data into another format...is the Juan sheet (that you complete) only there to collect data to be then viewed on the Data List sheet (that sheet is then used to feed the charts...)

If I were you, i would consider using a macro to "dump" each completed assignment sheet to one "catcher" sheet. So instead of entering a Juan sheet (based on Juans Assignment sheet) you would run macro to add his data to the Catcher sheet (and would identify it was from Juan by maybe adding in a Juan name field (field = column). you could then use pivot charts/tables and subtotals to recreate your reports.


Basically you could automate the data capture, replace the sheet-for-every-user idea with a catcher type sheet (that's a common term for this approach) and have most if not all of the reports/charts generate automatically using built in excel features...

could save you much time in the long run, btw, did you like the named formula example?

[itsaysmoops]

Yes, that was helpful, thank you!

i grasp the concept of what you are saying and it sounds like that is exactly what I am looking for, just not sure if i know how to execute it. obviously there is a wealth of information here in these forums and I am not sure where I would begin to look to learn how to do that.

I really appreciate you taking the time to help me in this. This project was kind of just dumped into my lap without any direction.

[GeneralDisarray]

one step at a time actually a similar issue spurred me to learn excel VBA in the first place. Do you have an example of a few filled out Assignments you can share(cleaned of all proprietary data). Do they come to you as separate files?

I could try to give you something that would make a catcher file out of those sheets which you could then use.

[itsaysmoops]

here is a basic example of what 2 completed Week 1 assignments would look like.

[GeneralDisarray]

ok, here is a giant head start for you.

Basically, there is a macro in this book that will fill the catcher based on any "assignment" sheet in the same book...i don't know if you usually do it this way, or if the assignments come to you in separate books -- the procedure can be modified to work in either case.


The catcher is "captured" by a named range, which is tied to the pivotExample tab. When you activate (click on) the pivotExample sheet the pivot chart / table will refresh to reflect the latest catcher -- in fact any pivot table or chart on that tab will be refreshed.

I had to change a couple of things to make it work well (no merged cells! they are not your friend) and noticed that your Done column came from the left 3 characters of the another column (so can be eliminated until the catcher needs it).

Take a look. If you don't know about macros, you need to enable this content to run to see anything happen in this workbook. ENjoy,

basic macro:

Please Login or Register  to view this content.

[itsaysmoops]

Thanks, you have been a tremendous help! I will play with this tomorrow when I get back to the office, hope you have a good evening!

Thanks again!