Drag formula into every other row

[spartain]

I would like to drag a formula (with links) into every other row. For example, I have a list of items:
Apple
Orange
Lemon
Lime
.
.
.

And I want to make a new tab that looks links to the list that looks like:
Apple
--
Orange
--
Lemon
--
Lime
--

And so on, where the "--" marks indicate an empty row that allows user input. Does anyone know how this can be done? Is there any way I can structure the references so that I can just drag the formula down and it links appropriately?
Thanks

 

[SkipVought]

Hi,

What is the purpose if the interspersed user input?

Why not have the user enter at the bottom of the list?

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[spartain]

I'll explain more about the purpose of my question. This is for a manufacturer, who wants to monitor actual production against forecasted production for each item. The tab I am creating lists the forecast production for each item and week of the year in one row, and allows the user to input the actual production for that item and that week in the next row. For example:

Item: Week 1 Week 2 Week 3 ...
Ball -Forecast 25 26 26
Ball -Actual 29 20 21
Cone -Forecast 12 10 9
Cone -Actual 12 10 5
...

The forecast data cells will link to another worksheet where the numbers are stored in sequential rows.
I realize I could set it up so that the "Forecast" and "Actual" cells were in the same row under Week 1, etc., but then I would be faced with the same issue (only trying to get the link to skip a column, rather than a row).

Does this help?
Thanks.

 

[spartain]

Just in case it isn't clear, the "Actual" rows will be where the manufacturer manually enters production information every week.
Thanks.

 

[Beowulf005]

Couln't you use the "Sheets" to complete this task?
IE
Sheet 1 = Forecast
Sheet 2 = Actual
Sheet 3 = % difference

Then you will only have to copy the Items from one sheet to the other.

 

[arthurbr]

Hello Spartain,
you could also think of letting the user add the actual after your forecast list ( without interspersing anything), and then use a Pivot Table to analyse the results.

PT is fast, reliable, and will autoupdate on opening if you chose to do so.

 

[spartain]

Thanks for the replies. If possible, I would like to keep the "Actual" information as close to the "Forecasted" as possible for usability reasons: for each item, the user will be going off of the forecasted information to make a decision about how much to schedule (or put into the actual row). Separating the two, so that the user has to do some manipulation to look at both sets of data at once will make it more cumbersome to use. I'll do what I have to, but I would obviously like to make it as easy as possible.

 

[Zathras]

Since this is not the VBA forum, here is a non-VBA way that you could use:
Starting with Sheet 1:
[tt]
Thing a b c
Apple 1 2 3
Orange 4 5 6
Lemon 7 8 9
Lime 10 11 12
[/tt]
Then on sheet 2 put this in A2:
[tt]
=Sheet1!A2
[/tt]
Copy from A2 to A25
Replicate the headings.
Put "x" in E1
Put numbers in column E, 1 thru 4 twice like this:
[tt]
Thing a b c x
Apple 1 2 3 1
Orange 4 5 6 2
Lemon 7 8 9 3
Lime 10 11 12 4
1
2
3
4
[/tt]
Now sort on columns E and A and you get this:
[tt]
Thing a b c x
Apple 1 2 3 1
1
Orange 4 5 6 2
2
Lemon 7 8 9 3
3
Lime 10 11 12 4
4
[/tt]
Delete column E and you're almost home.
You can tweak the labels in column A before sorting for a prettier result if you wish.

 

[spartain]

Many thanks to all you who gave me input on how to handle this. I finally figured out a way to accomplish this exactly as I have it by writing a formula. I was able to do this by taking advantage of the row indices of the data, both of my source list and the linked list.
My source list (the one in sequential rows) started on row 3 of the source worksheet, and the linked list started on row 11 of the destination worksheet. This means that the data on row 3 needed to be in row 11, the data in row 4 needed to be in row 13, and so on:
Source Destination
Row 3 = Row 11
Row 4 = Row 13
Row 5 = Row 15
Row 6 = Row 17
...

If you treat the source row indices as "x" and the destination row indices as "y", you can get the following relationship: (Source row) = .5*(Destination row)-2.5
Now you know the source row for any destination row. The columns stay the same, and so you can fill the desired links in all of the odd rows using "ADDRESS" and "INDIRECT". The only thing remaining is to use an "IF" statement and "ISEVEN" command to leave the even rows blank. The final equation looks like:

=IF(ISEVEN(ROW(E11))=TRUE,"",INDIRECT(ADDRESS(ROW(E11)*0.5-2.5,11,4,TRUE,"Source Sheet")))

It's not the prettiest equation, but it works!
Thanks again for the help!
Than