Modify IF Formula

I have this formula in one of my sheets which is working ok but if the statement is False it adds up an empty row which I don’t like it

=IF(LEFT(Entry!B15,3)="Design",Entry!B15,"")

Is there anyway to modify this formula so when the "Entry" sheet doesn't contain "Design" the formula does nothing?

Would it be better to use a Vlookup formula and if yes how is the syntax?

I prefer not to use a Macro or VB code

My fault, sorry, it is 6. I design a new workbook and working on several alternatives and guess I mixed up things

In any case is it possible to have no action when the formula is False and avoid the empty row i get now

Is VLOOKUP a better way?

I have attached a sample file to indicate the empty rows I get

I know it happens because on the "Entry" sheet I dont have "Design" entries but I wonder if there is a way to modify the formula to avoid having empty rows on the "Design" sheet.

Also I thought if I would use VLOOKUP may be better?

Originally Posted by martindwilson

i'd add a helper column on each of the 3 sheets then use index/match to get the values

I print some instructions from the web on the new functions (Index, Count, Rows, Max, Match) and study them but they did not provide me with any more info regarding the new Index column C

Looking at the Index columns, I notice that the numbers are in ascending order but the Design sheet has many more than the other sheets and why the space between these number?

I would appreciate it if you can give me a little insight on the Index column C use

Cheers

That’s pretty neat Martin, thanks so much

It looks that is working really well and don’t mind the new index column.

I will study the new formulas to see if I understand them just so I can educate myself

Much appreciated

Thanks for your time Martin

Sorry for my ignorance but have some difficulty right now understanding these functions.

For example on the Design sheet, rows 3 & 4 there are 2 Design entries but there is no numbers in column C. Why?

As I design my new workbook, I will add perhaps 10-15 more sheets (like parts, specifications, metallurgy, etc) and my main concern is how do I figure out what numbers I will write in column C?

Perhaps I copy the formulas into my new sheets and it will work?

Thank you for your patience Martin

I printed your last 2 emails and I now study them and think I'm getting there; I want to understand them so in the future if I have any problems I can fix them myself

Cheers

I now understand what you say in your last 2 emails Martin but I observed the following which in my point of view are confusing:

On the Entry form I noticed you have put some sequential numbers next to the text of each entry (Row 2 design you have 1, row 5 design you have 2, etc). I erased these numbers to see what affect they have and looks like that nothing changed. Do these numbers perform something?

On your last email you said "the numbers in col c are obtained by checking col b of entry sheet, they mark the rows that mach". My observation does not agree with this statement but of course I may be wrong

For example on the entry sheet the first four entries for design are in rows 2, 5, 7 & 8

Now if I check column c on the design sheet I see these numbers

column c value in row 2 = 1
column c value in row 3 = nothing
column c value in row 4 = nothing
column c value in row 5 = 2

Do I read something wrong?

Thanks again so much Martin

Cheers

Great work Martin, took me some time to understand your nicely done file but now I can see how the formulas work together

Many thanks again

Cheers

Hi Martin

Working on my file I discovered that if I press F2 to edit an entry on the "Design" sheet I only see the formula and not the entry. Is it easy to fix this problem?

If not I will find another way to go about it because I really like what you did and works well

Hello Martin

Go to the file you posted on Post 7 and on the design sheet try to edit any entry. I do this by selecting the row I want to edit and pressing F2. What I get is the formula and I'm not able to edit the entry

That's what I thought Martin and its ok

However the idea of having the various sheets (Design, Purchasing, etc) was to have a separate record of each on different sheets. Now lets say 6 months later I may have 100 entries on the Design sheet and if I notice an error or I want to add something on this record I should be able to do it without trying to find the same record on the Entry sheet.

Thanks again, your help has been more than excellent

Oskar

I have been following this thread, which is related to your thread about calendars which I've already responded to - I hope Martin doesn't mind me butting in.

Using Martin's file, suppose you want to amend the entry which is in row 23 of the Design sheet. Scroll down column C of that sheet until you find 23, and note the row that it appears on - that is the row in the Entry sheet where you will find the original entry which you now want to amend, so it is relatively easy to do the amendment on the Entry sheet, and of course it will automatically reflect back on the Design sheet (well, as long as you keep "Design" as the first word).

Hope this helps.

Pete

That is an easy way of amending an entry, thanks for your time Pete