Tech Support Guy banner
Status
Not open for further replies.
1 - 8 of 8 Posts

·
Registered
Joined
·
125 Posts
Discussion Starter · #1 ·
I am having trouble finding an easy way to copy and paste the following data so that it will automatically fill in the correct formula. Every fourth row the number increments by one. The example below shows 4 rows that refer to the 69th row before it moves to the 70th row. If I were it copy and paste this it would increment every row. The paticular workbook I am working on has 1000 rows and 22 columns. Every column in a row refers to the same row but sometimes a different page.

Example:
=IF('Summer Input'!F69="","",'Summer Input'!F69)
=IF('Summer Input'!H69="","",'Summer Input'!H69)
=IF('Summer Input'!G69="","",'Summer Input'!G69)
=IF('Summer Input'!I69="","",'Summer Input'!I69)
=IF('Summer Input'!F70="","",'Summer Input'!F70)
=IF('Summer Input'!H70="","",'Summer Input'!H70)
=IF('Summer Input'!G70="","",'Summer Input'!G70)
=IF('Summer Input'!I70="","",'Summer Input'!I70)

Thanks,
Joe
 

·
Registered
Joined
·
869 Posts
Hi and welcome on board!
It can be done, by adding an additional column and modifying a little initial formula, or it can be done by a macro!
Say what you chose and we'll try to do this!
 

·
Registered
Joined
·
125 Posts
Discussion Starter · #3 ·
It does not matter to me I have tried both. When I try the macro it always changes the same cell instead of going down the page, and fixing all 1000 rows. I am not a visual expert but I am trying to learn. As for adding a column I added a column and put a number in as a reference for four cells. Example: If all my cells referenced H80 but on different pages, but I have many columns and usually the column will change from H to G to I. I tried to just use 80 and ended up with something like. HC1, so instead of putting the value of the cell it put in the cell location. I hope you know the fix because I have spent hours on this. Copy paste change all the reference locations. This is both time consuming and leaves a lot of room for errors. So I am definately looking for a different way.

Thanks for the help,
Joe
 

·
Registered
Joined
·
869 Posts
First! Instead of :
Code:
=IF('Summer Input'!F69="","",'Summer Input'!F69)
I would just use
Code:
='Summer Input'!F69
And second look at the attached file, Is it near to what you need?
 

Attachments

·
Registered
Joined
·
125 Posts
Discussion Starter · #5 ·
Thank you,
I think I got it. I have another question which I am going to start a new thread for, but I am not sure how to ask it. I have a Transmission line that goes from one location to another. Each location has a name and a number. Every line has is identified by The location it is coming from to the location it is going. The lower number of the two locations is alway where it is coming from. I receive many cases with a lot of lines, but they might not always contain all lines.

In my example I attached I would like Excel to automatically add a line to the September 2008 section so that the line 3z 4t lines up with the same line in August 2008. I sometimes have to deal with over 20 sections and 20,000 lines and it is difficult to go through and line them up to compare data. A new line could come in later or a line could go out of service, so I can't necessarily think I will have 3 line just because my first case has three. I think access does this well, but I would like to do this in excel. I think I described it well enough so I will post, but if you have any suggestions I would appreciate your input. I will also let you know if I can get the first problem fixed but after going through the formula in your attached worksheet, (for about an hour, I know I am slow), I think I can perform the comparison.

Thanks again,
Joe
 

Attachments

·
Registered
Joined
·
869 Posts
How about having all this sections on separate sheet, and a master sheet in which to do the comparison thing?
 

·
Registered
Joined
·
869 Posts
Please mark this thread as solved!
And we'll continue in the other thread you started!
 
1 - 8 of 8 Posts
Status
Not open for further replies.
Top