LinkBack URL
About LinkBacks
Hello. I have a list of line items in column A in Sheet 1. I then have these line items referenced (using "=") in column A of Sheet 2. When I add new content (add or delete a row, for example) how can I get Sheet 2 to automatically update? Danks.
Hello. I have a list of line items in column A in Sheet 1. I then have these line items referenced (using "=") in column A of Sheet 2. When I add new content (add or delete a row, for example) how can I get Sheet 2 to automatically update? Danks.
sheet2 cel a1=if(sheet1!a1="","",sheet1!a1) and drag down.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
oeldere that will leave an error message if they delete a row, and wont update if they add a new row
Try this insead, copied down and across..
=IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1))
it will not be affected by adding or deleting rows/columns
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks but that doesn't work. I need sheet 2 to be identical to sheet 1. For example, if I add a row in sheet 1 with NO information. I need the same thing to happen in sheet 2. Can I accomplish this with a formula or will I need to use something else, like a table or something. Thanks.
@ FDibbins. Money!! FDibbins>oeldere. Keep at it though oeldereOh, a question for you Fdibs: I just need this formula in one column (so across is not applicable) Can I condense the formula based on this info? Obrigado.
Last edited by amartino44; 08-29-2013 at 05:43 PM.
Out of curiousity...is there a way you could do that with a named range or a table or something else? I'm trying to be versatile. Accent on the e.
Yes it does, if you just drag the formula far enough e.g. A1500.
Please Login or Register to view this content.
Yep, it gives an error messagePlease Login or Register to view this content.
I'm curious. Do you excel gods get bitter when another excel god defeats you in battle?
just simplify it to...
=IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,0)=0,"",OFFSET(Sheet1!$A$1,ROW(A1)-1,0)
oeldere, if all the formulas are already in place (say rows 1:10), and they insert a new row above row6, sheet2 will not show the data inthe new row 5, it will show row4 and then row6
edit: as far as excel gods are concerned...naaa they left this 1 to us
@FDibbins
Very good point about thatPlease Login or Register to view this content.
There's no battle in it for me.Please Login or Register to view this content.
Question
But I'm curious why people want the same data on sheet 1 AND on sheet 2.
What is the use for that.
Copy / past sheet 1 is an alternative.
Sorry to revive an old thread. Thanks for this formula, it does mirror cells and include addition/deletion of rows. The next question I have is, how can we get this to apply formatting as well? If cells are highlighted, bordered, re-sized, etc., how can we make that carry over as well? With 400 documents, specifically color-coded, I would need formatting to follow if I were to utilize this. Thanks.Originally Posted by FDibbins
oeldere that will leave an error message if they delete a row, and wont update if they add a new row
Try this insead, copied down and across..
=IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1))
it will not be affected by adding or deleting rows/columns
@ageclipsegt
Please start your on question, according to the forumrules.
Please refer to this topic, if it is usefull for understanding your question.
If you don't want to manipulate the data in the mirror sheet, and just to use it for view for some reasons, you may try out for excel camera tool just google you will get n number of results for usage and enabling it in your sheet, you may also refer the attachment
☚ Click ★ just below left if it helps, Boo?ath?
New user here, and this is my first post after introduction. Don't know how everyone feels about old threads getting resurrected, but here goes!
I have found a way that works, but it only brings back the data - no formatting.
Use the INDIRECT formula. Formula in cell A1 of your copy looks like:
=INDIRECT("worksheetname!A1",TRUE)
There are some tricks to doing this right. The sheet I was trying to mirror has 24,800 cells. As you can probably guess, the double-quotes in the formula prevent it from being able to drag across the range of the sheet. Here's how you get around that:
- Type in the formula in the example above, using the names and cells that are relevant to your situation
- Leave the double quotes out!
- Drag the formula across the cells it needs to mirror
- You will notice that your sheet is filled with #REF - THAT'S OKAY!
- Do a CTL+` (the key to the left of the number 1) to expose the formulas
- Select all the cells with the indirect formula, copy them, and then paste into Notepad or other text editor of choice
- Use Find/Replace to reinsert the double quotes in the appropriate locations
- Copy and paste the whole thing back into Excel.
- Do a CTL+` (the key to the left of the number 1) to hide the formulas - Voila! Data. You can mess with the source sheet all you want and the mirror stays true.
- Note: the "TRUE" parameter is used for letter/number cell references. If you're using row/column, then use FALSE.
I know, it seems like you should be able to put the quotes back in while in Excel, but it returns an error.
Let me know how this works for you.
Last edited by LewisG; 05-16-2018 at 08:28 PM. Reason: Refined the directions ... again.
I just registered as a new user on this forum just to give a HUGE thumbs up to LewisG. This worked like a charm, thanks!
Great job LewisG. Thank you.
Glad to see this thread is still useful!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks