[Solved] VLOOKUP using table range or address

Discuss the spreadsheet application
Locked
Ricky
Posts: 14
Joined: Sun Nov 12, 2017 11:39 pm

[Solved] VLOOKUP using table range or address

Post by Ricky »

i got 4 tables of data defined as cells range

now i need to select one of the table to get required data
may be using a combo box giving the data's range from another data range

is there a way to add in a vlookup command the table name from a list containing the address from a combo box list ?

table could be as a table name or address range
like B1:C12 or range name as text in a cell

or specify the name from another cell with the table name or address

looks like there are some limits on how to represent the cells range in a vlookup!
but not really shown in the command description


note if needed let me know will prepare a small sample file


thanks for feedback
Last edited by MrProgrammer on Sun Jun 25, 2023 2:48 pm, edited 1 time in total.
Reason: Ricky says: VLOOKUP/INDIRECT working fine now
Openoffice 3.1 Win 10 64 bits
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Vlookup using Table range or address ?

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ricky
Posts: 14
Joined: Sun Nov 12, 2017 11:39 pm

Re: VLOOKUP using table range or address

Post by Ricky »

i made a table for the 4 DATA TABLES i need to select

i use combo box which allows me to select one table

i can then show either the range's name or range's address

but then i tried to use the indirect with the range's address or
and it is refusing to use it !

from combo box let say i get one table range like
$awg1.$C$30:$I$33

zz2.JPG
zz2.JPG (82.02 KiB) Viewed 493 times
if i pass this address inside an indirect command as range it gives an error
is there some other command to use this address and make it works inside the indirect command?


thanks for feedback
Openoffice 3.1 Win 10 64 bits
Alex1
Volunteer
Posts: 723
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: VLOOKUP using table range or address

Post by Alex1 »

Function parameters must be separated by semicolons, not commas.
You don't show how you use the indirect function and you don't give the error message.
It isn't clear which tables you mention.
Screenshots are of no use. We cannot test them and have to guess the formulas and what's on the other sheets,
so upload an example file instead.
AOO 4.1.15 & LO 24.2 on Windows 10
Ricky
Posts: 14
Joined: Sun Nov 12, 2017 11:39 pm

Re: VLOOKUP using table range or address

Post by Ricky »

the sample example given was just to give an idea on getting the right commands

i did find the indirect command with Vlookup
which is working fine now


now my main file objectives will contain something like around 20 tables
this is a complicated electrical design file for wire selection function of codes

parameters include

Ambient temp
Wire Temp
Wire tables capacity
Wire type - Cu - AL
qty of wires 1 - 3 or more using another table for derating
wire voltage - low Volt - HV

if you have any know how in electrical design may be you can help
let me know

i'm beginning to wonder if it would not be easier to do it using some VB macro in Calc
to access the different tables !

i did a few small VB macro in CALC
but never really played with tables on sheet !


is there a way to get the range's address in a cell from range's name ?
 Edit: New question needs new topic
You will get questions solved quicker when you attach a spreadsheet 
-- MrProgrammer, forum moderator  
not certain if i should share all the data cause this does not apply in every country only North America.
but i could modify the tables - make them smaller and no formulas
this is a lot of work and time to prepare all the data's and the calculations.


thank for feedback
Last edited by MrProgrammer on Sun Jun 25, 2023 2:53 pm, edited 1 time in total.
Reason: New question needs new topic
Openoffice 3.1 Win 10 64 bits
Locked