Fiddly question on auto-populating cells

[BristolJGM]

Hi everyone

I’ve set out my question below – any help gratefully received. I’m quite new to Excel – I think it’s quite a tricky question and I’ve tried to ask it in as clear a way as possible. It may be that what I’ve asked simply isn’t possible at all. Maybe with a macro or something.

- I have an Excel spreadsheet with two worksheets.
- Worksheet 1 has numbers 1-100 in column A (i.e. cell A1 is 1, A2 is 2, and so on)
- Column B has some different values, the detail of which is explained below.
- Worksheet 2 has number 1 in cell A1 and then in cells B1-5 it has a range of different numbers between 1 and 100. Let’s say that it has 3, 31, 29, 8, 17. I will be typing in these numbers manually.
- Then in cell A6 it will have the number 2, and 5 more different numbers in cells B6-10, and so on, cell A11 the number 3 and 5 more different numbers in cells B11-15, etc.
- Now we come onto cell B1 in worksheet 1 – this is the one I need help with.
- What I would like this cell to do is easiest to explain with an illustration. I would like it to say =AVERAGE(B3,B31,B29,B8,B17). I’d like it to auto-populate the numbers to average out based on the numbers in column 2 of worksheet 2 (which I’ve typed in manually).
- I don’t know how make cell B1 of worksheet 1 auto-populate based on the numbers from B1-B5 in worksheet 2 (or if it’s even possible).
- I would like this to continue for the remaining numbers. Let’s suppose for example that in cells B6-10 of worksheet 2, we have the values 34, 58,23,6,87. I would like cell B2 on tab 1 to auto-populate (based on the info in cells B6-10 of worksheet 2) with =AVERAGE (B34,B58,B23,B6,B7)

The reason for doing this is that I am prepared to manually type in the values in worksheet 2 but would like Excel to use these values to auto-populate column B in worksheet 1 - to cut down the time spent on data entry and reduce the risk of data entry error.

Thanks!

[mehmetcik]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[BristolJGM]

Thanks for the quick reply. I've now attached two Excel files - the first one showing the position before the automation process, and the second one showing the position afterwards. These are based on the numbers in my original post. Cells B1 and B2 in worksheet 1 need to be automatically completed based on data in worksheet 2.

Here, this produces two DIV/0 errors in cells B1 and B2 but it doesn't matter - you can see what entries have gone into these cells in the 'After' file.

Thanks for the help!

[mehmetcik]

Your requirement is not understood

which range are you trying to get values for?

Both Sheets 1 and 2 have data in column B

[Dineth]

if you need to refer another worksheet type worksheet name and ! mark

e.g. if you have sheet named w1 and w2, and if you are in w1, say in cell b1 if you type =AVERAGE('w2'!B1:B5) it gives avg of b1 to b5 in wroksheet 2.

see the file

[BristolJGM]

if you need to refer another worksheet type worksheet name and ! mark

e.g. if you have sheet named w1 and w2, and if you are in w1, say in cell b1 if you type =AVERAGE('w2'!B1:B5) it gives avg of b1 to b5 in wroksheet 2.

see the file

Thanks for the reply but what I'm trying to do is harder than this.

Your 'After' spreadsheet at Worksheet 1, cell B2, is averaging B1 to B5 of Worksheet 2. It needs to be doing what my 'After' spreadsheet is doing, i.e. averaging cells B3,B31,B29,B8,B17 of Worksheet 1.

The difficulty is finding a way to auto-populate cell B2 of Worksheet 1 using the 'raw' data from Worksheet 2. In other words, take the 'raw' numbers 3, 31, 29, 8 and 17 from Worksheet 2 and use them to generate a cell in Worksheet 1 which averages B3, B31, B29, B8 and B17 from Worksheet 1.

My cells B1 and B2 of Worksheet 1 of the 'after' spreadsheet are doing the right thing but only because I typed them in manually, I need these cells to appear automatically based on the data in Worksheet 2.

[BristolJGM]

Your requirement is not understood

which range are you trying to get values for?

Both Sheets 1 and 2 have data in column B

Essentially, make cells B1 and B2 of Worksheet 1 in the 'after' spreadsheet appear automatically. The only reason these cells are there in the 'after' worksheet is that I typed them in manually. I need something to auto-generate the same cells (or cells with the same effect) using the data in Worksheet 2.

The difficulty in this is translating the 'raw' numbers in Worksheet 2 to auto-populate these two cells in Worksheet 1.

I'm not sure if there's a way of doing this at all...

[BristolJGM]

Thanks for the replies, by the way - sorry my explanation isn't very clear...

[Dineth]

I think this can be done, what we need is
formula to convert your numbers in to cell addresses e.g lets say you have 5 in second worksheet b1 cell, in c1 we can write ="B"&B1 it will give the cell range. if u need add a comma s well.
so c col is your ranges.

in worksheet 1, again write a formula something like ="=average("&....
dotted line for continue the formula to get the ref from c col in wrksht 2.

let me try a file.

[BristolJGM]

I think this can be done, what we need is
formula to convert your numbers in to cell addresses e.g lets say you have 5 in second worksheet b1 cell, in c1 we can write ="B"&B1 it will give the cell range. if u need add a comma s well.
so c col is your ranges.

in worksheet 1, again write a formula something like ="=average("&....
dotted line for continue the formula to get the ref from c col in wrksht 2.

let me try a file.

Thanks. I finally managed to ask the question correctly...

[BristolJGM]

Did anyone manage to figure this one out? (Or to conclude that it's impossible...)

[BristolJGM]

I think I've (half) figured this out for myself... I think the INDIRECT function can be used to do it.