If Then Formula

[gambinus2]

I am trying to create an If Then formula to create a table. What I do is dump data from a website into Excel (see attachment) into columns A and B. I have set up a table so that if certain data is in column B then a name will appear in Column C.

So what I would like to do is create an If Then formula saying, If a certain name in Column C appears then the transaction amount would go in one cell and the value date would go in another.

I would like to have a worksheet for each name in that is in column C. I have 35 possibilities that could show up. If that name shows up in column C then I would like that transaction amount in one cell and the value date in another cell.

So if a certain name shows up in column C then 6 cells above it in column A would be the value date.

And then in another cell, if a certain name shows up in column C then 3 cells above it in column A would be the transaction amount.

Is a formula like this possible?

[NBVC]

See attached.

I create a sheet for the first one, VEAM

Formula in A3:

Please Login or Register  to view this content.

Formula in B3:

Please Login or Register  to view this content.

Both formulas are confirmed with CTRL+SHIFT+ENTER not just ENTER.

Then copy them down as far as you want

You can then make copies of this sheet and just change the reference in B1 to the appropriate Center.

I changed the Second center in the main sheet to VEAM for test... you can change it back if it was real.

Btw: What happened to your other username?

[gambinus2]

I am having some trouble taking the formulas you created and implementing them into my sheet.

The worksheet my data is in is named: Nov Data

I have data down to line 1043.

So would not the formula look like the following:

=IF(ROWS($B$1:$B1)>COUNTIF(NOV Data!$C$2:$C$1043,$B$1),"",INDEX(NOV Data!$B$2:$B$1043,SMALL(IF(NOV Data!$C$2:$C$1043=$B$1,ROW(NOV Data!$B$2:$B$1043)-3-ROW(NOV Data!$B$2)+1),ROWS($B$1:$B1))))

When I enter the above and click CTRL+SHIFT+ENTER I get a pop up box that says: Update Values: Data

What am I doing incorrectly?

[NBVC]

Since you have a space in the Sheetnames, you need to surround the sheetname with single quotes at each occurrence

e.g. 'Nov Data'!

[gambinus2]

Still not getting this formula to work. Does it matter that I have formulas in Column C as opposed to the Centers name typed in?

Attached is the sheet I actually use, with the formulas I had in place before you started helping me.

[NBVC]

Your column F in NOVDATA had an extra space in front of the VEAM entries... please clean them all.

The #N/A is causing an issue to...

You can get rid of them.. or if you want to keep them change the formulas in A3 and B3 of VEAM sheet to:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

both confirmed with CSE and copied down

[gambinus2]

THANK YOU THANK YOU THANK YOU!

Finally got it to work.

Again, Thank you for your patience and help.

[shetaznut]

Trying to create "If then" formula for several Item Codes and Description:
ie. If A-003 item code then Alcolec S automatically displays as description? Help?

[NBVC]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.