Hi there, thanks in advance for any help I receive.

I'm wanting to use a function, such as maybe VLOOKUP (in the yellow cells on my data page). to check on the data sheet column BJ (which is my dataset column),

then lookup on the dataset page in the full BLUE COLUMN A for either matching text in Column A as DATA Column BJ and copy the forumula into the cell.

e.g on the data page, CELL b2 needs to lookup BJ2 and read the text thats in this column (or blank value), then lookup on the blue column on dataset sheet for matching text (or default to set of formulas for a blank value) the formulas i want to copy are in the orange cells.

when the dataset has no value / is blank then i want it either defaulting to the cell with the blank value. or MD Lookup Table . as md lookup table is not a dataset but the actual main table for all the data that has no values in the dataset column.


i tried to achive it with VLOOKUP but got REFS

is this possible with excel? is it a different function to make this possible than vlookup maybe?

want some sort of lookup to return a formula from another workbook or sheet to run on the cell that looks up.

Perhaps you could post a small mock-up file instead of the one that had to be deleted?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

You could maybe point me in the direction of that does the same thing or a guide (i cant find one)

basically need to lookup a text value cell which features at the end of my data and then return a formula from another sheet to go in the box that is completing the lookup.

so i have a sheet with column A having the name of the dataset, then row B, C, D etc would be the formulas I want the lookup to return when column A is matched.

I can only work out how to return values, and not use a formula that works on the active sheet the cell with the formula is in.

is this possible? what functions are best for this

As far as I understand your explanation, you cannot retrieve a formula with another formula. VBA would be needed, which is not my cup of tea...

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

not my cup of tea either, although like to learn but i only take small bits in at a time as need them with my work so get the expierence of using the new knowledge as i learn it.

thanks that kinda lets me know why it wasnt answered over the weekend if its not possible or really hard. would of thought they could make a VFORMULALOOKUP function that did this for you in a future version. would be really useful

If your formula is stored as a string minus the leading '=', rather than as an actual formula, you could convert it to an actual formula via your VLOOKUP using the INDIRECT function.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

thanks you got an example or a guide to recommend that i could follow thanks again

Not to hand, though the INDIRECT function's usage is fairly straightforward. That said, it's not clear why you believe the VLOOKUP function needs to return different formulae.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Actually I've read a little about indirect and still not 100% sure on its use in practice in my worksheet. Sorry I'm not amazing on excel but can take things in when i put them into practice and look back at it and get the understanding from my working example I've been helped to create.

I've never touched indirect so not 100% sure how to use it sorry. but reading another guide I found explaining its uses and rethinking what I want to achieve to simplfy this task and I think I don't need to VLOOKUP the formulas and could use INDRECT to change which worksheet page and document it points part of the formula too. As the only differences in the formulas I wanted to rip with a VLOOKUP is that they point to a different sheet than the default choice.

Example:

i have say 2000 rows of data from row 2 to 2002. Formula's from Column A to K.

in column P is the name of the data type.
each data type looks up order numbers on a different worksheet in another document. If i could use indirect to change that part of the formula only I would get the same result I'm looking for.


below is a formula in cell A2 which is a formula i have to normally copy and paste over - based on what text value is in column P on the line of data. this is from row 2

basically in this cell (a2) it looks up at the end of the data in the row in P2. In here is a word which is "Data type 1" in this example on P2. This is the data type and name of the worksheet it needs to lookup in another document so currently it looks like this


I manually review it and if it was say Data Type 2 in cell column P I would copy and paste over it

now looking at indirect even though I dont properly understand it. I think it could check out column P for me and then use that to enter a different sheet name.

could you give me an example of how to do this based on the quote above for row 2 and using cell p2 as the cell which has the sheet name in it.

Only extra thing I need in the example formula, is what to do when there is no text or numbers in column P. I would then like it to then default to the sheet in the other document "MD Lookup Table" instead of the "Data Type 1" etc.

Think this will be much simpler to get the same result. Can you give me an example and then I can apply it to all the other cells and put it in practice. Thanks for your help again, this has been a helpful site

I'm not really following what you're doing here. I gather that in column P you have values like:
Data type 1
Data type 2
etc.

But, if your VLOOKUP:
=VLOOKUP(P2,'[different workbook.xlsx]Data type 1'!$A:$I,5,FALSE)
is referencing P2, that seems to be saying you're looking for a match with 'Data type 1' on '[different workbook.xlsx]Data type 1'. In other words, the equivalent of:
=VLOOKUP("Data type 1",'[different workbook.xlsx]Data type 1'!$A:$I,5,FALSE)


PS: For a demo of INDIRECT's use in conjunction with the OFFSET function (not necessarily relevant to your specific needs), see: https://www.msofficeforums.com/excel...reference.html

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

thanks, i know its hard when I'm been slapped on the wrist for uploading an example documant and you cant see what im on about and i'm not the best at typeing stuff out online without rambling or repeating and can be consufing but I think you got me the help i need and I can now explain to people I'm working with what functions and what i need to get there.

Im just trying to automate as much as my excel work as I can, and find its a great way of learning macros and more in depth and new functions.

As pecoflyer said in post #3, it would be really helpful if you could post a workbook with some representative data. We don't need anything sensitive, just something that lets us see the kind of structure & data you're working with - it doesn't even need to be separate workbooks, since a solution that works with separate worksheets will generally work equally well for separate workbooks.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I can paste a formula to try and explain and you may be able to get it from the quotes below.

and where its meant to go and what the actual file names are. I think i've worked it out, i just need to format it so the formula accepts it and dont give an error so the function works as intended. I think I've got something like a sympbol missing or in the wrong place (could be to do with the docuemnt name and sheets have spaces in and need more punctuation?)

in my examples working on Row2 for this formula

Current Formula in cell A2 is

im cell BK2 i have the words I'd like to use indirect to do this

The full file name location of the sheet i want indirect to look at is
Looking at other sites and using your help I came up with
which dont work - I think I've entered it slighlty wrong.


I got the idea to use the above formula (that dont function yet) from this on another site when reading further into it
Is that better for you to see what I need to do. I dont mind putting the actual full filename and path in for the document and thats why have included it in the quotes thanks again

Also on a side note, ive tried googling but cant see an answer
What are the & symbols for in excel? Im unsure on their purpose and not seen them before?


thanks for your continued help even though its probally bit frustrating for you guys

From what you've posted I think you'd need to use:
=VLOOKUP(P2,INDIRECT("'[2015 Cordell Valuation Lookup Tables.xlsx]"&BK2&"'!$A:$I"),5,FALSE)

On a side note, if you add worksheets to your existing workbook to pull in the data from your '2015 Cordell Valuation Lookup Tables' workbook, you could reduce the formula to:
=VLOOKUP(P2,INDIRECT("'"&BK2&"'!$A:$I"),5,FALSE)
That, in turn, means you wouldn't need to have your '2015 Cordell Valuation Lookup Tables' workbook open for the calculations to work.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]