Lock source links

vicwra

I've created a web form that feeds a very simple tracking sheet. Each week, the person providing the info will submit their data via web form and the information flows to row one of the tracking document.

 

In a separate sheet, I've created a dashboard that should, in theory, pull the latest data and compare it against monthly goals. 

 

What I cannot figure out - if it's even possible - is how to lock the cell links so my dashboard is always looking at the data in row one. Right now, it's linked to row one but next week, when the new form comes in, this week will become row two and the links follow.

 

Is it possible to lock this down?

 

Thanks!

Atus Bartal

You can set the form to put the entries to the top of the sheet. I think that will work (can't check at the moment)

vicwra

I set the form to feed latest data to the top of the sheet thinking the link would then always look at the top row. However, the links are following the specific data I pointed it to, rather than staying with the top row. Does that make sense? 

Atus Bartal

Oh yes, it does make sense, and also, it's logical that cell linking is following the specific data. At the moment I have no idea about the possible solutions...  Sorry.

Richard Rymill SBP

Hi Send the data from your forms to the bottom of your sheet (so becoming a child Row) , then using hierarchy to to sum all the data you wish to have "Transmitted" to your dashboard from the Parent Row of your Hierarchy by Cell Linking.to the dashboard. No matter how many new Rows arrive, the sum totalling continues.

Assuming you have already decided on the Logic behind your formulas, you have done it. Can be useful to have a dashboard in the landing sheet too?  

Job Done. 

 

Good luck

RichardR

vicwra

Thanks RichardR. For future forms, I'll remember that - great idea!

 

For this particular project, I need to pick up the new data standalone, rather than through a total of each week. The person submitting the info provides new year to date numbers weekly, rather than a synopsis of just one week at a time.

 

I'm thinking I need to start from the beginning and rework how he is submitting data, rather than trying to fix it on the report side.

Richard Rymill SBP

Definitely worth spending time on the design of the workflow so it is intuitive to all users and ideally makes life easier for all of them including you. You will know you have it right when everyone is smiling.. 

If you get stuck, let us know our team love new challenges.

 

RichardR