Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I need an updated excel formula

Posted on 2011-05-13
18
Medium Priority
?
211 Views
Last Modified: 2012-06-27
I need an updated formula to automatically add rows on Sheet 2 & Sheet 3. On a previous question, I was provided a very handy formula that did it, but I cant seem to manipulate it to work on other sheets.

Here is the formula: {=IFERROR(INDEX(Sheet1!$A$2:$A$183,SMALL(IF(Sheet1!$B$2:$B$183<>"",ROW(Sheet1!$A$2:$A$183)-ROW(Sheet1!A$2)+1),ROWS(A$4:A13))),"")}

Right now when I insert a new project name on Sheet 1, it automatically puts it on Sheet 4 (see the highlighted yellow "TEST."

I need it to work when I insert a new project on Sheet 1, it automatically adds it to Sheets 2 & 3.

See attached file.
5-13-11.xlsx
0
Comment
Question by:wrt1mea
  • 11
  • 6
18 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35755407
I assume you're happy with how it works on sheet 4 - appeasr this formula is all about sorting the projects by number (at first glance)?

On Sheet's 2 and 3 appears the direct link formulas got out of synch.

When you say, you "Insert" a new project on Sheet 1 - do you mean you do an entire row insert, or you just type in a project name?

Here's a "repair" so that the TEST shows up. (all I did on Sheet 2 and 3 was copy down the formula from row 3).

Please let me know if that's all you need, or if you want to make Sheet 2 and 3 formulas as sophisticated as Sheet 1

Dave
5-13-11-r1.xlsx
0
 
LVL 6

Expert Comment

by:akajohn
ID: 35755434
I would like to ad the following . As the sheet is now : Although the rows will move up and down on sheets 2 ,3 ,4 as sheet 1 changes
the The Columns Agent and Admin on sheet 2 will not!
We do not know really your requirements but do test.
A>.
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 35755440
Dave,

Yes, when I insert, I mean I insert an entire row. I will need to have the sophisticated formulas becuase I am trying to get this workbook as automated as I can, so I only have to insert a row one time and its replicated.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 42

Expert Comment

by:dlmille
ID: 35755444
Just in case you want the same formula in Sheet 2 and 3 as in Sheet 4 - here it is with that updated.

FYI - You cannot delete rows in these sheets with the way the formula is written, today, and expect them to reflect the sorted list from Sheet 1.

Dave
5-13-11-r2.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35755453
Do you want Sheets 2-4 in the same order as Sheet 1 always?

Dave
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 35755455
akajohn,

I dont need the columns to change just the row.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35755476
The way the formula is written, it won't handle INSERTING or DELETEING rows from Sheet1.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35755482
So please let me know if Sheets2-4 are sorted differently from Sheet1 - or an exact copy for the Project Name.

Thanks,

Dave
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 35755496
Dave,

If I insert a row on Sheet 1, it will be replicated on sheet 4. I know that works because I have it working on my production sheet with the same formula.

Thats why I illustrated the "test" in yellow.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35755528
Try that again with the Sample you submitted online...  I just did that and did not see that result.

At any rate - please respond:  do sheet 2-3-4 need to be sorted, or are they just a capture of sheet1 in the same order?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35755535
I see that the formula is not sorting, I know that, but I'm asking as I'm needing to rewrite it anyway.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35755543
apologies - I see the formula does work fine on insert ;)

Try the second post I submitted, please

Dave
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 35755557
They are just a capture of Sheet 1 in same order.

I think the reason it isnt working for you is that you didnt modify the code (which is really a unique accounting code). See attched.
5-13-11.xlsx
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35755559
It should be fine.  I guess I hadn't had enough coffee this morning :)

#1 - The original error was that the formulas were messed up at some point in Sheet 2 and 3.
#2 - I used that formula for Sheet 2 and 3 as I assume that's what you wanted.

Here it is again, please advise if this helps


Dave

5-13-11-r2.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35755568
That's correct - I had to add the code, lol...

Everything should be working ok now, please advise.

Dave
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 35755606
Taking a look now...Seems to work. I will keep you posted as I get it migrated into the production sheet.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35755622
Sorry about all (my) confusion at the start, lol.  I hadn't added the code when I was testing so I thought something was very wrong...

Dave
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 35756549
THANKS FOR THE HELP!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question