Getting data from a different sheet and incrementing by a value

[Teaman]

Hi everyone, I'm working with data in one sheet that has it in columns and another sheet that needs it the data in a single line to be separated into rows. I have most of it figured out with multiple formulas, but can't seem to get it to increment automatically and have to copy the block of data and manually change the cell reference. Below is the formula that gets the data for one cell in the second sheet. It gets data from two cells and combines them. It's pretty simple.

='NB1804'!H138&":"&'NB1804'!A138

"NB1804' is the first sheet and it gets the value from H138 and combines it with A138. In the second sheet I just copy this data to the next 6 lines that it's needed in.

I then need to drop down one line in 'NB1804' and grab the next data in H139 and A139. I tried nesting the ROW function to get the row number and to add 1 to it, but I get an error. It looked like this: ='NB1804'!(ROW('NB1804'!A138+1)), but it doesn't like that at all. It seems that going to the other sheet is causing problems for me. I can get this to work in the same sheet: =ROW(H137)+1&":"'NB1804'!A137. It returns the row number, but I can't get it to add that to the reference from the previous row to get the next row. If I try =A257+ROW(H137)+1&":"'NB1804'!A137, I get #VALUE! in the cell. If I can get this to work, it needs to reference a cell in the first sheet rather than the active sheet and add a number to it. In four cases I'm adding 1 and then I need to jump to the next set of data and add 9.

Can anyone help?

Thanks.

[Fotis1991]

wHY DON'T YOU JUST DROP DOWN, THIS?

='NB1804'!H138&":"&'NB1804'!A138

[Teaman]

Thanks, I'm sort of doing that now, but the way the data is laid out in the sheet NB1804, I can't go line by line like you suggested. The first row in the NB1804 sheet needs to be duplicated 6 times with different column data from a single row in NB1804. Maybe I should have attached the spreadsheet I'm working on, it would have been much easier to understand.

You can see there are three different types of data, they are a Motor, a power monitoring device, and an interfacing device. The motor is duplicated several times, there is one power monitor and one interface for each of these files. Not all files have the same number of motors, so this needs to be somewhat customizable.

If you look at the sheet labeled Alarm Mgmt, you can see that any data type in column D that is a RIN, needs to be duplicated 6 times to account for all the parameters that need to be imported into a different system than the spreadsheet that is labeled NB1803. Any data in column D that is a CIN is duplicated twice. What I want to do is set the first set up which starts at row 2 and goes to row 17, then copy that down to the next set that starts at row 19 and goes to row 34 and have all references move to the next set of data in the NB1803 sheet. When I simply copy the block, the cell references increment by 17 and I need them to increment by 9.

Then we get to the different data of the power monitor which has more RIN data types in column D and needs to increment differently than the motor from the pattern above. Again there is only one of these, but I'd like to be able to duplicate the motors as many times as I need, then copy the Power supply pattern in and have it automatically grab the data after the last motor.

Last is the Gateway, the interface. It only has two CIN data types in column D and I'd like it to increment after the start of the power monitor.

I will be doing this for approximately 120 spreadsheets, some of them probably 3 times the size of this one. It took me about 5 hours to modify it by hand.

It's easy enough to look at the simple formulas in each yellow cell and see where it's getting it's data from. Each group of 6 for a motor comes from a single line in the NB1803 sheet.

[Teaman]

The spreadsheet I attached yesterday has some errors I found last evening. All references for each block in the Alarm Mgmt sheet should have the same line number reference. For example lines 2 through 7 should all reference line 3 in the sheet NB1813. I also noticed my previous post started mentioning sheet NB1814 which was an unfinished spreadsheet I was working on at the time, I sent a completed one. Sorry if that caused any confusion.

Hopefully someone can help, this seems like it shouldn't be that complicated, I just can't seem to put my finger on the solution.

Thanks
TeaMan