I need an updated excel formula

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
LVL 1
wrt1meaAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
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
 
dlmilleCommented:
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
 
AnilData ManagerCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
wrt1meaAuthor Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
Do you want Sheets 2-4 in the same order as Sheet 1 always?

Dave
0
 
wrt1meaAuthor Commented:
akajohn,

I dont need the columns to change just the row.
0
 
dlmilleCommented:
The way the formula is written, it won't handle INSERTING or DELETEING rows from Sheet1.

Dave
0
 
dlmilleCommented:
So please let me know if Sheets2-4 are sorted differently from Sheet1 - or an exact copy for the Project Name.

Thanks,

Dave
0
 
wrt1meaAuthor Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
apologies - I see the formula does work fine on insert ;)

Try the second post I submitted, please

Dave
0
 
wrt1meaAuthor Commented:
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
 
dlmilleCommented:
That's correct - I had to add the code, lol...

Everything should be working ok now, please advise.

Dave
0
 
wrt1meaAuthor Commented:
Taking a look now...Seems to work. I will keep you posted as I get it migrated into the production sheet.
0
 
dlmilleCommented:
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
 
wrt1meaAuthor Commented:
THANKS FOR THE HELP!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.