transfering a row of cells between sheets

Microsoft Excel 2003 (full product)
September 17, 2009 at 04:12:05
Specs: Windows XP
I am stuck on trying to get data from one sheet to another using a drop down menu as the trigger.

What I have is a spreadsheet for comparing # of completed tasks on a weekly/monthly/yearly basis for 12 different employees.

What I want to do is by selecting the week in a drop down menu on the Primary sheet, to have the data corresponding to that week (from the table on the Data sheet) be displayed on the primary sheet.

See More: transfering a row of cells between sheets

Report •

September 17, 2009 at 05:08:46

Here is one way to do this.

If your data is on a sheet called 'Data' with employee names in column C and weekly data starting in column D and continuing to BC.
With the first employee data in row 4, and continuing to row 15.

In the primary sheet:
The week number will be entered in C3.
Use data validation with a list to give you the 52 weeks drop-down
In cell C4 enter the formula
In C5 enter
and so on for all 12 employee's data
(You can drag this formula down the remaining rows, but you will have to go back and edit the first value after 'ADDRESS(' as it will not have incremented automatically).

Entering a week number in C3 will bring up that week's data for your 12 employees

So that you can see the employees name in cells, start at B4 and enter
You can drag this formula down the remaining 11 rows.

Others may be able to suggest ways to shorten the formulas, but this way you can see the elements that make up the addresses.


Report •

September 17, 2009 at 05:54:23
I tried it exactly as you had it put in and everything else looks the way I want it to, except for the cells using the INDIRECT formula. all of those are coming up #VALUE!

I tried formatting the data to various different type (general, text, numbers, custom, special) but none of them made a difference

Report •

September 17, 2009 at 06:31:25
I got it figured out. It was not recognizing the "date" as a valid progression. I added another row I titled week # and ran it 1-52
reffenced that instead of the date and it worked fine.

thanks for all your help

Report •

Related Solutions

September 17, 2009 at 07:06:56
Your welcome

Report •

September 17, 2009 at 07:59:54
Just an FYI...

Instead of manually changing the first value after 'ADDRESS(' in every cell, here is a trick I often use:

Using your example of the INDIRECT formula being in C4, and the first value after ADDRESS needing to be 4, use:


As you drag this down, ROW() will evaluate to 4, 5, 6, etc.

As another example, if you need the formula to start in Row 1, but still need a 4 after ADDRESS, use:


The ROW() and COLUMN() functions, used either by themselves or with an "offset" will often allow you to autofill formulae throughout a range.

Report •

September 17, 2009 at 09:05:08
Hi DerbyDad03

Thanks for the suggestion on ROW() and COLUMN() functions


Report •

Ask Question