Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference to worksheet fails if row deleted
I have 8 colums of information for one item in one row and I need to change
that to about 8 rows of information for the same item. Such as: A B C 1 stuff junk other needs to become: A 1 stuff 1 junk 1 other there is other stuff involved, but this is the heart of the matter. I have created a new spreadsheet that pulls all the information from the first spreadsheet, exactly as I want it. I thought I would be able to pull the information for the one item, then copy and paste as a value to another spreadsheet, then go back to the first spreadsheet and delete the first row, moving the second row to forst row position, and then the new spreadsheet formulas would pull the information from the new first row in the first spreadsheet. But Excel, in all its wisdom, deletes in the formula worksheet the reference to the first row (giving a #ref! error) when the first row in the first data worksheet is deleted. Is there a way to stop Excel from being so smart? If the formula spreadsheet will just pull the data from the first row of the data worksheet, even if the row is deleted and another row moves up, then I can accomplish my task. Any help is much appreaciated!! Jane |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference to worksheet fails if row deleted
If you're just trying to transpose the date, copy the entier table, go to
your new place, paste special/ transpose... "GISJane" wrote: I have 8 colums of information for one item in one row and I need to change that to about 8 rows of information for the same item. Such as: A B C 1 stuff junk other needs to become: A 1 stuff 1 junk 1 other there is other stuff involved, but this is the heart of the matter. I have created a new spreadsheet that pulls all the information from the first spreadsheet, exactly as I want it. I thought I would be able to pull the information for the one item, then copy and paste as a value to another spreadsheet, then go back to the first spreadsheet and delete the first row, moving the second row to forst row position, and then the new spreadsheet formulas would pull the information from the new first row in the first spreadsheet. But Excel, in all its wisdom, deletes in the formula worksheet the reference to the first row (giving a #ref! error) when the first row in the first data worksheet is deleted. Is there a way to stop Excel from being so smart? If the formula spreadsheet will just pull the data from the first row of the data worksheet, even if the row is deleted and another row moves up, then I can accomplish my task. Any help is much appreaciated!! Jane |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference to worksheet fails if row deleted
Thanks for the suggestion, but I need to do more than that. The data table
has particular depths and the formula table needs to take the depth of one cell and make it the 'TO' depth on one row and also use that same cell reference on the next row as a 'FROM' depth. If I can just get Excel to accept that it is to pull the data from row 1, whether or not row 1 has been deleted and replaced, then I'm good to go. Thanks for your suggestion. I had not used that command before and I'm glad to see what it does! Jane "Sean Timmons" wrote: If you're just trying to transpose the date, copy the entier table, go to your new place, paste special/ transpose... "GISJane" wrote: I have 8 colums of information for one item in one row and I need to change that to about 8 rows of information for the same item. Such as: A B C 1 stuff junk other needs to become: A 1 stuff 1 junk 1 other there is other stuff involved, but this is the heart of the matter. I have created a new spreadsheet that pulls all the information from the first spreadsheet, exactly as I want it. I thought I would be able to pull the information for the one item, then copy and paste as a value to another spreadsheet, then go back to the first spreadsheet and delete the first row, moving the second row to forst row position, and then the new spreadsheet formulas would pull the information from the new first row in the first spreadsheet. But Excel, in all its wisdom, deletes in the formula worksheet the reference to the first row (giving a #ref! error) when the first row in the first data worksheet is deleted. Is there a way to stop Excel from being so smart? If the formula spreadsheet will just pull the data from the first row of the data worksheet, even if the row is deleted and another row moves up, then I can accomplish my task. Any help is much appreaciated!! Jane |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference to worksheet fails if row deleted
use indirect.. =INDIRECT("A1")
Should ignore deletions and such. "GISJane" wrote: Thanks for the suggestion, but I need to do more than that. The data table has particular depths and the formula table needs to take the depth of one cell and make it the 'TO' depth on one row and also use that same cell reference on the next row as a 'FROM' depth. If I can just get Excel to accept that it is to pull the data from row 1, whether or not row 1 has been deleted and replaced, then I'm good to go. Thanks for your suggestion. I had not used that command before and I'm glad to see what it does! Jane "Sean Timmons" wrote: If you're just trying to transpose the date, copy the entier table, go to your new place, paste special/ transpose... "GISJane" wrote: I have 8 colums of information for one item in one row and I need to change that to about 8 rows of information for the same item. Such as: A B C 1 stuff junk other needs to become: A 1 stuff 1 junk 1 other there is other stuff involved, but this is the heart of the matter. I have created a new spreadsheet that pulls all the information from the first spreadsheet, exactly as I want it. I thought I would be able to pull the information for the one item, then copy and paste as a value to another spreadsheet, then go back to the first spreadsheet and delete the first row, moving the second row to forst row position, and then the new spreadsheet formulas would pull the information from the new first row in the first spreadsheet. But Excel, in all its wisdom, deletes in the formula worksheet the reference to the first row (giving a #ref! error) when the first row in the first data worksheet is deleted. Is there a way to stop Excel from being so smart? If the formula spreadsheet will just pull the data from the first row of the data worksheet, even if the row is deleted and another row moves up, then I can accomplish my task. Any help is much appreaciated!! Jane |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference to worksheet fails if row deleted
can Indirect be used within a formula that references another worksheet? I'm
not having luck with this, although it seems like this is the way I need to go. The formula is: =Lithology!K2 but if the second row is deleted, then Excel is making the formula =Lithology!#REF! If the formula would just stay =Lithology!K2, my problem would be solved. "Sean Timmons" wrote: use indirect.. =INDIRECT("A1") Should ignore deletions and such. "GISJane" wrote: Thanks for the suggestion, but I need to do more than that. The data table has particular depths and the formula table needs to take the depth of one cell and make it the 'TO' depth on one row and also use that same cell reference on the next row as a 'FROM' depth. If I can just get Excel to accept that it is to pull the data from row 1, whether or not row 1 has been deleted and replaced, then I'm good to go. Thanks for your suggestion. I had not used that command before and I'm glad to see what it does! Jane "Sean Timmons" wrote: If you're just trying to transpose the date, copy the entier table, go to your new place, paste special/ transpose... "GISJane" wrote: I have 8 colums of information for one item in one row and I need to change that to about 8 rows of information for the same item. Such as: A B C 1 stuff junk other needs to become: A 1 stuff 1 junk 1 other there is other stuff involved, but this is the heart of the matter. I have created a new spreadsheet that pulls all the information from the first spreadsheet, exactly as I want it. I thought I would be able to pull the information for the one item, then copy and paste as a value to another spreadsheet, then go back to the first spreadsheet and delete the first row, moving the second row to forst row position, and then the new spreadsheet formulas would pull the information from the new first row in the first spreadsheet. But Excel, in all its wisdom, deletes in the formula worksheet the reference to the first row (giving a #ref! error) when the first row in the first data worksheet is deleted. Is there a way to stop Excel from being so smart? If the formula spreadsheet will just pull the data from the first row of the data worksheet, even if the row is deleted and another row moves up, then I can accomplish my task. Any help is much appreaciated!! Jane |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference to worksheet fails if row deleted
=INDIRECT("Lithology!$A$4")
Just need the " on either side... "GISJane" wrote: can Indirect be used within a formula that references another worksheet? I'm not having luck with this, although it seems like this is the way I need to go. The formula is: =Lithology!K2 but if the second row is deleted, then Excel is making the formula =Lithology!#REF! If the formula would just stay =Lithology!K2, my problem would be solved. "Sean Timmons" wrote: use indirect.. =INDIRECT("A1") Should ignore deletions and such. "GISJane" wrote: Thanks for the suggestion, but I need to do more than that. The data table has particular depths and the formula table needs to take the depth of one cell and make it the 'TO' depth on one row and also use that same cell reference on the next row as a 'FROM' depth. If I can just get Excel to accept that it is to pull the data from row 1, whether or not row 1 has been deleted and replaced, then I'm good to go. Thanks for your suggestion. I had not used that command before and I'm glad to see what it does! Jane "Sean Timmons" wrote: If you're just trying to transpose the date, copy the entier table, go to your new place, paste special/ transpose... "GISJane" wrote: I have 8 colums of information for one item in one row and I need to change that to about 8 rows of information for the same item. Such as: A B C 1 stuff junk other needs to become: A 1 stuff 1 junk 1 other there is other stuff involved, but this is the heart of the matter. I have created a new spreadsheet that pulls all the information from the first spreadsheet, exactly as I want it. I thought I would be able to pull the information for the one item, then copy and paste as a value to another spreadsheet, then go back to the first spreadsheet and delete the first row, moving the second row to forst row position, and then the new spreadsheet formulas would pull the information from the new first row in the first spreadsheet. But Excel, in all its wisdom, deletes in the formula worksheet the reference to the first row (giving a #ref! error) when the first row in the first data worksheet is deleted. Is there a way to stop Excel from being so smart? If the formula spreadsheet will just pull the data from the first row of the data worksheet, even if the row is deleted and another row moves up, then I can accomplish my task. Any help is much appreaciated!! Jane |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference to worksheet fails if row deleted
Bless you, Sean!! That seems to do the trick!!
Thank you so much! "Sean Timmons" wrote: =INDIRECT("Lithology!$A$4") Just need the " on either side... "GISJane" wrote: can Indirect be used within a formula that references another worksheet? I'm not having luck with this, although it seems like this is the way I need to go. The formula is: =Lithology!K2 but if the second row is deleted, then Excel is making the formula =Lithology!#REF! If the formula would just stay =Lithology!K2, my problem would be solved. "Sean Timmons" wrote: use indirect.. =INDIRECT("A1") Should ignore deletions and such. "GISJane" wrote: Thanks for the suggestion, but I need to do more than that. The data table has particular depths and the formula table needs to take the depth of one cell and make it the 'TO' depth on one row and also use that same cell reference on the next row as a 'FROM' depth. If I can just get Excel to accept that it is to pull the data from row 1, whether or not row 1 has been deleted and replaced, then I'm good to go. Thanks for your suggestion. I had not used that command before and I'm glad to see what it does! Jane "Sean Timmons" wrote: If you're just trying to transpose the date, copy the entier table, go to your new place, paste special/ transpose... "GISJane" wrote: I have 8 colums of information for one item in one row and I need to change that to about 8 rows of information for the same item. Such as: A B C 1 stuff junk other needs to become: A 1 stuff 1 junk 1 other there is other stuff involved, but this is the heart of the matter. I have created a new spreadsheet that pulls all the information from the first spreadsheet, exactly as I want it. I thought I would be able to pull the information for the one item, then copy and paste as a value to another spreadsheet, then go back to the first spreadsheet and delete the first row, moving the second row to forst row position, and then the new spreadsheet formulas would pull the information from the new first row in the first spreadsheet. But Excel, in all its wisdom, deletes in the formula worksheet the reference to the first row (giving a #ref! error) when the first row in the first data worksheet is deleted. Is there a way to stop Excel from being so smart? If the formula spreadsheet will just pull the data from the first row of the data worksheet, even if the row is deleted and another row moves up, then I can accomplish my task. Any help is much appreaciated!! Jane |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference to worksheet fails if row deleted
Not a problem! Have fun with that!
"GISJane" wrote: Bless you, Sean!! That seems to do the trick!! Thank you so much! "Sean Timmons" wrote: =INDIRECT("Lithology!$A$4") Just need the " on either side... "GISJane" wrote: can Indirect be used within a formula that references another worksheet? I'm not having luck with this, although it seems like this is the way I need to go. The formula is: =Lithology!K2 but if the second row is deleted, then Excel is making the formula =Lithology!#REF! If the formula would just stay =Lithology!K2, my problem would be solved. "Sean Timmons" wrote: use indirect.. =INDIRECT("A1") Should ignore deletions and such. "GISJane" wrote: Thanks for the suggestion, but I need to do more than that. The data table has particular depths and the formula table needs to take the depth of one cell and make it the 'TO' depth on one row and also use that same cell reference on the next row as a 'FROM' depth. If I can just get Excel to accept that it is to pull the data from row 1, whether or not row 1 has been deleted and replaced, then I'm good to go. Thanks for your suggestion. I had not used that command before and I'm glad to see what it does! Jane "Sean Timmons" wrote: If you're just trying to transpose the date, copy the entier table, go to your new place, paste special/ transpose... "GISJane" wrote: I have 8 colums of information for one item in one row and I need to change that to about 8 rows of information for the same item. Such as: A B C 1 stuff junk other needs to become: A 1 stuff 1 junk 1 other there is other stuff involved, but this is the heart of the matter. I have created a new spreadsheet that pulls all the information from the first spreadsheet, exactly as I want it. I thought I would be able to pull the information for the one item, then copy and paste as a value to another spreadsheet, then go back to the first spreadsheet and delete the first row, moving the second row to forst row position, and then the new spreadsheet formulas would pull the information from the new first row in the first spreadsheet. But Excel, in all its wisdom, deletes in the formula worksheet the reference to the first row (giving a #ref! error) when the first row in the first data worksheet is deleted. Is there a way to stop Excel from being so smart? If the formula spreadsheet will just pull the data from the first row of the data worksheet, even if the row is deleted and another row moves up, then I can accomplish my task. Any help is much appreaciated!! Jane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleted row reference error | Excel Discussion (Misc queries) | |||
Worksheet fails to filter at times | Excel Worksheet Functions | |||
Deleted Reference Cell | Excel Discussion (Misc queries) | |||
deleted reference | Excel Discussion (Misc queries) | |||
Macro fails when worksheet protected | Excel Discussion (Misc queries) |