Can This formula give 2 choices

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

=VALUE(LEFT($F$6,1))

With the above formula can I for example

=VALUE(LEFT($F$6,1,W))

So it can find the either or Numeric or Alpha value
please.

Thankyou.
 
Hi Steve
not really sure what you're trying to do. Do you want something like
=IF(ISNUMBER(--(LEFT(F6,1)),--LEFT(F6,1),LEFT(F6,1))
 
Try to be more specific while staying in the same thread instead of creating
three of them...

It seems you want something like:

=IF(1-ISERROR(F6),IF(ISNUMBER(--LEFT(F6)),--LEFT(F6),LEFT(F6)),"")
 
Hello from Steved

Firstly Thankyou
In most cases I do stay in the same thread, but in this
case I created three diiferent situations hence believing
that in this case that was my best option but I wiil in
future stay in the same thread besides it makes sense.

Cheers
 
Thankyou.

The formula you gave me will do either Alpha or Numeral.

So What I am doing is if the 1st character is text or
General

Cheers.
 
Hi
not sure what you mean with this?
The first part should be either a number or a text (if you have stored
the value as text value). Can you give an example for your question
 
Hello Frank from Steved

In cell F6 I might have "5401" Because I am only wanting
to Identify the first Character in this case "5" the
information on that row "A6:A30 which will extract the
information and copy into Sheet5. Using this method it only
allows me to do Sheets 1 to 9, so now I need to do "W5401"
which will goto a sheet named "W", now I am able to go
from SheetA to SheetZ depending on the 1st Character
ie "W5410, O6419" as an examples

ok How do I tell W5401 goto SheetW please, as at the
moment the formula works for Sheets 1 to 9 but not for W
or O Sheets

Thankyou.
 
Hi Steved
it's probably me but your description is confusing me.
- What formula are you currently using that 'gets you' to the sheets
1-9
- What do you want to extract and from there
- A6:A30 is not a row but a range

Maybe you're trying to do something like
=INDIRECT("'" & left(F6,1) & "'!X1")

to get the cell value from X1 from thet sheet identified by the first
character of cell F6
 
Hello Frank from Steved

What is my objective
My objective is that I need to copy Row information in the
Master Sheet "as an example B6 to Q6"

How do I acheive this
In R6 I have a formula which needs to look in F6 and
give the first numeral, say I have "5401" the first numeral
is 5, so I Need to copy the information to Sheet5 in this
case another example "7544" in this case 7 so this
information will go to Sheet7

Now to put the copied information in this case to Sheet5
In Sheet5 I have a formula in Col A6 =IF(Master!R6=5, ROW
(),""), Okay looks in Master Sheet looks in R6 and finds 5
and in rows B6 to B30 I have the below formula in each cell
=IF(ISNUMBER(SMALL(IF($A$6:$A$30="","",ROW($A$6:$A$30)),ROW
(1:1))),INDIRECT("Master!"&CHAR(COLUMN()+64)&SMALL(IF
($A$6:$A$30="","",ROW($A$6:$A$30)),ROW(1:1))),"")

If you would like to see how it works
Create 2 Sheets one called master and the other Sheet5
put data in cells B6 to Q6.

Ok If I have as normal not made myself clear, I would be
only to happy to send to you The Spreadsheet.

ps if you decide in your own wisdom to do the example
please Frank will you do 2 situations, name 2 Worksheets
1 called Sheet5 and the other Called SheetW, Sheet5 put in
Cell F6 type 5401 please and goto Sheet5 in Row B6 to Q30
you should see 0's in each cell, now please go back to
Master and change 5401 to W5401 now it should be in SheetW
and nothing in Sheet5. This is my dilemma getting SheetW
to work.

Cheers


Thankyou.
 
Hi Steved
to be honest this formula is much to complicated if you really only
want to 'copy' row 6 to the appropiate workshett depending on the first
character in ell F6.
- Master sheet: leave this untouched
- sheet7:
A6:
=IF(Master!R6=5,1,0)
and copy down for A6:A30
B6:
=IF(A6,'Master'!B6,"")
and copy down

- sheetW
just replace A6 with
=IF(Master!R6="W",1,0)
and copy down

Of course you could make the statement in A6 dynamic. That is reading
the sheetname with CELL("filename",A1). So you may try in A6:
=IF(Master!R6=RIGHT(CELL("filename",A1),1),1,0)
 
Back
Top