2

Please note: I am aware that the function I am using will cause a circular reference. I am trying to find a work around for this problem.

I have a simple excel report which I use to fill in weekly data and expect some other columns to get auto-populated based on data entered for current week. Please take a look at the screen shot below: enter image description here

The Column B is filled by me, column C ("Total Till Date") is auto-calculated each week. So for row#11, the effort till date for QA (Cell# C11) is C4+B11.

The problem is I want to represent the data in one table, like so: enter image description here

The Idea here is, everyweek, I just update the "Effort this Week" cell and the "Total Till Date" cell for each (Dev or QA or PM) gets updated. The Formula would be something like:

C24 = C24+B24

Of course this will cause a circular reference. How can I update a given cell by adding the cell value to another value? I am trying to avoid using VBA and cell change events. Can it be done using just the excel itself?

4
  • I'd like to help, but I'm not sure what kind of "one table" you want to get. The wanted table that you shown is just the same as all the previous, except it says "Week-n" instead of for example: "Week1", so it doesn't explain much. Would you want to have one new table that would have a list of all the previous weeks and totalsTillDate in each week?
    – userfuser
    Commented Jul 27, 2015 at 10:16
  • @userfuser I mean a single table that gets updated week-after-week. The table Week-n represents a single table that gets updated every week. By "Table" I simply meant a range. nothing special. So week-n in my question represent how the table looks after 3 weeks. Week-1 through Week-3 represent how the table looks like after end of each week. Does it help?
    – Ayusman
    Commented Jul 27, 2015 at 10:19
  • I think I understand now what you mean but I'm afraid that's not possible in basic Excel. You want a cell to server as an input and and an output field at the same time. It is possible to get what you want with a help, for example, of another sheet, where you would just keep the data and then use the main sheet for just the overview. Let me see if i can some make a simple demo
    – userfuser
    Commented Jul 27, 2015 at 11:13
  • I feel VBa will be a much easier solution - and the code will be very small and managable
    – Dave
    Commented Jul 27, 2015 at 12:58

1 Answer 1

1

As mentioned in my previous comment, I think your original idea is not possible in basic Excel, but here is one way to have it - with simple Excel functionality (2 Excel functions: VLOOKUPand SUM).

First image is how would your 1st sheet look like:

The Overview sheet

And below is the looks of the 2nd, the data sheet - in which you would enter some data, when and if you have it. So this sheet doesn't normally show:

The data entering sheet

Would that work for you? Here are some explanations of what happens:

  1. When you need to enter the data for the week XX, you go to "Data" sheet and enter the values in appropriate column of the appropriate row.
  2. It shows automatically in sheet "Overview"

How is "Overview" sheet working:

  • the number "31" shown in C1 is the number of the week - automatically calculated by =WEEKNUM(NOW()) in C1 column
  • values in B3 are calculated by having VLOOKUP($C$1;Data!$A$2:$D$53;2;FALSE) inside of it. What it does is:
    • takes the value from C1 (being 31),
    • looks for it insides the first (index) column of a range in the Data sheet (specifically, the range of: Data!$A$2:$D$53)
    • when it finds the entry with value of 31, it picks up the value from the 2nd column in that range (column for "Dev")
  • the same logic applies to other fields (B4 and B5)
  • Values in C columns are a simple =SUM(Data!$B$2:$B$53)

BTW, all those $ signs that you see, are just there so that the cell-ranges - specified in one cell's formula - do not auto-change when you copy them over to some other place. Feel free to ignore them for this answer.

4
  • It's a nice lookup function in Excel, that you use to fetch the content of some cell in specific row, while that row is being found by comparing it to some index.. Hmm, let me explain with a bit more details in the proposed answer.
    – userfuser
    Commented Jul 27, 2015 at 11:51
  • Odd indeed.. Maybe it comes as a part of some additional Excel module, analysis pack or something like that. I normally always install it all. Here's MS link for it: support.office.com/en-in/article/…
    – userfuser
    Commented Jul 27, 2015 at 12:13
  • Huh, you're right. It's VLOOKUP. My apologies. I actually wrote it wrong initially and then later I wrote correctly in the code examples. Also this link to MS website is going to the correct function.
    – userfuser
    Commented Jul 27, 2015 at 12:44
  • Naturally, corrected already. Hopefully that was not the reason for someone's commentless downvote on my answer.
    – userfuser
    Commented Jul 27, 2015 at 12:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .